How to Exclude Manually Hidden Rows from a Pivot Table in Google Sheets

Published on

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:

  1. Using the Filter command: Click Data > Create a filter.
  2. 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.

Pivot Table

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.

Sample data and pivot table for testing filtering in Google Sheets

To create the Pivot Table, select columns A to C and click Insert > Pivot Table.

The following screenshot shows the Pivot Table settings.

Google Sheets pivot table filter to remove blank rows

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:

  1. Hover your cursor over the Pivot Table report.
  2. Click on the pencil icon labeled “Edit.”
  3. Within the editor, replace the range A:C with A:D.
  4. 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:

  1. Click the “Add” button next to “Filter” within the Pivot Table editor.
  2. Select “Hidden Flag.”
  3. Click the drop-down, uncheck “0,” and then click “OK.”
Google Sheets Hidden Flag Filtering to filter out manually hidden rows

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.