Pivot Table Issues

How to Exclude Hidden Excel Table Rows from PivotTables

Maira Mehtab Sep 27, 2026 869 views

Question details

The user wants to know how to prevent hidden or filtered rows in a source Excel table from appearing in a connected PivotTable.

Product: Excel
Device & OS: not provided
Scenario: Filtering data in a source spreadsheet to summarize only specific visible information using a PivotTable.
Observed behavior: When rows in the source table are filtered or hidden, the PivotTable still includes the data from those hidden rows because it pulls from the entire source range by design.

Before you start

Ensure your source data is formatted as an official Excel Table (Insert > Table) and identify the specific categories or values you want to filter out before modifying your PivotTable connections.

Solution 1Recommended

Use Slicers to Filter Data Directly on the PivotTable

Slicers provide a visual, interactive way to filter data directly on the PivotTable, eliminating the need to filter the original source table.

By design, Excel PivotTables include all data within the source range, regardless of whether rows are hidden or filtered in the source sheet. To effectively exclude specific data, you must apply the filter to the PivotTable itself rather than the source range.

Select the PivotTable

Click any cell inside your existing PivotTable to bring up the PivotTable Tools on the ribbon.

Insert a Slicer

Navigate to the 'PivotTable Analyze' tab and click 'Insert Slicer'.

Choose Fields to Filter

Check the boxes for the specific fields (columns) you want to use for filtering out data, then click 'OK'.

Apply Filters

Use the generated Slicer buttons to select the data you want to display. Data that is not selected in the Slicer will be excluded from the PivotTable.

Connect Multiple PivotTables

Right-click the Slicer, select 'Report Connections', and check any other related PivotTables to sync this exclusion filter across multiple reports.

Quick Filtering: Slicers are the fastest way to manage visible data across multiple PivotTables simultaneously without risking permanent data loss.

Solution 2

Use Power Query to Create a Filtered Dataset

Ideal for permanent data exclusions, Power Query lets you filter out rows before they are loaded into the PivotTable cache.

Advanced Data Analysis

Effortlessly Manage PivotTables with WPS Spreadsheet

WPS Spreadsheet offers powerful data analysis tools, including fully-featured PivotTables and Slicers, allowing you to easily filter and exclude unwanted data with a highly intuitive interface.

1. Open Your Data: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
2. Create a PivotTable: Highlight your source data, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Add a Slicer: With your PivotTable selected, go to the 'Analyze' tab and click 'Insert Slicer'.
4. Filter Unwanted Rows: Choose the fields you wish to filter and easily toggle data visibility directly from the Slicer dashboard to exclude unwanted rows.

Seamlessly compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.Insert and manage Slicers to visually filter data without modifying source tables.Free, lightweight alternative featuring all essential spreadsheet functionalities.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable still show data I deleted from the source table?

PivotTables do not update automatically when source data changes. If you delete data from the source table, you must right-click the PivotTable and select 'Refresh' to update the data cache and remove the deleted entries.

Can I apply standard filters directly to the PivotTable fields?

Yes. You can click the drop-down arrows on the Row Labels or Column Labels directly within the PivotTable to uncheck items you want to exclude. This acts similarly to filtering the source data but only applies to the PivotTable view.

Do hidden columns get excluded from PivotTables automatically?

No, hiding columns in the source data does not exclude them from the PivotTable fields list. To exclude them, simply avoid dragging those specific fields into your PivotTable's Rows, Columns, or Values areas.

Will filtering via Power Query reduce my overall file size?

Yes. Loading a filtered subset of data directly into a PivotTable via Power Query (instead of loading the entire dataset into the worksheet) can significantly reduce the overall workbook file size and improve calculation performance.

Excel & Data Problems