Table of Contents
A pivot table in Excel is a powerful tool that allows users to analyze and summarize large amounts of data. To create a pivot table using filtered data, follow these steps:
1. Select the data that you want to include in the pivot table, including any headers or labels.
2. Go to the “Insert” tab on the Excel ribbon and click on “PivotTable.”
3. In the pop-up window, select “Use an external data source” and click “Choose Connection.”
4. Choose the data source that contains your filtered data and click “Open.”
5. In the next window, choose the table or range of data that you want to use for the pivot table and click “OK.”
6. Drag and drop the fields you want to include in the pivot table into the “Rows,” “Columns,” and “Values” sections.
7. Right-click on any value in the pivot table and select “Value Field Settings” to change the calculation method or format of the data.
8. To filter the data further, click on the “Filter” button in the “PivotTable Analyze” tab and select the criteria you want to use.
9. To update the pivot table with any changes to the filtered data, right-click on the table and select “Refresh.”
By following these steps, you can easily create a pivot table in Excel using filtered data, making it easier to analyze and understand your data.
Excel: 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:
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I create a pivot table in Excel using filtered data?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-in-excel-using-filtered-data/
stats writer. "How can I create a pivot table in Excel using filtered data?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-in-excel-using-filtered-data/.
stats writer. "How can I create a pivot table in Excel using filtered data?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-in-excel-using-filtered-data/.
stats writer (2024) 'How can I create a pivot table in Excel using filtered data?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-in-excel-using-filtered-data/.
[1] stats writer, "How can I create a pivot table in Excel using filtered data?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I create a pivot table in Excel using filtered data?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.