OmniDocHow to filter PivotTable data: How to use filters and slicers
Explain clearly the three filtering methods of pivot tables: the report filter controls the entire table, the field drop-down selects specific items, and the slicer uses buttons to switch and link multiple tables. Attached are the operating steps and verification methods after screening.
Key points of this article
- If you want to see the entire summary by a category, use the report filter; if you want to hide only some items in rows or columns, use the field drop-down filter.
- When you need to switch frequently or demonstrate to others, the slicer is more intuitive than the drop-down menu. You can see the current filtering status by clicking on it.
- A slicer can connect multiple pivot tables from the same data source, which is suitable for batch filtering of multiple summary tables.
- After adding new rows to the source data, the data range must be expanded and refreshed, otherwise the new data will not appear in the filter items.
- Check totals with source data after filtering, and check blank items and number formats to avoid misreading numbers.
After making a pivot table, a common next step is to look at only a part of it: a certain region, a certain salesperson, or certain months. Many people will go back and modify the source data or create a new table. In fact, the pivot table itself has a filtering function.
This article is written for people who use Excel for daily summarization. It explains clearly when to use report filters, row and column field drop-down filters, and slicers, and how to use a slicer to control multiple pivot tables at the same time. After reading, you should be able to choose the right filtering method and check whether the filtering results are correct.
How to filter pivot tables? One sentence conclusion
The key to filtering pivot tables is to first think clearly about the scope you want to control: the entire table, a certain row or column, or synchronization of multiple tables. Once the scope is determined, the method is also determined.
| your situation | Recommended practices | reason |
|---|---|---|
| I just want to see the entire summary by one category, such as by region. | Report filters | Place it above the pivot table to affect all data |
| Want to hide only certain items in rows or columns | Drop-down filtering of row and column fields | Check items directly without affecting other fields |
| Need to switch back and forth, or multiple people share a report | slicer | Just click the button and the current filtering status can be seen at a glance |
| Multiple pivot tables need to be filtered simultaneously | Slicer connects multiple pivot tables | Tables from the same data source can share a slicer |
| Want to filter by numerical conditions such as amount, quantity, etc. | value filter | Just select the numerical condition in the field drop-down |
Before you start: Confirm these points first
- Whether the source data is standardized. Each column must have a title, no blank rows in the middle, and no merged cells. Pivot tables rely on column headers to identify fields. When the format is confusing, filter items will be missing or duplicated.
- Which field is to be filtered? The same field has different functions when placed in rows, columns or filter areas. Decide first whether you want to "look at only one part" or "compare all parts."
- Whether the data will continue to increase. After new rows are added to the source data, the pivot table will not automatically include these rows. You need to expand the data range first and then refresh the pivot table.
- Whether multi-table linkage is required. Slicers can only join pivot tables from the same data source. If several tables come from different data, they cannot share the same slicer.
- What is the source of the table? If you receive a PDF table, you must first convert it to an Excel file before you can create a pivot table. The scanned PDF can be OCR recognized in OmniDoc and then exported to Excel format.
Steps to add report filters and slicers in Excel
- Select any cell in the source data area, insert the pivot table, and confirm that the automatically recognized data range includes all rows.
- Drag the field to be filtered to the "Filter" area, and it will be displayed above the pivot table.
- Click the drop-down box above and select one or more items. The report will only display the summary of these items.
- When button filtering is required, insert a slicer in the pivot table-related tool and select the field to be used as a button.
- Click an item on the slicer. Hold down Ctrl to make multiple selections, and click the Clear button on the slicer to restore all data.
- If you want to filter multiple pivot tables simultaneously, select the pivot table to be connected in the slicer settings and check all tables that need to be linked.
- Adjust the number and size of the button columns of the slicer so that the project name is fully displayed and easier for others to read.
Check each item after completion
Just because the filter results look correct does not mean that the numbers are actually correct. The following suggestions should be checked one by one.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Total after filtering | Manually add up the amount of the corresponding items in the source data or compare it with the filter function | When the difference is large, check whether there are duplicate records or fields that have been dragged to the wrong position. |
| Are the filter items complete? | Does the drop-down list contain all the items you expected? | Refresh the pivot table when there are missing items and confirm that the data range includes the new rows |
| Whether the slicer and report are synchronized | Click on an item to see if all related pivot tables change at the same time | When not syncing, recheck the slicer's connection settings |
| blank item | Whether the "(blank)" item appears in the field list | Clean empty cells in source data, or exclude whitespace in filters |
| Number format | Whether the amount and percentage display are as expected | Adjust the number format in the value field settings. Do not directly change the display method of the source data. |
📊 Core comparison
| Way | Can multiple selections be made? | Can multiple tables be linked? | occupy space | Suitable for the scene |
|---|---|---|---|---|
| Report filters | Yes, check multiple items | Cannot be linked directly | Occupy one row above the pivot table | Fixed looking at the whole from one dimension |
| Row and column field drop-down | Yes, check the displayed items | cannot | No additional space occupied | Hide individual rows or columns |
| slicer | Yes, hold down Ctrl for multiple selections | Yes, multiple pivot tables can be connected | Occupies a floating area | Frequent switching, multi-table synchronization, demonstration and reporting |
❓ Frequently Asked Questions (FAQ)
1. How to restore all data after filtering the pivot table?
Click "Select All" in the report filter drop-down box, or click the Clear Filter button on the slicer. All the check boxes in the field drop-down boxes also need to be checked again.
2. There is no field I want in the slicer, what should I do?
Slicers can only use fields that are already in the source data. First confirm whether the field exists in the field list of the pivot table. If this column does not exist in the source table, it needs to be added to the source data before refreshing the pivot table.
3. Why can’t the newly added data be seen in the filter items?
The pivot table reads the data range selected when creating the table. Expand the source data range and refresh the pivot table before the new data will appear in the filter items.
4. Can batch filtering of pivot tables process multiple tables at one time?
Can. Connect the same slicer to multiple pivot tables from the same data source and filter them simultaneously with one click. Manual filtering one by one requires separate operations.
5. The form needs to be shown to English-speaking colleagues. Can it be translated directly?
OmniDoc supports translation of Excel (.xlsx) files, and you can choose bilingual comparison or translation only. The translation is machine translation, and headers, proper nouns, and key figures still need to be checked one by one. After opening the translation, also check whether the structure of the pivot table remains intact.
📝 Summary
How to filter pivot tables, the key is to first think clearly about the scope to be controlled: the report filter controls the entire table, the field drop-down selects specific items, and the slicer is suitable for click switching and multi-table linkage. Confirm the source data specifications before starting, and use the source data to check the total amount after completion, so that the screening results will be more reliable.
