Excel does not have one command that adds a single filter to every PivotTable column. To filter across the table, place the relevant fields in the PivotTable and filter each field using its dropdown, or add slicers for several fields as easy-to-use filter controls.
What “filter every column” means in a PivotTable
A PivotTable summarizes fields from a source range or Excel table. Its visible columns might represent several different things: source fields placed in the Rows or Columns areas, calculated summaries in the Values area, or a report-filter field displayed above the table. These are not all filtered in the same way.
For example, a sales PivotTable might show Region and Salesperson as row fields, Quarter as a column field, and Sum of Sales as a value. You can filter the Region, Salesperson, and Quarter items. To find sales above a threshold, you generally apply a value filter to a row or column field, rather than filtering individual cells in the Sum of Sales column like an ordinary worksheet range.
So, if by “all columns” you mean all the fields shown in the PivotTable, add each relevant field to the layout and filter the fields individually. If you mean a filter dropdown on every original column in the source data, use a regular table filter instead. A PivotTable is a summarized report, not a copy of the source table with filters attached to each cell column.
How PivotTable filters work
PivotTable fields can be placed in four areas: Filters, Columns, Rows, and Values. A field in the Filters area becomes a report filter above the PivotTable. A field in Rows or Columns groups the summary by its distinct items and can usually be filtered using a field dropdown. Fields in Values are summarized, for example as a sum or count, and are usually filtered indirectly with a value filter on a row or column field.
Filters usually narrow the records contributing to the displayed summary; they do not delete source records. If you select East in a Region filter, the PivotTable recalculates its summaries using the records that match the selection. Several fields can be filtered at once. For instance, choosing East for Region and Q2 for Quarter limits the report to records meeting both choices.
Within a field, selecting several items normally includes records matching any selected item. Across separate fields, the selections work together. This distinction is useful when you are trying to understand why a report shows fewer records than expected.
Add a report filter for a field
In Excel for Microsoft 365 on Windows, use the PivotTable Fields pane to add a report filter:
- Click a cell inside the PivotTable. If the field list is hidden, go to PivotTable Analyze > Field List.
- In the PivotTable Fields pane, find the field you want to filter by, such as Region or Year.
- Drag the field into the Filters area, or select its checkbox and move it to that area if Excel places it somewhere else.
- Use the dropdown that appears above the PivotTable to choose an item. To include more than one item, turn on Select Multiple Items if that option appears, then make your selections.
Repeat these steps for every field you want to expose as a report filter. Excel displays the filter fields above the PivotTable, typically one below another. This is convenient when the report should have a compact set of controls, but it does not put a dropdown beside every displayed row or column heading.
Filter fields in the Rows and Columns areas
Fields in the Rows and Columns areas can be filtered from their field buttons. Select the dropdown beside a row or column field, clear Select All if appropriate, select the items to keep, and choose OK. The exact button label depends on the layout: it may show a field name or a generic label such as Row Labels.
To make individual row fields easier to identify, click the PivotTable and choose Design > Report Layout > Show in Tabular Form. This displays row fields in separate columns rather than nesting them in a compact outline. It can make the report easier to read, but the layout change does not turn the PivotTable into a normal filtered table; use each field’s own filter control.
If you cannot see the field buttons, select the PivotTable and check PivotTable Analyze > Field Headers. Turning field headers on displays the buttons for filtering and sorting. The labels and ribbon arrangement can vary slightly between Excel updates, and the Mac ribbon may use a slightly different tab layout, but the same ideas apply.
Use slicers to filter several fields
Slicers are often the clearest answer when you want users to filter multiple fields without opening a series of dropdown menus. A slicer is a panel of buttons for one PivotTable field; selecting a button filters the report, and users can often select multiple items. You can add a separate slicer for each field you want people to control.
- Select a cell in the PivotTable.
- On Windows, choose PivotTable Analyze > Insert Slicer.
- Select the fields to control, such as Region, Product, and Salesperson, and choose OK.
- Move or resize the slicers as needed. Use the slicer’s clear-filter button to remove its selection.
A slicer does not automatically provide a control for every source column. You must choose the fields to include, and a field generally needs to be part of the PivotTable’s available source data. Slicers are especially helpful in dashboards because the current selection is visible. On Mac or in Excel for the web, ribbon availability and exact placement can differ by version; if the Insert Slicer command is unavailable, use the field dropdowns in the PivotTable or open the workbook in desktop Excel for Windows.
Choose the right kind of filter
The best filter depends on what you need to include or exclude. Common PivotTable filter choices include:
- Manual item selection: Keep selected items, such as two regions or a short list of products.
- Label filters: Include items whose text meets a condition, such as labels that begin with a chosen word.
- Date filters: Narrow date items to a period or date condition, when the source field contains dates Excel recognizes as dates.
- Value filters: Show row or column items whose summarized value meets a condition, such as sales greater than a specified amount or the top items by sales.
- Report filters: Put one or more field selectors above the PivotTable for a simple report interface.
For example, suppose a report lists products by region and totals their sales. A manual filter can show only East and West. A value filter can show products with a total above a threshold. The value filter acts on the summarized sales for each product; it is not the same as filtering the original transaction rows one at a time. If the distinction matters, check which field the filter menu is attached to and what summary is being evaluated.
PivotTable filters versus worksheet filters
A regular Excel table can have a filter dropdown on each column heading. To add these to a source table, click inside the table and use Data > Filter, or select a range and use the same command. On Windows, Ctrl+Shift+L toggles worksheet filter buttons for a selected range; on Mac, the shortcut is Command+Shift+F. A PivotTable is different: its dropdowns filter grouped field items, and it may not display every original source column.
Use a table filter when you need to inspect or hide individual source records while retaining the original row-by-row structure. Use a PivotTable when you want summaries by categories, such as total sales by region and month. You can use both in the same workbook, but filtering the source worksheet and filtering the PivotTable are separate operations. A PivotTable may also need refreshing after its source data changes; select it and choose PivotTable Analyze > Refresh.
Common problems and fixes
- A source field is missing: Open the field list and confirm the PivotTable source includes that column. If you added a new source column, update the source range or use an Excel table as the source, then refresh the PivotTable.
- There is no dropdown beside a heading: Check PivotTable Analyze > Field Headers. Also confirm the field is in Rows or Columns; a field placed only in Values does not behave like a row-label filter.
- One dropdown seems to control multiple row fields: In Compact Form, row fields can appear nested under a shared Row Labels heading. Use the field selector in the dropdown, or switch to Design > Report Layout > Show in Tabular Form to make the fields more distinct.
- A filter changes the report unexpectedly: Check whether multiple items are selected and whether other fields already have active filters. Clear a field’s filter from its dropdown, or use a slicer’s clear-filter button.
- New items do not appear: Refresh the PivotTable and verify that the source range includes the new records. A PivotTable cannot filter items it has not loaded from its source.
For a more dependable source, format the data as an Excel table with Ctrl+T on Windows (Command+T on Mac in many Excel versions) before creating the PivotTable. Tables expand as records are added, although you still need to refresh the PivotTable to update its results.
Frequently Asked Questions
Can I add one filter to every PivotTable column at once?
No. Excel filters PivotTable fields, not every visible column with one universal control. Add the relevant fields to the layout and filter them separately, or use slicers for multiple fields.
Can I put a filter dropdown on every source-data column?
Yes, if you want to filter the original records rather than the PivotTable summary. Use Data > Filter on the source range or table. Those worksheet filters are separate from PivotTable filters.
Can I filter a PivotTable by more than one field?
Yes. Add multiple fields to the Filters area, filter multiple row or column fields, or create slicers for several fields. Each active selection contributes to the displayed result.
Can I filter the Sum of Sales column directly?
Usually, use a Value Filter on the row or column field whose items you want to keep, and choose the summarized sales value as the condition. This filters grouped items by their totals rather than filtering individual worksheet cells.
Why does my PivotTable not show all source columns?
A PivotTable only displays fields placed in its Rows, Columns, Values, or Filters areas. Open PivotTable Analyze > Field List and add the fields you need; then refresh if the source structure or records have changed.
Related
- Excel Decimal Comma: Change Number Separators
- Excel Number Format to Hide Zeros Without Changing Values
- Excel Number Custom Format Codes: Examples and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Line Chart: Switch X and Y Axes the Right WayIn Excel, a line chart’s Switch Row/Column command changes which data series are plotted; it does not literally…
- Add a Data Table to an Excel Chart: Steps and TipsTo add a data table beneath a chart in Excel for Microsoft 365 on Windows, select the chart,…
- Bar Chart Construction Schedule in ExcelTo make a construction schedule bar chart in Excel, list each task, its start date, and its duration,…
- Excel Graphs: Ignore Zero Values Without Hiding DataYes. To keep zero values out of an Excel graph without changing your source data, make a helper…
- Excel Pivot Chart Pareto: Build and Read OneYes—but Excel cannot create a Pareto chart as a native PivotChart. The practical workaround is to build a…
- Excel Bar Graph Conditional Formatting: Color by RulesExcel does not apply worksheet conditional formatting rules directly to the bars in a chart. To make bar…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.