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
