Yes, you can filter a PivotTable based on a calculated field’s results, but usually you do it with a Value Filter on a row or column field—not by putting the calculated field itself in the Filters area. If you need to filter individual source records by a calculated condition, add a helper column to the source data instead.
What a calculated field filter means
A classic Excel PivotTable calculated field is a formula built from fields in the source data. It appears as a value that the PivotTable calculates and summarizes, such as net sales defined as sales minus cost. It is useful when the result should update as the PivotTable groups or filters the source records.
A filter, however, can mean two different things. A report filter or slicer selects categories in the source data, such as Region or Product. A Value Filter keeps or hides row or column items according to a summarized value, such as showing only regions whose total net sales exceed a threshold. The second kind is what you usually want when asking how to filter by a calculated field.
In a standard PivotTable, a calculated field is a value calculation, not a new category field. It generally belongs in the Values area; it cannot be used as a normal row, column, or report-filter category in the same way as a source-data field. A Value Filter lets you use its results to decide which row or column items remain visible.
Create a calculated field in Excel
These steps apply to Excel for Microsoft 365 on Windows and to a conventional PivotTable, not a PivotTable built from the Data Model or an OLAP source. Select a cell in the PivotTable, then use the ribbon path PivotTable Analyze > Fields, Items & Sets > Calculated Field. In the dialog, enter a name for the calculation and build its formula using the source field names.
For example, assume the source table has fields named Sales and Cost. In the calculated-field dialog, the formula =Sales-Cost defines Net Sales. Select Add or OK to create it, then make sure Net Sales is in the Values area. The PivotTable will summarize that calculation for each region, product, or other grouping you have placed in the layout.
Use the Insert Field control in the dialog to add field names where possible. This avoids mistyping a name or using a worksheet cell reference where a source field is expected. The formula is stored with the PivotTable; it is not a formula entered into a worksheet cell beside the report.
Filter the PivotTable using the calculated result
To show, for example, only regions with more than 10,000 in summarized Net Sales:
- Put Region in the Rows area and Net Sales in the Values area. Confirm that the value field is summarized as Sum if that is the intended measure.
- Select the drop-down arrow beside the Row Labels or Region heading in the PivotTable.
- Choose Value Filters, then select a condition such as Greater Than.
- In the dialog, choose the calculated value field, such as Sum of Net Sales, enter 10000, and confirm.
Excel hides region items that do not meet the condition. The underlying source rows are not deleted, and the filter does not rewrite the calculated field. To change or remove the condition, use the field’s drop-down and adjust or clear the filter. To filter products instead, place Product in Rows or Columns and apply the Value Filter to that field’s items.
The exact heading can vary with the PivotTable layout. If the report displays a separate Region heading rather than Row Labels, use the arrow beside Region. On Excel for Mac, select the PivotTable and use the PivotTable ribbon tabs and the field drop-down; ribbon organization can differ by build. Excel for the web can display and interact with many PivotTables, but advanced PivotTable editing options may be limited compared with desktop Excel.
Example: filtering regions by net sales
Suppose three source records have Sales and Cost of 100 and 60 for North, 200 and 150 for North, and 120 and 80 for South. The calculated field =Sales-Cost produces 40, 50, and 40 for those individual records. With Region in Rows and Sum of Net Sales in Values, the PivotTable shows North at 90 and South at 40.
A Value Filter of Greater Than 50 on Sum of Net Sales keeps North and hides South. This is a filter on the grouped total. It does not mean that every North transaction is greater than 50, nor does it remove any transaction from the source table. If you instead need to display only individual transactions whose net sales exceed 50, a PivotTable value filter is not the right tool; create a source-data helper field or filter the source table before building the report.
Value Filters can use conditions such as greater than, less than, between, or a Top 10 rule. The condition applies to items in the chosen row or column field, evaluated using the selected value field. When more than one value field is present, check that the dialog is comparing the intended calculated field rather than another measure such as Sum of Sales.
Important limitation: calculated fields and percentages
Calculated fields can be misleading when a formula is a ratio. In a conventional PivotTable, Excel evaluates a calculated field for the source records and then summarizes those results. If you define a calculated field as =Profit/Sales and it is summed, Excel adds the individual record-level ratios; that is not generally the same as total profit divided by total sales.
For example, a record with profit 40 and sales 100 has a 40% ratio, while a record with profit 50 and sales 200 has a 25% ratio. Summing those record ratios gives 65 percentage points, but the ratio of total profit to total sales is 90 divided by 300, or 30%. A Value Filter on the summed calculated field would filter using the 65% figure, not the overall 30% margin. That can produce a plausible-looking but incorrect report.
For a ratio that must be calculated from grouped totals, use a measure in a Data Model PivotTable, for example a DAX measure that divides the sum of profit by the sum of sales. A measure is designed to respond to the PivotTable’s filter context. If you need a simple record-by-record category, such as a flag for whether one transaction exceeds a limit, a source helper column is usually clearer.
Choose the right filtering method
Decide what the condition is supposed to evaluate before choosing a filter. The distinction is whether you are filtering source records, categories, or summarized results:
- Filter individual source rows: Add a helper column to the source table, then use that field in the PivotTable’s Filters, Rows, Columns, or a slicer. This is appropriate for a record-level flag such as Eligible or Not Eligible.
- Keep categories above or below a grouped total: Use a Value Filter on the row or column field, selecting the calculated field as the value to compare. This is appropriate for regions whose total net sales exceed a target.
- Filter by a calculated category: Create a source field that assigns each record to a category, such as a date band or transaction type, then filter that field. A PivotTable calculated field is not a substitute for a category column.
- Calculate a ratio from totals: Use a Data Model measure when the report needs a mathematically correct ratio that changes with grouping and filters.
These methods answer different questions. For example, “show regions with total net sales above 10,000” is a grouped-result question, while “show orders with a line value above 10,000” is a source-record question. Applying the first kind of filter does not automatically answer the second.
Common problems and fixes
The calculated field is missing from the filter list. A calculated field is not a normal category field for the Filters area. Put it in Values, then apply a Value Filter to the relevant row or column field and select that calculated value in the condition dialog.
Value Filters is unavailable or disabled. Check that the PivotTable has a row or column field with items to filter and at least one value field. Some external, OLAP, or Data Model PivotTables have different capabilities and do not support classic calculated fields. For those sources, create a supported measure or use a field in the source data.
The wrong items disappear. Reopen the Value Filter dialog and verify both the operator and the measure selected. Also check whether the measure is being summed, averaged, or otherwise summarized as intended. A field labeled Sum of Net Sales is not interchangeable with Count of Net Sales.
New source rows do not appear in the calculation. Refresh the PivotTable after changing the source data. If the source range did not expand to include the new rows, update the PivotTable’s data source or use an Excel Table as the source so its range can expand with added records. Then refresh and confirm the calculated field still references the correct source fields.
A percentage filter seems unreasonable. Check how the calculated field is aggregated. A sum of per-record percentages is often not a valid overall percentage. Rework the calculation as a ratio of grouped totals using a Data Model measure when that is the required meaning.
Frequently Asked Questions
Can I put a PivotTable calculated field in the Filters area?
A classic calculated field is a value calculation, not a regular source category, so it is not normally used as a report-filter field. Put it in Values and use a Value Filter on a row or column field, or add a helper column to the source data if you need a report filter.
Can I filter a PivotTable by a calculated field greater than a number?
Yes, if you mean filtering grouped items by their summarized result. Open the row or column field’s drop-down, choose Value Filters, select a comparison such as Greater Than, choose the calculated value field, and enter the threshold.
Can a slicer filter a calculated field?
A slicer filters a field’s items; a calculated field is generally a measure in the Values area rather than a category of items. Use a slicer on a suitable source-data field, or use a Value Filter to hide row or column items according to the calculated result.
Why does my calculated percentage total look wrong?
A classic calculated field such as Profit divided by Sales may calculate a ratio for each source record and then sum those ratios. That differs from total Profit divided by total Sales. Use a measure based on grouped totals for an overall percentage.
Why can’t I create a calculated field in my PivotTable?
The PivotTable may use the Data Model, an OLAP connection, or another source type that does not support classic calculated fields. Use a measure supported by that source, or add the required calculation to the source data where appropriate.
Related
- Excel Next Tab Shortcut: Move to the Next Worksheet
- Excel Keyboard Shortcut to Highlight a Cell with Color
- Excel Keyboard Shortcuts Mouse Pad: Guide to Choosing
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Stacked Bar Chart Data: The Right LayoutFor an Excel stacked bar chart, put each category in its own row, each data series in a…
- Treemap Chart in Excel: How to Create OneTo create a basic treemap chart in Excel, arrange your categories in columns from broadest to most specific,…
- Excel Pivot Table Unable to Group Dates? Fix the SourceIf an Excel PivotTable cannot group dates, first check the source date column for blanks, errors, or values…
- How to Create a Waterfall Chart in ExcelTo create a waterfall chart in Excel, arrange your categories and positive or negative changes in two columns,…
- Exploded Doughnut Chart in Excel: How to Make OneTo make an exploded doughnut chart in Excel for Microsoft 365 on Windows, select your category-and-value data, choose…
- Best Free Excel Gantt Chart Templates: 5 PicksFor most people, Vertex42’s Gantt Chart Template is the best free Excel Gantt chart template: it gives you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.