Pivot Table Value Filter Not Working? Fix the Cause

A PivotTable value filter usually seems broken because it is filtering a different summary than you expect, the source values are not being treated as numbers, or another filter is narrowing the results first. Check the value field and its summary calculation, clear conflicting filters, then refresh the PivotTable; these checks resolve most cases in Excel for Microsoft 365 on Windows.

What a PivotTable value filter actually filters

A value filter does not test each source-data row independently. It evaluates the summarized result for each item in a PivotTable field, such as each product, salesperson, or region, and keeps or hides those items according to a condition. For example, a filter for Sum of Sales greater than 1,000 keeps product names whose summarized sales meet that threshold.

The distinction matters because the summary changes with the PivotTable layout. If Region is in the Rows area and Month is in Columns, a value filter on Region may be evaluated against the displayed totals for each region. Add another field to Rows, and the comparison may apply at a different level of detail. The filter is working against the current arrangement, even if that is not the figure you had in mind.

There are also different kinds of PivotTable filters. A label filter tests item names, such as names beginning with a letter. A manual filter lets you select items directly. A value filter tests summarized values, while a Top 10 filter keeps items ranked by a selected measure. Choosing the wrong kind can make a valid condition appear ineffective.

Confirm the field and calculation being used

Start by checking which field’s dropdown you used. Value filters belong to a row or column field’s item list; they are not applied by opening the dropdown for the measure itself in the Values area. In Excel, select the dropdown beside a row or column label, choose Value Filters, and inspect the field and measure named in the dialog.

Next, verify the calculation. A field displayed as Sum of Sales can produce a different result from Count of Sales, Average of Sales, or Max of Sales. If the condition uses the wrong calculation, the filter may retain unexpected items. To inspect or change the calculation, right-click a value in the relevant measure and choose Summarize Values By. You can also use Value Field Settings from the same context menu.

Check that the condition is attached to the intended row or column field.
Check that the dialog names the intended measure, especially when several measures appear in Values.
Confirm whether the calculation should be Sum, Count, Average, or another available summary.
Check the comparison operator and threshold, such as greater than rather than greater than or equal to.

For example, suppose a PivotTable contains Sum of Sales and Count of Orders. A condition of greater than 100 on Count of Orders does not mean sales exceed 100. With multiple measures, read the selected measure in the filter dialog rather than assuming Excel uses the first number you see.

Check whether the source values are really numeric

Numbers imported as text are a frequent cause of misleading summaries. Values such as 250 may look numeric in the worksheet but be stored as text because of an import, a leading apostrophe, spaces, or inconsistent formatting. Depending on the source and PivotTable, the field may be counted instead of summed, or records may not contribute to the total as expected.

Look at the Values area and the field’s summary label. If Excel shows Count of Sales when you expected Sum of Sales, investigate the source column. Test a few source cells by selecting them and checking whether Excel treats them as numbers; also look for blanks, error values, and text mixed into the column. Correct the source data consistently, then refresh the PivotTable. Merely changing the cell format to Number does not convert text into numeric values.

Dates can cause a related problem. A date column containing a mixture of genuine Excel dates and text that resembles dates may group or summarize unpredictably. Normalize the source column so every entry has the same data type. If the source is maintained outside Excel, correct the data there when practical so future refreshes do not restore the problem.

Look for other filters that change the result

Filters combine. A report filter, slicer, timeline, label filter, manual item selection, or another value filter can remove records before you judge the result. For instance, a value filter may be correct for the currently selected year but seem wrong when you compare it with an all-years total. A slicer can also hide an item, making it look as though the value condition removed it.

Temporarily clear other restrictions and check the outcome again. Use the dropdowns on the relevant PivotTable fields to clear their filters, and inspect any slicers or report filters connected to the PivotTable. In Excel for Microsoft 365 on Windows, you can select the PivotTable and use PivotTable Analyze > Clear > Clear Filters to remove PivotTable filters. This is broader than clearing one field’s selection, so note any filters you need to restore.

Also check for a label filter on the same field. A label condition such as Does Not Equal or Begins With can hide items even when their values meet the value-filter rule. Remove the label condition temporarily to isolate the value filter. If the result changes, the value filter was not the only rule affecting the list.

Refresh the PivotTable and verify its source

A PivotTable can display an old snapshot of its source. If rows were added, values corrected, or formulas recalculated after the PivotTable was created, the filter may be evaluating cached results rather than the current worksheet values. Select the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables.

Refresh will not fix an incorrect source range. If new rows are outside the PivotTable’s source, they will not appear simply because you refreshed. Select the PivotTable and choose PivotTable Analyze > Change Data Source to inspect the range. An Excel Table is often a reliable source for data that grows: when you add records directly below a Table, the Table expands, and the PivotTable can use that Table as its source. Refresh the PivotTable after adding records.

For PivotTables built from the Data Model, an external connection, or another source, refresh behavior and available options can differ. Confirm that the connection has updated and that the relevant records are included. If one PivotTable works and another does not, compare their sources and refresh status rather than assuming they share the same cache or data.

Apply the value filter in the right place

Use this sequence to create a clean test in Excel for Microsoft 365 on Windows:

Click inside the PivotTable and identify the row or column field whose items you want to keep or hide.
Open the dropdown beside that field’s label. If field headers are not visible, enable them from PivotTable Analyze > Field Headers.
Choose Value Filters, select the condition you need, and review the measure shown in the dialog.
Enter the threshold and comparison operator, then confirm with OK.
Compare a retained item’s displayed summary with the threshold, taking the current report filters and layout into account.

If the menu does not offer Value Filters, check whether you opened the dropdown for a Values measure rather than a row or column field. Also confirm that the PivotTable has a measure to evaluate and that you are working in a standard Excel PivotTable. PivotTables based on certain external or OLAP sources can expose different filtering and calculation choices, so the available commands may not match a PivotTable built from a worksheet range.

On Excel for Mac, the ribbon layout and wording can vary slightly by release, but the same principle applies: use the field dropdown for a row or column field and look for Value Filters. Excel for the web may also present a different interface or fewer controls than the Windows desktop app. If a command is missing, open the workbook in the desktop version and check whether the PivotTable’s data source supports that operation.

Use a small example to identify the mismatch

Imagine source data with Product, Month, and Sales columns. A PivotTable has Product in Rows, Month in Columns, and Sum of Sales in Values. You choose Value Filters for Product and set Sum of Sales greater than 5,000. Before deciding it failed, ask what total the filter is comparing: the product’s overall displayed total, a month-specific value, or a total affected by a report filter such as Year.

Now suppose the result includes a product with a visible total of exactly 5,000. Check whether the selected condition was greater than or greater than or equal to, whether the value is rounded for display, and whether the displayed measure is the same one selected in the filter dialog. A cell formatted with zero decimal places can display 5,000 even when its underlying value is 5,000.49. The filter evaluates the underlying summary, not the rounded appearance.

For a quick diagnosis, temporarily simplify the layout: keep only the item field in Rows and the intended measure in Values, remove other filters, refresh, and apply the condition again. If it behaves correctly in the simplified PivotTable, restore fields and filters one at a time. This reveals whether the cause is the layout, a competing filter, or stale or inconsistent source data.

Frequently Asked Questions

Why is Value Filters greyed out in my PivotTable?
Usually, the dropdown is not for a row or column field, or the PivotTable’s source does not support the same filtering options as a standard worksheet-based PivotTable. Click a row or column label dropdown and check the source type. If the menu remains unavailable, try the desktop Excel app and review the PivotTable connection or data model.

Why does my PivotTable show Count instead of Sum?
Excel may have encountered text, blanks, or mixed data types in the source field, so it selected Count or cannot summarize the field as expected. Check the field label in Values and inspect the source column. Convert text-formatted numbers into actual numbers, make the column consistent, and refresh.

Does a value filter use the displayed, rounded number?
No. Number formatting changes how a result looks, not the underlying value used for comparisons. A total displayed as 5,000 may be slightly above or below that amount. Increase the displayed decimal places temporarily to check the actual summary.

Why does my value filter change when I add another field?
Adding a field changes the PivotTable’s level of detail and may change which summarized results are compared. Review the field arrangement, selected measure, and active report filters. Test the condition with a simpler layout, then add fields back one at a time.

Will refreshing fix a value filter that misses new records?
Only if the new records are already included in the PivotTable’s source. Refresh updates the PivotTable from its source, but it does not automatically correct a fixed range that excludes added rows. Check PivotTable Analyze > Change Data Source, expand the range or use an Excel Table, and refresh again.

Can I filter a PivotTable by a value from the original rows?
A PivotTable value filter evaluates a summarized value for each PivotTable item, not an independent test on every source record. If you need to exclude individual source rows based on a condition, filter or add a helper field in the source data first, then refresh the PivotTable.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.