Excel Pivot Table Filter: Count Greater Than a Value

Charts & pivot tables · Updated November 19, 2026

To show only PivotTable items whose count exceeds a number, apply a value filter: open the dropdown for the row or column field, choose Value Filters > Greater Than, select the Count measure, and enter the threshold. If the measure is currently a sum, change its summary calculation to Count first.

Set up a count filter in Excel

These steps apply to Microsoft Excel for Microsoft 365 on Windows. A PivotTable value filter compares each row or column item’s calculated value with your threshold. For example, it can keep only departments with more than 10 tickets.

  1. Click a cell in the PivotTable. In the PivotTable Fields pane, put the field you want to group by, such as Department, in Rows. You can use Columns instead if the categories run across the top.
  2. Drag the field you want to count, such as Ticket ID, into Values. If the field is text, Excel will commonly summarize it as Count; if it is numeric, Excel may choose Sum.
  3. If needed, change the calculation. Right-click a value in the PivotTable, choose Summarize Values By, and select Count. You can also open Value Field Settings for the value field and select Count on the Summarize Values By tab.
  4. Open the dropdown beside the row or column field heading. Depending on the layout, it may appear as Row Labels or as the field name. Choose Value Filters > Greater Than.
  5. In the filter dialog, select the Count measure, check that the comparison is greater than, enter the threshold, and select OK.

The PivotTable now hides categories whose count is at or below the threshold. To include categories equal to the threshold, choose Greater Than Or Equal To if that option appears in the Value Filters menu. The filter is based on the current PivotTable calculation, not on a count typed into a worksheet cell.

What Count means in a PivotTable

In a PivotTable, Count summarizes how many nonempty source entries occur in a group. If the source has one row per ticket and every ticket has a Ticket ID, Count of Ticket ID is a practical way to count tickets by department. A blank ID is not counted, so an incomplete source field can make the result smaller than the number of records.

Count is not the same as Sum, and it is not automatically a count of unique values. If a customer appears on five source rows, Count of Customer may contribute five, not one, to the group total. Likewise, counting a field with blanks can produce a different result from counting a field that is filled on every record. Choose a reliable identifier when the question is how many records belong to each category.

There is also a distinct-count calculation, which counts unique values rather than rows. In a standard PivotTable, that choice is available when the PivotTable uses the Data Model; it is not interchangeable with ordinary Count. Use distinct count for questions such as how many different customers placed orders, and ordinary Count for how many order records there are.

Worked example: departments with more than 10 tickets

Suppose a source table has columns Department and Ticket ID, with one ticket per row. The PivotTable places Department in Rows and Ticket ID in Values, summarized by Count. Its result might show Operations 12, Sales 8, Support 15, and Finance 10. Applying Value Filters > Greater Than to the Department field and entering 10 leaves Operations and Support. Finance is excluded because 10 is not greater than 10.

This example also shows why the filter is applied to Department, the field whose items you want to keep or hide. The Count of Ticket ID is the measure being tested. If the PivotTable instead counts a field that is blank on two Operations records, Operations might show 10 rather than 12 and would no longer pass a greater-than-10 test. Check the source field and the displayed aggregate before interpreting the outcome.

Choose the right field and threshold

A useful filter depends on matching the measure to the question. Count of an always-filled transaction ID answers how many records are in each category. Count of a notes field answers how many records have a note, which may be a narrower question. Distinct Count answers how many unique entities appear. If the intended measure is a quantity or revenue total, use Sum rather than Count; a count filter cannot substitute for a sum filter.

Also decide whether the boundary should be included. Greater Than 10 means 11 or more when the counts are whole numbers. Greater Than Or Equal To 10 includes 10. For a range, use Between where available and enter both boundaries. These choices matter when someone asks for a minimum count, such as at least 10, because the everyday phrase at least means greater than or equal to, not strictly greater than.

Counts are usually whole numbers, but other PivotTable measures may be decimals. A greater-than filter tests the calculated value for each category, not a rounded display value. If a result is displayed with no decimal places, its underlying value could still be just below or above the threshold. Increase displayed decimal places when a decimal measure makes the boundary unclear.

Value filters versus label filters

A value filter tests a calculated result, such as Count of Ticket ID greater than 10. A label filter tests the text of the item name, such as Department begins with S. These answer different questions: a label filter could keep every department starting with S regardless of its ticket count, while a value filter keeps departments based on the count regardless of their names.

For a count threshold, apply the value filter to the row or column field containing the categories to be screened. A report-filter field, often displayed above the PivotTable, is primarily used to select particular field items; it is not the right place to test the count of each category. If the menu you opened does not offer Value Filters, check that you opened the dropdown for a row or column label field rather than a report filter or a value cell.

Filters on different fields can work together. For instance, you might first select one year in a report filter and then apply a count threshold to departments. The count filter then evaluates the PivotTable results for the selected year. Changing that year can change which departments qualify, because the underlying counts have changed.

Common problems and how to fix them

  • The menu shows Label Filters but no Value Filters: Open the dropdown on a row or column label field. Also confirm that a value field has been placed in Values; without a measure, there is no calculated count to compare.
  • The PivotTable shows a sum instead of a count: The source field may be numeric, so Excel selected Sum automatically. Right-click a displayed value and choose Summarize Values By > Count, then apply the filter again.
  • A category you expected is missing: Check for blank IDs, filters on other fields, and whether the threshold uses strict greater than or greater than or equal to. A category with a count exactly equal to the threshold is excluded by Greater Than.
  • The count is unexpectedly high: Ordinary Count counts populated source rows, including repeated IDs. If the question is about unique customers or products, use Distinct Count with a Data Model PivotTable instead.
  • The filter seems to have no effect: Confirm that the selected measure is the Count field and that the entered threshold is sensible for the scale of the data. Also check whether the PivotTable is already narrowed by another field selection.

When changing the value-field calculation after setting a filter, revisit the filter dialog and confirm it still refers to the intended measure. PivotTables may have multiple value fields, such as Count of Ticket ID and Sum of Hours, and choosing the wrong one can produce a valid-looking but irrelevant result.

Refreshes, source changes, and layout

A PivotTable summarizes a cached view of its source data. If source rows are added or edited, refresh the PivotTable so its counts and filter results reflect those changes: right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If newly added rows are outside the PivotTable’s source range, update the source through PivotTable Analyze > Change Data Source or use an Excel table as the source so that added table rows are included when refreshed.

After a refresh, the threshold rule remains useful, but the categories that pass may change because the counts have changed. If an item disappears, it may simply no longer meet the filter or may not exist in the refreshed source. To inspect all categories, reopen Value Filters and clear the filter, or use the field dropdown’s clear-filter option. Be careful not to confuse clearing a value filter with removing the whole PivotTable.

Excel for Mac has PivotTable field and filter controls, though their placement and labels can vary somewhat by release. Look for the field dropdown and its Value Filters menu, and use the value-field settings to select Count. Excel for the web may expose a different set of PivotTable controls; if the needed filter or Data Model option is unavailable, open the workbook in the desktop app.

When a PivotTable filter is not the best fit

A value filter is convenient when you want an interactive summary and a result that updates after refresh. It hides items in the PivotTable without deleting the original records. It is usually the clearest choice for comparing groups, such as products with more than a chosen number of orders.

For a one-time list of qualifying records rather than categories, filter the source table itself or create a separate summary. For a report that must show an explicit count beside every category, leave the count visible in Values and apply the value filter; hiding the count column can make the reason for inclusion less obvious to readers. A worksheet formula or a separate summary table can be more suitable when the threshold must be driven by a cell or when the output needs a custom layout, but those approaches require maintaining their own logic.

Frequently Asked Questions

How do I filter a PivotTable to show count greater than 5?

Put the category field in Rows, put a reliably populated field in Values, and set that value field to Count. Open the category dropdown, choose Value Filters > Greater Than, select the Count measure, enter 5, and confirm. Only categories with a count above 5 remain visible.

Does Greater Than include the number I enter?

No. Greater Than 5 excludes a count of exactly 5. Choose Greater Than Or Equal To when the threshold should include 5, as in the phrase at least five.

Why does my PivotTable count show a sum?

Excel may default to Sum when the source field contains numbers. Change the value field’s summary calculation to Count using Summarize Values By or Value Field Settings. Then make sure the value filter tests that Count measure.

Can I count unique items instead of rows?

Yes, use Distinct Count in a PivotTable based on the Data Model. Ordinary Count counts populated entries, including repeated values; it does not remove duplicates.

Will the count filter update when I add data?

It updates after the PivotTable is refreshed, provided the new rows are included in the source range. Refresh the PivotTable and verify its data source if the added records are not appearing.

Related

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.