Excel Pivot Table Filter: Less Than an Amount?

Charts & pivot tables · Updated November 19, 2026

To filter a PivotTable to amounts less than a threshold in Excel for Microsoft 365 on Windows, open the drop-down for a row or column field, choose Value Filters > Less Than, select the value field to test, enter the limit, and select OK. Excel then displays the row or column items whose summarized value is below that amount; it does not simply hide individual source records below the limit.

What a less-than PivotTable filter does

A PivotTable value filter compares an aggregate for each item in a row or column field with a condition you specify. For example, if a PivotTable lists salespeople in rows and Sum of Amount in Values, a less-than filter can show only salespeople whose total sales are below $10,000. It evaluates each salesperson’s summarized amount, not each transaction separately.

This distinction matters because the result depends on the PivotTable’s structure and calculation. If the Values area uses Sum, Excel tests each item’s sum. If you change the summary to Average, it tests the average instead. Adding another row field can split the figures into smaller groups, so the same threshold may keep or remove different items. The filter is about the displayed PivotTable calculation, not necessarily the raw amount in a particular source row.

Value Filters are available for fields placed in the Rows or Columns areas. A report filter, also called a page filter, is different: it selects field items directly and does not provide the same less-than comparison against a summarized value.

How to apply the filter in Excel

  1. Click a cell in the PivotTable and confirm that the field you want to filter is in Rows or Columns. Also confirm that the measure you want to compare, such as Amount, is in Values.
  2. Open the drop-down arrow beside the row or column field whose items you want to include or exclude. In a compact layout, the control may appear beside Row Labels or Column Labels; in other layouts, it may appear beside the field’s own heading.
  3. Point to Value Filters and select Less Than.
  4. In the filter dialog, choose the value field to evaluate, keep or select the less-than condition, and enter the threshold. Select OK.

The field choice is important when the PivotTable has more than one value, such as Sum of Amount and Count of Orders. Select the measure that represents the comparison you intend. The filter is applied to the items of the field whose drop-down you opened; it is not a workbook-wide filter on every number in the PivotTable.

A worked example with sales totals

Suppose the source data has one transaction per row, with columns for Salesperson and Amount. A PivotTable places Salesperson in Rows and Amount in Values, summarized by Sum. If the results are Ana $8,450, Ben $12,300, and Chris $9,700, applying Value Filters > Less Than to Salesperson and entering $10,000 leaves Ana and Chris visible. Ben is hidden because his summarized total is not less than the limit.

If you instead want to find individual transactions below $10,000, a value filter on this PivotTable is not the right interpretation: the filter tests each salesperson’s total. You could put the transaction identifier or another row-level field in Rows to create groups at transaction level, or filter the source records by Amount using a suitable data workflow. Be clear about whether the question concerns each record, each customer’s total, an average, or another grouping before choosing a filter.

Choosing the right comparison and value field

Excel offers several value-filter comparisons, including less than, less than or equal to, greater than, greater than or equal to, between, and not between. Choose the operator that matches the boundary you mean. “Less than $10,000” excludes an item whose total is exactly $10,000; “less than or equal to” includes it. For a range, use Between and supply both endpoints.

When there are multiple measures, the selected measure can change the result substantially. A store might have a Sum of Amount above the cutoff but an Average of Amount below it. Likewise, a count of transactions is not a dollar total. Before applying the filter, check the Values area and the displayed captions so you are comparing the intended calculation. If necessary, open the value field’s settings and verify whether it is summarized by Sum, Count, Average, or another available calculation.

A value filter can be combined with other PivotTable selections, such as choosing particular categories from a field drop-down. Remember that each restriction narrows what is displayed. If results seem unexpectedly sparse, clear other filters temporarily and inspect the unfiltered totals.

Value filters versus label filters

Use a Value Filter when the condition is based on a summarized measure, such as sales total below $10,000 or order count above 20. Use a Label Filter when the condition is based on the field’s item names, such as customer names beginning with a letter or product labels containing a word. A label comparison does not test the amount in the Values area.

For example, filtering a Customer field with a label condition that begins with “A” selects customer names; it does not select customers whose purchases are under a specified amount. Conversely, a value filter on Customer can select customers by their summed purchases, but it does not change the text in their names. The two filter types may appear in the same field menu, so check the submenu heading before entering a condition.

What changes the results

Value-filter results are tied to the current PivotTable layout and its current data. These factors can affect which items remain visible:

  • Aggregation: Sum, Average, Count, and other summaries produce different values to compare.
  • Grouping: Adding or removing fields in Rows or Columns changes how source records are grouped.
  • Other filters: Report filters, slicers, and other field selections can limit the records contributing to a displayed total.
  • Threshold: A different cutoff, or changing from less than to less than or equal to, can alter the included items.
  • Source data: New or edited records affect the PivotTable after it is refreshed, provided the source range includes those records.

A value filter is not a permanent rule attached to the source data. It controls which PivotTable items are shown under the current conditions. If the underlying data or layout changes, review the results rather than assuming that the same visible items will remain.

Common problems and how to fix them

Value Filters is unavailable or does not show the expected option. Check that you opened the menu for a field in Rows or Columns, and that the PivotTable contains a value in its Values area. A drop-down for a report filter is not the same control. If the PivotTable layout is unfamiliar, use the PivotTable Fields pane to confirm where each field is placed.

Amounts appear to be ignored. Check that the source Amount values are numeric. Numbers stored as text may be counted or summarized differently from genuine numbers. Inspect the source cells, correct the data if needed, and refresh the PivotTable. Also confirm that the filter is evaluating the intended value field rather than a count or a different measure.

The PivotTable shows no items. The threshold may be below every group’s value, another filter may already be restrictive, or the selected summary may be larger than expected. Clear other field filters and temporarily remove the value filter to inspect the totals. Make sure the comparison is less than, not greater than, and check whether the amount is displayed in whole dollars while the underlying values include cents.

The filter seems to use stale figures. Refresh the PivotTable after changing source data. In Excel for Microsoft 365 on Windows, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If new rows are outside a fixed source range, change the source or convert the source data to an Excel table so added rows can be included when the PivotTable is refreshed.

Refresh, change, or remove the filter

To change the cutoff, reopen the same field’s drop-down, go to Value Filters, and choose Less Than again. Enter the revised value and confirm. To remove the condition, use the field menu’s Clear Filter From command for the filtered field, where available, or choose Clear Filter from the relevant menu. Clearing a filter restores items hidden by that filter, though other filters may still be active.

The threshold is entered in the filter dialog; it is not automatically linked to a worksheet cell. If you change a cell that happens to contain the same cutoff, the PivotTable filter does not necessarily update from that cell. Reopen the filter and enter the new threshold. Excel for Mac has PivotTable filtering too, though the ribbon placement and wording can differ slightly by release. In Excel for the web, available commands can vary; if a needed PivotTable filter control is missing, open the workbook in the desktop app.

Alternatives when a value filter is not enough

If the goal is to isolate raw transactions rather than summarize and filter groups, use a workflow that operates on the source records. For example, filter the source table’s Amount column for values below the cutoff, or create a separate filtered result for analysis. Do not assume that merely hiding source rows with an AutoFilter will make a PivotTable ignore those records; PivotTables can still summarize rows hidden in the source sheet. Refreshing alone does not turn hidden rows into excluded source records.

If you need a cutoff that updates from a cell, a formula-based result or a query-based process may be more suitable than a manually entered PivotTable filter. Microsoft 365 includes the FILTER worksheet function, which can return source rows meeting a condition, but it is a different approach from a PivotTable and depends on a correctly structured source range. For recurring reports, Power Query can also shape and filter imported data before it is loaded for analysis. Choose the method based on whether you need grouped summaries, row-level results, or a repeatable data-preparation process.

Frequently Asked Questions

Can I filter a PivotTable to show values below a specific amount?
Yes. Open the drop-down for a row or column field, choose Value Filters > Less Than, select the measure to test, and enter the cutoff.

Does Less Than filter individual source rows?
No. In a typical PivotTable it filters grouped items according to their summarized value. For individual transactions, filter or transform the source records instead.

Can I use a cell as the less-than cutoff?
The standard Value Filter dialog takes a value you enter; it is not a live cell reference. You must edit the filter when the cutoff changes, or use another approach such as a formula-based result.

Why can’t I see Value Filters?
Make sure you opened the menu for a field in Rows or Columns and that the PivotTable has a measure in Values. A report-filter menu or a label-filter submenu will not offer the same value comparison.

Will new data be included automatically?
Not always. Refresh the PivotTable, and verify that its source includes the new rows. A fixed source range may need to be expanded; an Excel table is often easier to maintain as records are added.

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.