How to Create a Pivot Table From a Filtered List in Excel

Introduction

Creating a Pivot Table from a filtered list in Excel can be challenging, as Excel typically ignores applied filters when generating Pivot Tables. This tutorial will guide you through a simple technique to create a Pivot Table that reflects your filtered data, helping you analyze the information more effectively.

Step 1: Prepare Your Data

Before creating a Pivot Table, ensure your data is organized and filtered correctly.

• Open your Excel workbook with the data you want to analyze.
• Select the range of data you want to work with, including headers.
• Apply a filter to your data:
  • Click on the "Data" tab.
  • Select "Filter" to activate filter options on your headers.
• Use the dropdown arrows in the column headers to apply your desired filters.

Step 2: Add a Helper Column

Since Excel ignores filters when creating a Pivot Table, you will need to add a helper column to isolate the visible (filtered) data.

• Create a new column next to your data range, naming it "Visible" or something similar.
• In the first cell of this new column, enter the following formula:

  =IF(SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0)), "Yes", "No")
  

  • Adjust the range A2:A100 to match your actual data range.
• Drag the fill handle down to apply this formula to all rows in your dataset.

Step 3: Create the Pivot Table

Now that you've prepared your data, you can create the Pivot Table based on the filtered list.

• Select your entire dataset including the helper column.
• Go to the "Insert" tab on the ribbon.
• Click on "PivotTable."
• In the dialog box, ensure the selected range includes your helper column.
• Choose where you want the Pivot Table to be placed (new worksheet or existing worksheet) and click "OK."

Step 4: Filter the Pivot Table

Once the Pivot Table is created, you can filter it to show only the visible data.

• In the Pivot Table Field List, drag the "Visible" column to the Filters area.
• Set the filter to show only "Yes" to include only the rows that were visible before applying the filter.

Step 5: Analyze Your Data

With the Pivot Table now reflecting only the filtered data, you can proceed to analyze your information.

• Drag and drop other fields from your data into the Rows, Columns, and Values areas of the Pivot Table Field List to summarize your data as needed.
• Use the Pivot Table tools to customize the layout and presentation of your data.

Conclusion

Creating a Pivot Table from a filtered list in Excel requires a bit of extra work by adding a helper column. By following these steps, you can effectively analyze only the data that meets your specified criteria. For further exploration, consider looking into more advanced Pivot Table features or experimenting with different data sets to strengthen your Excel skills.