You cannot apply a filter to the PivotTable’s Grand Total row or column as if it were a regular field item. To keep or hide row or column items according to their summarized totals, use a Value Filter on the relevant field; if you need a rule against the overall Grand Total itself, use a different approach such as filtering the source data or building a helper calculation.
What a Grand Total Filter Can—and Cannot—Do
A PivotTable’s Grand Total is a result calculated from the items currently included in the report. It is not a source-data record or a field with its own filter menu. That is why clicking a Grand Total cell does not give you a command to show only totals above a threshold. The Grand Total row and column can be displayed or hidden, but they cannot be selected as ordinary items in a field filter.
There is, however, a closely related feature that solves many requests. A Value Filter applies a condition to items in a row or column field, using a summarized value such as Sum of Sales or Count of Orders. For example, you can show only product categories whose sales exceed $10,000. The filter hides categories that fail the test, and the displayed totals update to reflect the items that remain.
The distinction matters when a PivotTable has several fields. A Value Filter evaluates the selected field’s items using the chosen value field and the PivotTable’s current layout and selections. In a simple table, that can correspond to each row’s total. With nested fields or multiple column fields, it may not mean the single overall Grand Total you had in mind. Check the result against a small example before relying on it.
How Value Filters Work
PivotTables separate label filtering from value filtering. A label filter tests the text or date label, such as keeping product names that begin with “A.” A Value Filter tests an aggregate calculated for each item, such as Sum of Revenue, Average of Units, or Count of Records. The values come from the PivotTable’s summary calculation, not necessarily from a single source row.
For example, suppose a source table has transactions for several regions and products. A PivotTable with Region in Rows and Sum of Sales in Values calculates sales for each region. A Value Filter on Region can retain regions whose summarized sales meet a threshold. If Year is in Columns, the report also displays a Grand Total across the visible years; depending on the layout and filter, confirm that the field’s condition is testing the total you intend rather than a particular intersection of fields.
Value Filters can use conditions such as greater than, less than, equals, between, and Top 10. Top 10 is a label for a feature that can show a chosen number or percentage of items based on a value field; it does not mean the ten largest source rows. The available choices depend on the field and report context.
Apply a Value Filter in Excel for Microsoft 365
- Click a cell in the PivotTable so Excel displays the PivotTable tabs.
- Open the drop-down arrow for the row or column field whose items you want to filter. In Compact Form, the menu may appear beside Row Labels or Column Labels; in a layout with separate field headers, use the relevant field’s arrow.
- Point to Value Filters, then choose a condition such as Greater Than, Between, or Top 10.
- In the dialog, select the value field to test, such as Sum of Sales, and enter the threshold or other settings.
- Choose OK and inspect the visible items and totals. If the result is not what you expected, check which field’s menu you used and which value field the dialog names.
For instance, to show regions with more than $10,000 in sales, open the Region field menu, choose Value Filters > Greater Than, select Sum of Sales, enter 10000, and confirm. This filters Region items using the summarized sales value. It does not filter the Grand Total cell itself. If the value field is named differently in your PivotTable, choose its actual displayed name.
In Excel for Mac, the same general operation is available from the field drop-down in the PivotTable, though ribbon labels and menu placement can vary by release. Excel for the web also supports many PivotTable filtering tasks, but if a command is unavailable in the web interface, open the workbook in the desktop app and use the field menu there.
Choose the Right Field and Value
The field menu you open determines which items Excel can keep or hide. If products are in Rows, filtering the Product field acts on product items. If Region is in Filters, that is a report filter for choosing regions; it is not the same as a Value Filter on a row field. Select the field whose members you actually want to remove from the report.
The value field determines what Excel measures. A PivotTable may contain Sum of Sales, Count of Orders, and Average of Margin at the same time. A Value Filter on Product can use any suitable value field in the dialog. Be explicit about the aggregation: filtering by Sum of Units is different from filtering by Average of Units, even if both use the same source column.
- Use Sum when the question is about total volume or revenue.
- Use Count when the question is how many records or nonblank entries an item has.
- Use Average when the comparison should be based on a per-record mean rather than overall volume.
- Use a label or date filter when the criterion concerns the item’s name or date, not its summarized number.
Excel summarizes numeric fields by Sum by default in many cases, but the selected calculation may be different. Check the value field caption and its Value Field Settings before interpreting a threshold. A sum threshold and an average threshold answer different questions.
Grand Totals, Subtotals, and What Changes
Showing or hiding Grand Totals is a display setting, not a way to filter items. In Excel for Windows, click the PivotTable and use PivotTable Design > Grand Totals to choose whether totals appear for rows, columns, both, or neither. Removing a Grand Total from view does not remove records from the PivotTable’s calculations; it only hides that displayed total.
Subtotals are also different from filters. They summarize groups in a field hierarchy. If a report has Category above Product in Rows, a category subtotal summarizes its products, while the Grand Total summarizes the report as a whole. You can adjust subtotal display from the PivotTable Design tab, but subtotal visibility does not create a condition that filters products by their subtotal.
After a Value Filter hides items, the displayed report and its totals generally reflect the remaining visible items. A report filter or slicer can also change which source records contribute to the PivotTable. These are useful ways to narrow a report, but they do not turn a Grand Total into a filterable field. Be especially careful when explaining a filtered total to others: it may represent only the selected or retained items, not the entire source table.
When the Condition Must Use an Overall Total
Some requests really do mean “keep each row only if its Grand Total across all displayed columns exceeds this number.” Try a Value Filter on the row field and choose the relevant measure first. In a straightforward layout, this often provides the needed result. If several column fields, report filters, or nested row fields make the outcome ambiguous, simplify the PivotTable temporarily: remove extra fields, apply the Value Filter, and add fields back while checking which items remain.
If the rule must be tied to a specific final total or must remain unambiguous as the PivotTable layout changes, consider preparing the logic outside the PivotTable. Options include:
- Add a calculated flag or category to the source data when the rule can be evaluated for each source record or entity before summarizing.
- Create a separate summary table with formulas or a query that calculates one total per item, then filter that table.
- Use a PivotTable Value Filter for an interactive report, documenting the value field and aggregation used.
A source-data helper column cannot automatically know every possible PivotTable selection unless its logic is designed for that purpose. For example, a flag based on all-time sales will not necessarily match a PivotTable total after a user filters the report to one year. Decide whether the condition belongs to the underlying data or to the current report selection before choosing a method.
Common Problems and Checks
If Value Filters is missing or unavailable, confirm that you opened a row or column field menu rather than clicking the Grand Total cell, a value cell, or a report-filter control. Make sure the PivotTable is active. Also check that the field has items and that the report contains an appropriate summarized value field.
If the filter keeps unexpected items, check these points:
- Confirm the condition uses the intended field, such as Sum of Sales rather than Count of Sales.
- Check whether a report filter, slicer, or another field filter has already narrowed the data.
- Look for nested row or column fields that change the context in which the item values are summarized.
- Verify whether the threshold is inclusive or exclusive by choosing the exact condition you need, such as Greater Than versus Greater Than or Equal To when available.
- Refresh the PivotTable after changing the source data. On Windows, use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.
A PivotTable may also show old item names after a source change, or omit newly added records if its source range does not include them. Refreshing updates the cache from the defined source, but it does not automatically repair an incorrectly bounded source range. Check the table or range used by the PivotTable if recent records are missing.
Alternatives for Interactive Filtering
If readers need to choose categories rather than impose a numeric threshold, a report filter or slicer may be clearer. A report filter displays a field selector above the PivotTable. A slicer provides clickable buttons for field items and makes active selections easier to see. In Windows Excel, select the PivotTable and use PivotTable Analyze > Insert Slicer, then choose the field. Slicers filter the report by item selection; they do not directly test whether an item’s Grand Total passes a numeric condition.
For repeatable comparisons, a Value Filter is usually simpler than manually unchecking many items. For a fixed publication or dashboard where the exact comparison rule matters, a separate summary table can be more transparent. It allows the condition to be shown next to each total and can reduce confusion about how multiple PivotTable fields affect the result.
Another useful option is to keep the PivotTable as the calculation engine but place a linked report or chart beside it. That can make selections and totals easier to interpret, though linked outputs still depend on the PivotTable’s filters and refresh state. Whatever approach you choose, label the measure, aggregation, and scope—for example, “Sum of Sales, selected years”—rather than calling every displayed number simply a grand total.
Frequently Asked Questions
Can I filter the Grand Total row directly in Excel?
No. A Grand Total row or column is a calculated summary, not a field item. Apply a Value Filter to the relevant row or column field if you want to keep items according to a summarized value.
How do I show only PivotTable rows above a certain total?
Open the row field’s drop-down, choose Value Filters > Greater Than, select the measure such as Sum of Sales, enter the threshold, and confirm. Verify the resulting rows, particularly if the PivotTable has multiple fields.
Can a Value Filter use the row’s Grand Total across columns?
It can serve that purpose in a simple layout, but the outcome depends on the PivotTable’s fields and selections. Test it with a small example and confirm that the selected value field and item context match the total you intend to evaluate.
Does hiding Grand Totals remove them from the calculation?
No. Hiding Grand Totals changes what is displayed, not which source records are summarized. To exclude items or records, use an appropriate field filter, report filter, slicer, or source-data rule.
Why can’t I see Value Filters?
Open the drop-down for a row or column field, not the Grand Total cell or a value cell. If the option still does not appear, activate the PivotTable and check that it contains a suitable value field.
Will the filter update when the source data changes?
The PivotTable may need a refresh to include updated source values. Use PivotTable Analyze > Refresh on Windows, and confirm that the PivotTable’s source range includes any newly added records.
Related
- Excel Fill Color Shortcut: Apply Color Quickly
- Excel Keyboard Shortcuts for Selecting Cells Quickly
- Excel Number Format to Show Values in Thousands
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Chart Labels in Google Sheets: Add, Edit, and FormatTo add chart labels in Google Sheets, open the chart, choose Customize in the Chart editor, then use…
- Balanced Graph in Mathematics: Meanings and ExamplesA balanced graph does not have one universal meaning across mathematics: the definition depends on the area and…
- Pie Chart Creator in Excel: Make and Format a ChartExcel has a built-in pie chart creator: select a category-and-value table, then choose Insert > Insert Pie or…
- Excel Chart: How to Align Zero on a Secondary AxisTo align zero on an Excel chart’s secondary axis with zero on its primary axis, set fixed minimum…
- Excel Pivot Chart Trendline: What Works and What Does NotYou cannot add a trendline directly to a PivotChart in Excel for Microsoft 365 on Windows. To show…
- Excel Gantt Chart Dates: Formulas, Setup, and TipsFor a basic Excel Gantt chart, calculate each task’s finish date with =B5+C5-1 when B5 is its start…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.