Excel: How to Create Pivot Table Based on Filtered Data


By default, Excel is unable to create a pivot table using filtered data.

Instead, Excel always uses the original data to create a pivot table rather than the filtered data.

One way to get around this issue is to simply copy and paste the filtered data to a new cell range and then create a pivot table using the new cell range.

The following example shows exactly how to do so.

Example: Create Pivot Table Based on Filtered Data

Suppose we have the following data in Excel that shows the points scored by basketball players on various teams:

Now suppose we highlight the cell range A1:B12, then click the Data tab, then click the Filter icon.

Now suppose we click the dropdown arrow next to Team, check the boxes next to Mavs and Warriors, and then click OK:

The data will be filtered to only show rows where the Team is equal to Mavs or Warriors:

If we attempt to create a pivot table to summarize the sum of points scored by these two teams, the pivot table will actually use all of the original data:

To get around this issue, we need to highlight and copy the cells in the range A1:B12, then paste them in a different cell range.

For this example, we’ll paste them into a new sheet entirely:

Now if we highlight the cell range A1:B8 in this sheet and insert a pivot table, it will only contain the filtered data:

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Apply Multiple Filters to Pivot Table at Once
Excel: How to Filter Pivot Table Using OR Condition
Excel: How to Filter Pivot Table by Date Range

One Reply to “Excel: How to Create Pivot Table Based on Filtered Data”

  1. Here is the 3-step solution I worked out to create a virtual table from filtered data.

    Step 1: On a new sheet (named “FilteredTable” for this example), in cell A1 use:
    =tblMyTable[#Headers]

    This sets up a lateral list of all headers from the source table, starting in cell A1.

    Step 2: Directly under this virtual row of headers (A2), use:
    =FILTER(tblMyTable,tblMyTable[FieldName]=”Criteria”)

    This creates the filtered table, though without headers – but it isn’t quite useable yet.

    Step 3: Create a dynamic range name (“FilteredData”) to use for the pivot:
    =OFFSET(FilteredTable!$A$1,0,0,COUNTA(FilteredTable!$A:$A),COUNTA(tblMyTable[#Headers]))

    The range “FilteredData” starts at cell A1, extends down for as many rows in column A that have contents, and across for as many columns as there are Headers in the original table. Set the pivot to use “=FilteredData” as its data source. Voila! A virtual ‘table’ to supply filtered data from the original to the pivot.

Leave a Reply

Your email address will not be published. Required fields are marked *