Pivot Table from Filtered List Visible Rows

Written by Debra Dalgleish from Contextures Blog

When you create a pivot table in Excel, it doesn’t matter if there are filters applied in the source data table. The pivot table includes all the data, whether it’s hidden or not. But, if you’d like to create a pivot table from filtered list visible rows only, you can try this technique.

Excel Versions

This technique uses new features in Excel, that are found in Excel for Office 365. Be sure that your version of Excel has the new functions, such as SORT and UNIQUE. I don’t want you to waste your time trying something that won’t work for you!

To check for the new functions:

  • Select a blank cell, then type: =SO
  • If the screen tip shows SORT and SORTBY, you have the new functions.


Named Excel Table

In this example, the source data that we want to use for the pivot table is a named Excel table — Sales_Data.

There are 100 records in the table, and it’s currently filtered to show 2 of the sales rep names (Smith and Riaz), and all of the categories except Cookies.

NOTE: If your pivot table source data has a huge number of records, the following technique could slow down your workbook


Make a Pivot Table

If I create a pivot table from the Sales_Data table, it will include all 100 records, not just the visible row records.

All the sales rep names are listed, and all the categories are included.


Pivot From Filtered List

To create a pivot table from filtered list visible rows only, I’ll do these steps:

  • Add a new column in the Sales_Data table.
  • In that column, use a formula to mark the visible rows
  • On another sheet, get the source data headings
  • Use a new function to pull visible rows from the Sales_Data table
  • Create a dynamic named range, based on pulled data and headings
  • Create a pivot table based on the dynamic named range

Mark the Visible Rows

The first step is to add a new column in the Sales_Data table, to mark the visible rows, using the SUBTOTAL function.

  • In cell J3, type the heading – Vis
  • In cell J4, start this SUBTOTAL formula, with 2 (Count) as the function number:
  • =SUBTOTAL(2,


  • Then, click on cell I4, which has the number of Orders, and type a closing bracket.
  • =SUBTOTAL(2,[@Orders])
  • Press Enter, to add the formula to all the cells in the Vis column (even the hidden rows)

How It Works

All the visible rows show 1 as the formula result in column J.

But, if you use formulas to do a COUNT and a SUM for column J, you’ll see that the numbers are different.

There are 100 numbers in column J, but only 24 of those numbers are a 1.


The SUBTOTAL function ignores values that are hidden by a filter (and manually hidden rows, in some cases), so for non-visible rows, the result in column J is zero. For example:

  • row 18 is visible, so it returns a 1
  • row 19, is hidden by the filter, so it returns a zero


Start a New Sheet

We’ll create a new worksheet, and build a new source for the pivot table there.

  • Insert a new worksheet
  • Name the sheet – DataFiltered

To build a pivot table, the data needs headings, so we’ll use the ones from the Sales_Data table.

  • In cell A1 on the new sheet, type an equal sign
  • Go to the FoodSales sheet, and click on cell A3, which has the first heading cell in the Sales_Data table.
  • Press Enter, to complete the formula


Next, follow these steps to get the rest of the headings

  • Select cell A1, which has the link to the first heading
  • Point to the fill handle, at the bottom right corner of cell A1
  • Drag across to column J, to get the rest of the Sales_Data table headings
  • (optional) Format the heading cells in bold font

Get the Filtered Data

Next, we’ll use a new Excel function  — FILTER — to pull the visible rows from the Sales_Data table

  • Select cell A2, and start the formula:


The first argument is the array – what we want to filter.

Type the name of the table that we want to filter – Sales_Data, then type a comma


The next argument is include – our rule for which records to return in the results. We want the rows where there is a 1 in the Vis column.

  • Go to the FoodSales sheet, and click at the top of the Vis column heading cell
  • The table name and column name are added to the formula



  • Type our rule for that column: =1


  • Type a closing bracket, then press Enter to complete the formula


Dynamic Array of Filtered Data

Even though you only entered the formula in cell A2, the formula results are in a dynamic array, that spills down and across, in as many cells as needed.

There is a thin blue border around the dynamic array


Cells in the Dynamic Array

If you click in any cell in the dynamic array, other than cell A2,

  • you can see the formula in light grey font in the formula bar
  • you can’t make any changes to the formula

If you select cell A2, where the formula was entered, you can edit the formula, as usual

Create a Dynamic Named Range

Now that we have headings and filtered data, we’ll create a dynamic named
range to use as the pivot table’s source data.

To refer to a dynamic array in a formula, use the array’s starting cell, followed by the spill operator – #

In this example, here’s the dynamic array reference:


We’ll use that reference in an OFFSET formula, to create the dynamic range:

  • On the Excel Ribbon, click the Formulas tab, then click the Define Name command
  • For the Name, type: PivotUse
  • Leave the Scope as Workbook
  • In the Refers to box, enter this OFFSET formula:

=OFFSET(DataFiltered!$A$2#,-1,0, ROWS(DataFiltered!$A$2#)+1, COLUMNS(DataFiltered!$A$2#))

  • Click OK, to complete the name


How the OFFSET Formula Works

The OFFSET function returns a range that:

  • Starts at the dynamic array
  • Goes up one row (-1)
  • Moves over 0 columns
  • Includes the number of rows in the dynamic array, plus 1 for the heading row
  • Includes the number of columns in the dynamic array

If the filters on the Sales_Data table are changed, the number of rows in the dynamic array will change.

Our dynamic named range, PivotUse, will adjust to those changes automatically

Create the Pivot Table

The final step is to create a pivot table, based on the dynamic named range.

  • Insert a new sheet, and name it PivotVis
  • Select any cell on the new sheet
  • On the Excel Ribbon, click the Insert tab
  • Click the Pivot Table command
  • In the Create PivotTable dialog box, click in the Table/Range box, and press the F3 key on your keyboard
  • In the Paste Name list, click on PivotUse, and click OK
  • For the location, choose the PivotVis sheet
  • Click OK to create the pivot table.


Add Fields to the Pivot Table

Next, use the PivotTable Field List to add the fields that you want to show in the pivot table.

If you include the Rep and Category fields, you’ll see that they only include the items from the visible rows in the Sales_Data table.


Change the Sales_Data Filters

If you change the filters in the Sales_Data table, be sure to refresh the pivot table after you’ve finished making the changes.

For example, filter the data so it only shows sales in the East region.

The dynamic array updates automatically, and now there are only 14 rows on that sheet.


However, pivot tables don’t refresh automatically, so you can do that step manually.

Right-click on the pivot table, and click Refresh.


After the refresh, only the East region records are showing. Riaz didn’t make any sales in that region, so Smith is the only rep in the filtered data, and in the refreshed pivot table.


Get the Sample File

To see how the pivot table from filtered list visible rows technique works, go to the Pivot Table Source Data page on my Contextures site.

In the Download section, get the Filtered Source Data sample file. The zipped file is in xlsx format, and does not contain any macros.


More Pivot Table Info

How to Set Up an Excel Pivot Table

Pivot Table Dynamic Data Source

Create a Pivot Table in Excel

Macro to Filter Pivot Table Data


Pivot Table from Filtered List Visible Rows


Pivot Table from Filtered List Visible Rows


Pivot Table from Filtered List Visible Rows is a post from Contextures Blog and is not allowed to be copied to other sites

Original source: https://contexturesblog.com/archives/2020/03/05/pivot-table-from-filtered-list-visible-rows/?utm_source=rss&utm_medium=rss&utm_campaign=pivot-table-from-filtered-list-visible-rows

Leave a Reply

Close Menu