Yes, you can create a pivot table from filtered data in Excel, but the pivot table does not automatically respect the filter unless you explicitly tell it to. By default, a pivot table built from a filtered range includes all rows in the underlying data source, ignoring the filter. To make the pivot table use only the visible (filtered) rows, you must either copy the visible data to a new range or use a dynamic approach with a helper column.
Why Filters Don’t Automatically Carry Over
When you apply a filter to a worksheet range (via Data > Filter) and then insert a pivot table, Excel treats the entire contiguous range as the source data, not just the visible rows. This is because the pivot table’s cache stores a reference to the whole range, and the filter is a view-level setting, not a data-level transformation. The same applies if you filter a Table (created with Ctrl+T) – the pivot table will still use all rows in the table.
This behavior surprises many users because they assume the pivot table reflects what they see. But Excel’s design is intentional: filters are meant for temporary viewing, while pivot tables are meant to summarize the full dataset. If you want the pivot table to respect the filter, you have to change the source data itself.
How to Create a Pivot Table from Filtered Data (Step-by-Step)
The most reliable method is to copy only the visible rows to a new location, then build the pivot table from that copy. Here are the exact steps for Excel for Microsoft 365 on Windows:
- Select any cell inside your filtered data range.
- Press Ctrl+G (Go To), then click Special and choose Visible cells only. Click OK. (On Mac: press Cmd+Shift+Z or use Edit > Go To > Special.)
- Press Ctrl+C to copy the selected visible cells.
- Click on a new worksheet, select cell A1, and press Ctrl+V to paste. This creates a static copy of only the filtered rows.
- Now select any cell in that pasted range and go to Insert > PivotTable.
- In the dialog, verify the range is correct (it should reference the new pasted range) and choose where to place the pivot table.
This method works every time, but the copy is static – if you change the filter later, the copy won’t update. For a dynamic solution, see the next section.
Using a Helper Column for Dynamic Filtering
If you want the pivot table to update automatically when you change the filter, you can add a helper column to your source data that flags visible rows, then filter the pivot table based on that flag. Here’s how:
- Add a new column to your data (e.g., column Z) and name it “Visible”.
- In the first data row (e.g., Z2), enter the formula:
=SUBTOTAL(103,A2)– this returns 1 if the row is visible and 0 if hidden. Drag the formula down. - Now when you filter your data, the SUBTOTAL formula recalculates to show 1 for visible rows and 0 for hidden ones.
- Create a pivot table from the entire data range (including the helper column).
- Add the “Visible” field to the Filters area of the pivot table and set it to 1.
Now, whenever you change the filter on the source data, the pivot table will only show rows where the helper column equals 1. Note that SUBTOTAL with function number 103 (COUNTA) ignores hidden rows, but it does not ignore rows hidden by a filter – it only ignores rows manually hidden via Hide Rows. To make it work with filters, you must use =SUBTOTAL(103,A2) and ensure your data is filtered, not manually hidden. This formula works in Excel 2010 and later.
Alternative: Using Excel Tables and Slicers
If your data is formatted as an Excel Table (Insert > Table), you can use slicers to filter both the table and the pivot table simultaneously, but only if the pivot table is based on the same table. However, a slicer on the table does not automatically filter the pivot table – you need to connect the slicer to the pivot table as well. To do that:
- Insert a slicer for a field in your table (Table Design > Insert Slicer).
- Right-click the slicer, choose Report Connections (or PivotTable Connections in older versions).
- Check the pivot table(s) you want to filter.
This way, when you click a slicer button, both the table and the pivot table filter together. This is a clean, dynamic method, but it requires the pivot table to be based on the same table range. If your pivot table is based on a separate range, this won’t work.
Common Problems and Misconceptions
One common mistake is using Ctrl+A to select the data before copying, because that selects the entire worksheet, not just the filtered rows. Always use Go To Special > Visible cells only.
Another misconception is that the pivot table will update if you refresh it after changing the filter. Refreshing only updates the pivot table with the current source data – it does not apply the filter. The filter is not part of the pivot table’s cache.
Also, be aware that if you have hidden rows (not filtered), the pivot table will include them by default. The only way to exclude hidden rows is to use the helper column method or copy only visible cells.
If you use the helper column with SUBTOTAL, note that it will not update automatically if you manually hide rows (right-click > Hide) – it only responds to filter changes. For manual hiding, you’d need a VBA macro, but that’s beyond the scope of this article.
Comparing Methods: Pros and Cons
- Copy visible cells: Simple, no formulas, but static – you must redo it each time the filter changes.
- Helper column with SUBTOTAL: Dynamic, updates with filter changes, but adds a column to your data and requires you to remember to set the pivot filter to 1.
- Slicer connections: Interactive and visual, but only works if the pivot table is based on the same table, and it doesn’t respect other filters (like auto-filter) unless you use slicers exclusively.
For most users, the copy method is best for a one-time report, while the helper column is best for a dashboard that needs to update. Slicers are ideal for interactive reports where you want the user to control the filter.
Frequently Asked Questions
Why does my pivot table show more rows than my filtered data?
Because the pivot table is based on the entire data range, not just the visible rows. The filter is a view-level setting that doesn’t change the underlying data. To fix it, use one of the methods above.
Can I use a pivot table directly on a filtered range without copying?
No, not directly. Excel doesn’t have a built-in option to “use only visible rows” for a pivot table. You must either copy the visible cells or use a helper column with SUBTOTAL.
Does the helper column method work with Excel for the web?
Yes, SUBTOTAL is available in Excel for the web, and the steps are the same. However, the Go To Special feature is not available in Excel for the web, so you’d need to use a different method to copy visible cells (like using the FILTER function). For a dynamic copy, you could use =FILTER(A2:E100, SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0, 1)) = 1) but that’s complex. Simpler: use the helper column method.
What if I have a PivotTable already created and then I filter the source? Can I make it update?
No, refreshing the pivot table will not apply the filter. You need to change the source data or add a filter field as described above. If you want to filter the pivot table itself, you can use the built-in filter arrows on the pivot table fields, which is different from filtering the source data.
Is there a way to make the pivot table ignore hidden rows without a helper column?
Not without VBA. The only built-in way is to copy visible cells or use a helper column. Some advanced users use Power Query to filter the data before loading it into the pivot table, but that’s a different approach.
Final Thoughts
Creating a pivot table from filtered data is possible, but you must understand that the filter is not automatically part of the pivot table’s source. The copy method is straightforward for one-off reports, while the helper column method provides a dynamic solution. For interactive dashboards, consider using slicers connected to both the table and the pivot table. Always test your method with a small dataset to ensure the results match your expectations.
Related
- Excel Hourly Schedule Template: How to Make One
- Excel: How to Add Lines in a Cell (3 Easy Ways)
- Excel Hotel Group Jobs: Hiring, Pay, and How to Apply
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Horizontal Waterfall Chart: How to Build OneExcel does not provide a horizontal orientation for its built-in Waterfall chart: the native chart displays changes as…
- Gantt Chart Excel Template for Research ProjectsA useful Gantt chart Excel template for research projects has one row per task, columns for start date…
- Excel Stacked Bar Chart: Sort Categories by Largest ShareTo sort an Excel stacked bar chart by each bar’s largest share, add a helper column that calculates…
- Excel Pivot Table Slicer Search: Options and WorkaroundsNo: a standard Excel PivotTable slicer does not have a built-in search field you can add or turn…
- How to Refresh a Pivot Table in ExcelTo refresh a pivot table in Excel, right-click anywhere inside the pivot table and choose Refresh from the…
- KPI Dashboard Excel Templates: Free Options and SetupYes—free KPI dashboard Excel templates are available from Microsoft and other providers, and you can also build a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.