Excluding manually hidden rows from a Pivot Table in Google Sheets can be challenging, but not impossible. This process requires a dynamic array formula that adjusts its size depending on your source data.
Essentially, you need to add an additional column (a helper column) to your source data, with the top cell containing the mentioned array formula.
The purpose of the helper column is to automatically flag the rows that are manually hidden.
The formula will help you distinguish between hidden and visible rows.
Once you’ve completed this step (which we will see in detail below), you can utilize the “Filters” within the Pivot Table editor to exclude the flagged rows.
Google Sheets lacks a built-in option for excluding manually hidden rows within the Pivot Table settings.
Benefits of Excluding Manually Hidden Rows From a Pivot Table
In Google Sheets, you can systematically filter your source data in two ways:
- Using the Filter command: Click Data > Create a filter.
- Using a slicer: Click Data > Add a slicer.
These methods allow you to select specific items, such as filtering data by date, region, or product. This will hide the relevant rows in your source data.
What about the connected Pivot Table report?
The first option, Create a filter, will not have any impact on the Pivot Table report. You need to select the “Filters” within the Pivot Table editor to filter the data in the Pivot Table.
However, you can connect the Slicer to the Pivot Table so that the Pivot Table is filtered automatically when you select items in the Slicer.
Related: How to Use Slicer in Google Sheets to Filter Charts and Tables.
Example
Imagine you have the item “Mango” listed in three rows in your source data, with values of 50, 75, and 100, resulting in a total of 225.
If you filter out this item using Slicer or “Filters” within the Pivot Table editor (not Data > Create a filter), it will no longer appear in the report.
Manually hiding rows is different from systematically filtering data. In this case, you hide rows randomly for specific purposes, such as protecting sensitive data or excluding outdated information. You may want to retain this data rather than delete it, but you don’t want it to appear in your reports.
For instance, if you manually hide the row containing the item “Mango,” which amounts to 100, the report will show the aggregate value of 125.
When might you want to exclude manually hidden rows from a Pivot Table?
Consider a scenario where you need to generate a report while omitting sensitive data.
To achieve this, hide the rows to exclude them from the report, then proceed to print or download them as a PDF file. Share those copies.
How to Exclude Manually Hidden Rows From a Pivot Table Using a Helper Column
To see a live example, please click on the link below and make a copy of my sample sheet.
Sample Data and Pivot Table
The following sample data is in columns A to C, and a Pivot Table created from that starts in cell E1.
To create the Pivot Table, select columns A to C and click Insert > Pivot Table.
The following screenshot shows the Pivot Table settings.
Related: How to Create a Pivot Table Report to Summarize Data in Google Sheets.
Helper Column
When selecting a helper column, consider the following criteria:
- It should be blank.
- It should be the column immediately following the source data.
In my example, column D fulfills these criteria.
In cell D1, insert the following dynamic helper column formula:
=VSTACK("Hidden Flag",BYROW(B2:B, LAMBDA(row,SUBTOTAL(103,row))))This formula will flag the manually hidden rows by inserting 0 (zero) in hidden cells and 1 in visible cells wherever a value is present in column B.
What is the criterion for choosing the column in the formula above?
You should select the column that corresponds to the “Row” field in the Pivot Table settings. In my Pivot Table, this is the “Item” field, located in B2:B.
Note: For more information about this formula, please refer to my guide on the BYROW function.
How to Add the Helper Column to Your Pivot Table
To include the helper column labeled ‘Hidden Flag’ (yes, the formula mentioned above will insert the label ‘Hidden Flag’ in cell D1) within the Pivot Table editor, follow these steps:
- Hover your cursor over the Pivot Table report.
- Click on the pencil icon labeled “Edit.”
- Within the editor, replace the range A:C with A:D.
- Don’t hit the enter key on the keyboard. Click outside the field and click OK.
How to Filter the Pivot Table to Exclude Manually Hidden Rows
Here is the final step to exclude manually hidden rows from the Pivot Table in Google Sheets:
- Click the “Add” button next to “Filter” within the Pivot Table editor.
- Select “Hidden Flag.”
- Click the drop-down, uncheck “0,” and then click “OK.”
You can now close the Pivot Table editor by clicking on the “x” button in the top right corner.
Right-click on row #6 and select “Hide Row.” You will notice that the total for the item “Mango” decreases from 225 to 125.
Conclusion
The helper column approach is the only method for excluding manually hidden rows from a Pivot Table in Google Sheets.
When using this workaround, please take note of one important factor: If you have applied filters to the source data using Slicer, you must remove them. The above method will also exclude rows affected by those filters.
