Buzz
This article provides detailed guidance on how to filter PivotTable reports in Excel 2013 accurately and efficiently.
Original PivotTable reports are generated based on your requirements, allowing you to view comprehensive data information. If you need to analyze data based on a specific field, you can utilize data filtering to precisely retrieve the information you need.
You have created a comprehensive report:
Due to work requirements, you need to analyze based on certain criteria to provide sales direction -> utilize the data filtering feature in PivotTable.
1. Filter to view one of many sales representatives.
For example, filter report data by sales representatives, and these representatives are sorted by descending order of revenue by default.
Step 1: Right-click on the salesperson field name -> Add to Report Filter.
Step 2: Once you've selected the field to filter -> the report data changes according to the salesperson, and the salesperson field is added to the FILTERS list.
Step 3: Now you want to display detailed information about a specific salesperson as follows: Move up on the report, click the arrow -> list of displayed salespersons -> select the name of the salesperson you want information about -> click OK.
Step 4: After clicking OK -> all detailed information about the salesperson is displayed:
- If you want to display all salespersons instead of selecting a specific name, click All.
2. Identifying High-Revenue Sales Employees within the Data Table.
Step 1: Add the revenue field to the ROWS dialog box by: Right-clicking on the field name -> selecting Add to Row labels.
Step 2: Click on the arrow in Row Labels -> Value Filters -> Greater Than Or Equal To (or you can choose a different sorting type based on your purpose):
Step 3: The Value Filter (id) dialog box appears, enter the value you want to compare. For example, here we are finding employees with revenue greater than or equal to 4391500 -> click OK.
After selecting OK, employees with revenue greater than or equal to $43,915,000 are displayed on the report, while those with revenue less than that are not shown.
3. Filtering Information by Time.
To filter information by time, you need to consider the following:
+ Date data type must be formatted in the date format supported by Excel 2013.
+ It's advisable to add the date field to the Column for clarity.
Step 1: Right-click on the date field -> Add to Column Labels:
Step 2: Click on the arrow of Column Labels -> Date Filters -> Between (or you can choose a different comparison type depending on your purpose):
Step 3: Enter the date values for the time range you want to analyze -> press OK:
- Upon pressing OK, you'll get the result:
4. Canceling filters.
To cancel filters, you need to remove them in both sections of the PivotTable reports.
- Removing filters in PivotTable:
To filter by row or column, perform the following steps: Click on the arrow icon Column Labels -> Clear Filter From “Date”
- Clearing filters in the PivotTable Field List:
Click on the filter icon next to the field name -> Clear Filter From “Date”
With these two steps, you have successfully removed the filters in the PivotTable.
5. Cancel 1 filter in the PivotTable report.
If you cannot find Clear Filter From to remove filters in Filter or you want to remove all filters in PivotTable, follow these steps: Click on PivotTable -> go to ANALYZE -> Action -> Clear -> Clear Filters:
Above is a detailed guide on how to filter data in PivotTable in Excel 2013.
Wishing you success!
