PivotdrillValue filters
Open the Row Labels dropdown, choose Value Filters, then Greater Than, Less Than, Between or Equals, pick the value field and type the number. The condition tests each row's aggregated total, not the individual source rows.
Syntax
Row Labels dropdown > Value Filters > Greater Than... > field, value| Part | What it is |
|---|---|
Equals / Does Not Equal | Keep rows whose total matches or does not match a number |
Greater Than / Less Than | Also Greater Than Or Equal To and Less Than Or Equal To |
Between / Not Between | Two numbers, inclusive |
Top 10 | Rank-based version, covered on its own page |
Examples
Rows: Region
Values: Sum of Revenue
Value Filters > Greater Than > Sum of Revenue > 10000Result: Only regions whose total revenue exceeds 10,000 remain.
Rows: Product
Values: Count of Order ID
Value Filters > Less Than > 5Result: Products with fewer than five orders, useful for spotting slow movers.
Rows: Department
Values: Average of Salary
Value Filters > Between > 50000 and 70000Result: Departments whose average salary sits in that band.
Common mistakes
- The filter uses the wrong column: the dialog has a field dropdown; pick the right value field when there are several.
- Label Filters and Value Filters cannot both be on unless you allow it: PivotTable Options, Totals & Filters, tick Allow multiple filters per field.
- Rows filtered out still count in % of Grand Total: with a value filter applied the totals reflect visible rows only, so apply percentages first if you need the full base.
Practice Value filters
These drills check your answer on live data, free, no account needed.
- High priority workloadCount tickets by Team with Status across the top, but only tickets whose Priority is High or Urgent.
- Approved spending onlyShow total Amount by Category, counting only claims whose Status is Approved.
Questions
Does a value filter look at source rows or totals?
Totals. Greater Than 1000 on Sum of Revenue keeps every region whose summed revenue is above 1000, even if no single order is.
How do I filter a pivot table by a value in the source data?
Add that field to the Filters area or to Rows and filter its labels, or filter the source data before refreshing.
Related
- Top 10 filterUse Value Filters, Top 10 to keep only the top 5, the bottom 3 or the items that make up 80 percent of a total. Steps, the Items, Percent and Sum modes.
- Filter a pivot tableFour ways to filter a pivot table: the row label dropdown, the Filters area above the table, Value Filters on totals, and slicers. Examples and catches.
- Percent of grand totalShow each row as a share of the overall total with Show Values As, % of Grand Total. Add it next to the raw numbers, format it, compare with parent totals.