To filter an Excel PivotTable for values greater than a threshold, open the drop-down for a Row or Column field, choose Value Filters > Greater Than, select the value field to test, and enter the threshold. This filters categories according to their PivotTable calculation—for example, salespeople whose total sales exceed $5,000—not necessarily individual source-data records above $5,000.
What a Greater-Than Filter Does
A PivotTable summarizes records into groups, such as sales by salesperson, orders by region, or expenses by month. A greater-than value filter keeps only the groups whose calculated value is above the number you specify. It is useful when you want to focus a report on the categories that meet a performance, volume, or cost threshold.
For example, suppose a source table has one row per order, with columns for Region, Salesperson, and Order Amount. If the PivotTable puts Salesperson in Rows and Sum of Order Amount in Values, a greater-than filter of $5,000 displays salespeople whose combined order amounts exceed $5,000. It does not simply list orders over $5,000. That distinction matters whenever the source has multiple records per group.
“Greater than” means strictly above the threshold. A total exactly equal to $5,000 is excluded when the condition is greater than $5,000. If the threshold itself should count, look for the greater-than-or-equal condition in the filter options instead.
Apply the Filter in Excel for Microsoft 365
These steps apply to Excel for Microsoft 365 on Windows. First build or select a PivotTable that has a category field in Rows or Columns and a numeric field in Values. The category field is what Excel will show or hide; the value field supplies the calculation being tested.
- Click the drop-down arrow beside the row or column labels you want to filter. Depending on the PivotTable layout, this may appear as the field name, such as Salesperson, rather than as “Row Labels.”
- Point to Value Filters, then choose Greater Than. If Value Filters is unavailable, check that the PivotTable has a numeric field in the Values area and that you opened the menu for a Row or Column field.
- In the filter dialog, choose the value field to evaluate, such as Sum of Order Amount. This selection is important when the PivotTable contains more than one measure.
- Enter the cutoff, such as 5000, in the comparison box, then confirm with OK.
Excel updates the PivotTable to show only categories whose selected calculation is greater than the cutoff. The menu’s exact wording or location can vary slightly with the PivotTable layout, but the relevant choice is a value filter—not a label filter. On Excel for Mac, use the field drop-down in the PivotTable and look for the equivalent Value Filters command; ribbon and menu placement may differ from Windows. Excel for the web may offer a narrower set of PivotTable controls, so use the desktop app if the needed filter command is not available.
Choose the Right Value and Aggregation
A PivotTable filter evaluates the value field’s current summary calculation. In the example, Sum of Order Amount tests each salesperson’s total. If the field is instead set to Count of Order Amount, the same greater-than condition tests the number of records, not their dollar value. Average, Max, and other summaries also produce different results.
Before filtering, inspect the Values area in the PivotTable Fields pane and confirm the measure’s name and calculation. To change it, right-click a value in the PivotTable and choose Summarize Values By, then select an appropriate calculation. You can also open Value Field Settings from the field’s menu. The names and available summaries depend on the data and PivotTable type.
Imagine three salespeople have order totals of $4,000, $5,000, and $6,200. A filter for greater than $5,000 keeps only the person with $6,200. The person with exactly $5,000 is excluded. If you switch from Sum to Count, the filter instead compares how many order records each person has with 5,000—almost certainly not the intended test in this example.
Value Filters Versus Label and Report Filters
Excel has several kinds of PivotTable filters, and their names describe different tests. A Value Filter compares a summary such as Sum of Sales with a number. A Label Filter compares the text or date labels in a field. A Report Filter, shown above the PivotTable when a field is placed in the Filters area, lets you select items from that field; it is not generally a direct way to enter a numeric greater-than condition.
For instance, a Label Filter greater than “M” compares category names alphabetically. It does not test sales amounts. Likewise, if you put Order Amount in the Filters area and select displayed amount items, that is not the same as filtering each salesperson by their summed amount. For “show regions with total sales above $50,000,” use a Value Filter on the Region field and select the relevant sales measure in the dialog.
Keep the filter’s target in mind: the drop-down belongs to the category field whose items you want to keep or remove, while the dialog’s value-field choice determines which summarized number Excel compares.
Filter Individual Source Records Instead
One common misunderstanding is expecting a PivotTable value filter to remove individual source rows before calculating totals. It does not. It first summarizes records into PivotTable categories, then filters those categories according to the summary. Thus, filtering salesperson totals above $5,000 does not mean “include only orders whose individual amount is above $5,000.”
If you need to exclude source records below a threshold before summarizing, filter the source table or create a helper field that marks records meeting the rule, then refresh the PivotTable and arrange the report around that field. You can also use a source-data filter when preparing the data for analysis. Be clear about whether the business question concerns each transaction, each category’s total, or another calculation; those are different operations and can produce very different results.
For a one-off source-table filter, select a cell in the source table and use the filter arrow on the numeric column to choose Number Filters > Greater Than. This changes which source records are visible, but a PivotTable’s behavior depends on its source and refresh setup; hiding rows in a worksheet is not always equivalent to removing records from the PivotTable’s source. For a reliable analysis, use a deliberate source filter or helper field and verify the resulting PivotTable after refresh.
Refreshes, Multiple Filters, and Sorting
A PivotTable works from its source data or its PivotCache, so changes to the underlying records may not appear until the PivotTable is refreshed. Use PivotTable Analyze > Refresh to update the selected PivotTable, or Data > Refresh All to refresh workbook connections and PivotTables. After refreshing, check that the filter still expresses the condition you intend and that the source includes any newly added rows.
You can apply filters to more than one field, and Excel combines them to narrow the displayed categories. For example, you might filter Region to East and then apply a greater-than Value Filter to Salesperson totals. If the result is empty, the conditions may simply have no categories in common. Clear or adjust one condition at a time to identify which filter is excluding the expected items.
Filtering and sorting are separate. A greater-than filter hides categories below the cutoff; sorting changes the order of the categories that remain. Use the field’s sort options if you also want the largest totals at the top. A Top 10 Value Filter is another option when the goal is to show a fixed number of highest-performing categories rather than every category above a particular cutoff.
Common Problems and Fixes
- Value Filters is missing: Open the drop-down for a Row or Column field, not a value cell or an unrelated report filter. Confirm that the PivotTable has a value in its Values area.
- The wrong results appear: Reopen the dialog and verify the selected measure and its aggregation. Sum, Count, and Average answer different questions.
- A number field is treated oddly: Check the source column for numbers stored as text, blanks, or inconsistent entries. Correct the source data, then refresh the PivotTable.
- A category at the threshold disappears: That is expected with “greater than.” Use “greater than or equal to” if values equal to the cutoff should remain.
- New records are missing: Refresh the PivotTable and verify that its source range includes the added rows. Converting the source range to an Excel table can help its range expand as records are added.
- A field item seems to remain visible: Check for other filters, subtotals, or layout choices, and reopen the filter menu to confirm its active condition. Clearing a filter removes the condition; it does not delete source data.
When a Different Tool Is Better
A PivotTable value filter is a strong choice for interactive summaries by category. It is less suitable when you need a row-level list of qualifying transactions, a reusable calculation that updates in a specific way, or a report with complex logic across several conditions. For row-level analysis, filter the source table. For a custom interactive report, a PivotTable with helper columns, multiple measures, or slicers may be a better fit.
If you need a threshold displayed for every category rather than only hiding categories that fail it, consider adding the measure and comparing it with a target in a separate report area. A PivotTable filter removes nonmatching items from view; it does not by itself explain how far each item is above or below the target. Keep an unfiltered copy or clear the filter when readers need to see the full comparison.
Frequently Asked Questions
Can I filter a PivotTable for values greater than a number?
Yes. Open the drop-down for the relevant Row or Column field, choose Value Filters > Greater Than, select the value field, and enter the cutoff.
Does a greater-than filter include the cutoff value?
No. “Greater than 5000” excludes 5,000. Choose a greater-than-or-equal condition when the cutoff itself should be included.
Can I filter salespeople whose total sales exceed a target?
Yes. Put Salesperson in Rows and the sales amount in Values, set the measure to the intended summary—often Sum—and apply a Value Filter to Salesperson using that measure.
Why does my filter use Count instead of Sum?
The PivotTable may be summarizing that field by Count, or the field may contain values Excel does not recognize consistently as numbers. Check Value Field Settings and inspect the source data.
Can I filter individual orders greater than a threshold with a PivotTable?
A PivotTable Value Filter tests category summaries, not each underlying order. Filter the source records or add a helper field if the condition needs to apply to each transaction before summarizing.
Will the filter update when I add data?
Refresh the PivotTable and make sure its source includes the new rows. An Excel table used as the source can expand as rows are added, but the PivotTable still needs a refresh to reflect updated data.
Related
- Excel Number Format for Negative Numbers in Parentheses
- Excel Number Format for Lakhs With Commas
- Excel Number Format for Millions With One Decimal Place
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Open a Pivot Table Menu in Google SheetsTo open the pivot table controls in Google Sheets, click any cell inside the pivot table; the Pivot…
- Excel Pivot Table: How to Use the Same Field TwiceYes. In Excel for Microsoft 365 on Windows, you can add the same source field to a PivotTable’s…
- Excel Pivot Table Median Instead of AverageYes—but a standard Excel PivotTable cannot choose Median in its usual Value Field Settings list. In Excel for…
- How to Make a Graph in Excel with Multiple Data SeriesTo make a graph in Excel with multiple data series, arrange each series in its own column or…
- Chart Not Showing in Excel? Causes, Checks, and FixesIf a chart is missing or blank in Excel for Microsoft 365 on Windows, first check whether Excel…
- Waterfall Chart in Excel: Setup and TroubleshootingTo set up a waterfall chart in Excel for Microsoft 365 on Windows, put each step and its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.