A client once showed me a pivot table that was producing numbers that did not match her source data. She had checked her formulas, checked her data, and was convinced Excel had a bug.
The actual cause: she had applied a filter three weeks earlier while answering a one-off question, forgotten about it, and the filter was silently excluding a portion of her data from every report since. Excel was working perfectly. The filter was simply invisible unless you knew exactly where to look.
This happens constantly, and it is why understanding the different filtering methods in pivot tables — and how to verify what filters are currently active — is one of the most practically important skills beyond the basic drag-and-drop mechanics.
The Five Filtering Methods
Excel offers several distinct ways to filter pivot table data, and they behave differently enough that choosing the right one for your situation matters.
Method 1: The Filters Box
This is the method most tutorials teach first. Drag a field into the Filters area of the PivotTable Fields panel. A dropdown appears above your pivot table, and clicking it lets you select which values to include.
Best for: Filtering by a field you do not want visible in the actual table structure — for example, filtering by Year while showing Region and Product in your rows and columns, without Year cluttering the visible layout.
The trap: Filters applied here are easy to forget about because the field disappears from the visible table once you have made a selection — you only see evidence of the filter in the small dropdown indicator above the table, which many users do not notice during a quick glance.
Method 2: Row and Column Label Filters
Click the dropdown arrow next to “Row Labels” or “Column Labels” directly in the pivot table itself. This opens a checklist of every unique value in that field, letting you uncheck values you want to exclude.
Best for: Quickly excluding specific categories — for example, unchecking a discontinued product line from a Product field that is already in your Rows area.
The trap: This is the filter type most commonly forgotten because the dropdown arrow changes appearance only slightly (a small funnel icon appears) when a filter is active, and this visual cue is easy to miss, especially at a glance or when screen-sharing during a meeting.
Method 3: Value Filters
Within the same Row/Column Labels dropdown, there is a “Value Filters” submenu offering conditions like “Greater Than,” “Top 10,” or “Between.”
Best for: Filtering based on the calculated values rather than the category names themselves — for example, showing only salespeople whose total sales exceed $50,000, rather than manually selecting which salespeople to include or exclude by name.
Example: With Salesperson in Rows and Amount in Values, open the Row Labels dropdown, go to Value Filters, choose “Greater Than,” and enter 50000. Only salespeople whose total exceeds that threshold remain visible.
The trap: Value filters are dynamic — if your underlying data changes and someone’s total now exceeds or falls below the threshold, they will appear or disappear from the view automatically on refresh. This is often desired behavior, but it surprises people who expect a fixed list of names.
Method 4: Slicers
Slicers are visual filter buttons that sit outside the pivot table and make filtering more visible and interactive. Found under the Insert tab, or under PivotTable Analyze > Insert Slicer.
Best for: Dashboards and reports where the filter status needs to be immediately visible to anyone looking at the report, and where the audience may want to interact with the filter themselves.
The advantage over the other methods: Unlike the Filters box or Row Label filters, a slicer’s current state is always visible on the worksheet itself — there is no hidden dropdown to forget about. Slicers are covered in full detail in a separate tutorial since they deserve dedicated treatment.
Method 5: Search Filter
Inside the Row Labels or Filters dropdown, there is a search box at the top. Typing a partial name filters the checklist itself, making it faster to find and select specific items in a long list — useful when you have two hundred unique product names and need to find three specific ones.
Best for: Long lists where scrolling through every value to find the ones you want would be impractical.
Important distinction: This search box filters the list of checkboxes you are looking at, not the pivot table directly. You still need to confirm your selection (click OK) for it to actually filter the pivot table.
How to Check What Filters Are Currently Active
Given how easy filters are to forget, checking your current filter state before trusting any pivot table output is a habit worth building.
Check the Filters box area: Any field placed in the Filters area with a value selected other than “(All)” shows the selected value or “(Multiple Items)” directly in the dropdown above the pivot table. If it says “(All),” no filtering is happening through this method.
Check for funnel icons: Look at the Row Labels and Column Labels dropdown buttons. A small funnel icon appearing on these buttons (rather than the plain dropdown arrow) indicates an active filter on that field.
Use the PivotTable Analyze tab: Click anywhere in your pivot table, go to the PivotTable Analyze tab, and look for Clear > Clear Filters. If this option is available (not grayed out), filters are currently active somewhere in your pivot table.
I now check this before presenting any pivot table output in a client meeting, specifically because of the experience I described at the beginning of this article. It takes five seconds and has saved me from embarrassing situations more than once.
Filtering by Date Ranges
Date filtering deserves separate mention because it has its own dedicated options once a date field has been grouped (see the date grouping tutorial for the grouping step itself).
With a grouped date field in the Filters area or as Row Labels, the Value Filters submenu often includes date-specific options like “Date Filters” with choices such as “Last Month,” “This Quarter,” “Year to Date,” and “Between” two specific dates.
These relative date filters (“Last Month,” “This Quarter”) are dynamic — they recalculate based on today’s date every time the file is opened, which is extremely useful for recurring reports that should always show “the current month” without manual adjustment, but can be confusing if you expect a filter to stay fixed on a specific historical period.
Removing Filters Completely
To remove a filter from the Filters box: drag the field back out of the box, or click the dropdown and select “(All).”
To remove Row/Column Label filters: open the dropdown and either check “Select All” or click the Clear Filter from [Field Name] option that appears at the top of the dropdown when a filter is active.
To remove all filters across the entire pivot table at once: PivotTable Analyze tab > Clear > Clear Filters. This is the fastest way to reset everything and confirm you are looking at complete, unfiltered data — useful as a troubleshooting first step whenever numbers look unexpected.
Combining Multiple Filter Types
Filters from different methods stack together rather than overriding each other. You can have a Filters box selection for Year, a Row Label filter excluding certain products, and a Value Filter showing only totals above a threshold, all active simultaneously on the same pivot table.
This is powerful but is also exactly how the situation I described at the beginning happens — multiple filters from different sessions accumulate, and tracking what is currently applied becomes harder the more filter types are in use. For complex pivot tables used repeatedly over time, I recommend periodically using Clear Filters and rebuilding only the filters you currently need, rather than letting filters accumulate indefinitely across many editing sessions.
A Practical Troubleshooting Checklist
When a pivot table’s numbers do not match what you expect from the source data, work through this checklist before assuming a deeper problem:
Check the Filters box for any non-"(All)" selections.
Check Row and Column Label dropdowns for funnel icons indicating active filters.
Use PivotTable Analyze > Clear > Clear Filters to reset everything, then compare the totals to your expectation.
If totals still do not match after clearing filters, the issue is likely in the source data range itself (a row excluded from the original selection) rather than in filtering — at which point check the PivotTable Analyze > Change Data Source option to confirm the full intended range is included.
This sequence resolves the vast majority of “my pivot table numbers are wrong” situations I encounter in training sessions, and it takes under a minute to work through completely.
Which filtering method are you currently using, and what specifically does not match your expectations? Describe your situation and I can identify which of these five methods is the right tool, or help troubleshoot what is currently going wrong.