How to Refresh a Pivot Table Using Only Visible Filtered Rows
Question details
The user wants to configure a Pivot Table so that it only includes data from rows that remain visible after applying filters to the source data table.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Filtering a source data table and needing the dependent Pivot Table to dynamically update and reflect only the visible filtered data.
- Observed behavior
- By default, Pivot Tables include all rows from the source data range, including those hidden by table filters. The goal is to exclude the filtered-out rows from the Pivot Table's calculations.
Ensure your source data is formatted as an official Table and identify a column that contains data in every single row to use as your reference for the SUBTOTAL formula.
Use a SUBTOTAL Helper Column to Filter Visible Rows
Create a dynamic helper column in your source data that identifies visible rows, which can then be used as a filter field in your Pivot Table.
The SUBTOTAL function is unique because it automatically ignores rows that are hidden by a filter. By using it in a helper column, we can force the column to display a '1' for visible rows and a '0' for filtered rows.
Add a new column to the right of your source data table and name it 'Visible'.
In the first cell of the 'Visible' column, enter the formula =SUBTOTAL(3,[@YourColumnName]), replacing 'YourColumnName' with the name of a column that never contains blank cells. Press Enter to apply the formula.
Click inside your existing Pivot Table, go to the PivotTable Analyze tab, and click 'Refresh' so the new 'Visible' column appears in the PivotTable Fields pane.
Drag the 'Visible' field into the 'Filters' area of the Pivot Table. Click the filter dropdown above your Pivot Table and select '1'.
Apply Filters Directly Inside the Pivot Table
Instead of filtering the source data table, use the built-in filtering options within the Pivot Table itself to hide unwanted data.
Easily Manage Pivot Tables with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including advanced Pivot Tables and full support for dynamic formulas like SUBTOTAL. It perfectly handles complex data filtering needs while remaining intuitive and fast.
- 1. Open your data: Launch WPS Spreadsheet and open your dataset.
- 2. Insert the helper column: Add a 'Visible' column and type =SUBTOTAL(3, A2) (referencing a non-empty cell in the row).
- 3. Create the Pivot Table: Select your data, go to the 'Insert' tab, and click 'PivotTable'.
- 4. Filter the visible rows: Drag the 'Visible' field to the Filters area in the right pane and select '1' to show only visible data.
Frequently Asked Questions
Why doesn't my Pivot Table update automatically when I filter the source data?
Pivot Tables are designed to hold a cache of your data to improve performance and prevent lag on large datasets. Because of this cache, you must manually refresh the Pivot Table (right-click inside it and select Refresh, or go to Data > Refresh All) after filtering or modifying the source data.
What does the '3' stand for in the =SUBTOTAL(3, ...) formula?
The '3' specifies the COUNTA function within the SUBTOTAL formula. It counts non-empty cells. The key benefit of using SUBTOTAL instead of standard COUNTA is that SUBTOTAL automatically ignores rows that are hidden by a filter, allowing it to return 1 for visible rows and 0 for hidden rows.
Can I automate the Pivot Table refresh process?
Yes. If you are comfortable using VBA, you can create a simple macro triggered by a worksheet event (such as Worksheet_Calculate or Worksheet_Activate) that automatically refreshes the Pivot Table cache whenever the source data is filtered or modified.
