Filter data in a PivotTable
Filtering data in a PivotTable allows you to focus on specific portions of your data, such as a particular time period, category, or range of values. Filters can be applied to fields in the Rows, Columns, Values, and Filters areas, helping you to refine your analysis and view only relevant information.
Here’s how to filter data in a PivotTable:
1. Use the Filter Area in a PivotTable
Add a Filter to a PivotTable:
To add a filter, you can drag a field into the Filters area of the PivotTable Field List.
- Open the Field List: If it’s not already visible, click inside the PivotTable to open the PivotTable Field List.
- Drag a Field to the Filter Area:
- For example, if you want to filter by Region, drag the Region field from the Field List to the Filters area.
- Apply the Filter:
- Once the field is in the Filters area, a filter drop-down will appear above your PivotTable.
- Click the drop-down arrow to select specific items you want to filter by. For example, you can choose only a certain Region (like “North America” or “Europe”).
- Click OK: After selecting the items, click OK to apply the filter.
2. Filter Data Directly in the PivotTable
You can also apply filters directly within the PivotTable itself, without using the Filters area.
Filter Data in Rows or Columns:
To filter data within Rows or Columns areas of the PivotTable:
- Click the Filter Arrow: In the Rows or Columns area, click the drop-down filter arrow next to a field label (e.g., Product, Region).
- Select Filter Criteria:
- Check or uncheck the items you want to include or exclude from your PivotTable. For example, if you have a Product field in the Rows area, you can select only certain products to show in the PivotTable.
- You can also choose Label Filters (e.g., Begins With, Contains) or Value Filters (e.g., Greater Than, Less Than) for more complex filtering.
Steps for Filtering Rows or Columns:
- Click the drop-down arrow next to the row or column label.
- In the filter menu, check or uncheck the items to display only the desired data.
- Alternatively, you can click Label Filters or Value Filters for more advanced filtering options.
3. Apply Value Filters to a PivotTable
Value filters allow you to filter the data based on the values in the Values area (e.g., sales amounts, totals). This is useful when you want to show only records with a certain level of performance or numerical criteria.
Steps to Apply a Value Filter:
- Click the Drop-down Arrow: In the Values area of the PivotTable, click the drop-down arrow next to the field you want to filter (e.g., Sales Amount).
- Select Value Filters:
- From the menu, choose Value Filters.
- You’ll be presented with options such as:
- Greater Than
- Less Than
- Top 10
- Bottom 10
- Specify the Filter Criteria:
- For example, if you select Greater Than, you can enter a specific value, such as filtering to show only products with sales greater than $500.
- Similarly, you can use the Top 10 option to show the top 10 items based on a value, such as the top 10 products by sales.
4. Filter Data by Multiple Criteria
You can apply multiple filters to refine your data even further. For example, you might filter by both Region and Product simultaneously.
Steps to Filter by Multiple Criteria:
- Apply a Filter to the PivotTable: First, add a filter to the Filters area or apply filters to Rows or Columns.
- Apply Additional Filters:
- You can use multiple filter drop-downs to filter by more than one criterion. For example, you can filter by Region in the Filters area and filter by Product in the Rows area.
- Combine Filters: The filters will apply together, narrowing down the data to meet all the specified conditions.
5. Filter Using Slicers
A Slicer is a visual filtering tool that allows you to filter PivotTable data interactively. It provides buttons you can click to filter your PivotTable by categories.
Steps to Add a Slicer:
- Insert a Slicer:
- Click anywhere in the PivotTable.
- Go to the PivotTable Analyze tab on the Ribbon.
- Click Insert Slicer.
- Select Fields for Slicing:
- In the Insert Slicers dialog box, select the fields you want to use for filtering (e.g., Region, Product).
- Click OK.
- Use the Slicer:
- A slicer box will appear. You can click on the buttons within the slicer to filter the PivotTable.
- You can also select multiple items by holding the Ctrl key while clicking.
Clear Slicer Filter:
To clear the filter applied through a slicer, click the Clear Filter button in the slicer (the icon looks like a funnel with a red “X”).
6. Clear Filters from a PivotTable
If you want to remove all the filters and return to the original, unfiltered PivotTable view, you can easily clear the filters.
Steps to Clear Filters:
- Clear a Filter from a Field:
- Click the drop-down arrow next to a field in the Rows, Columns, or Filters area.
- Select Clear Filter From [Field Name] to remove the filter for that field.
- Clear All Filters:
- You can also remove all filters by going to the PivotTable Analyze tab and clicking Clear in the Actions group.
7. Example of Filtering a PivotTable
Let’s say you have a sales dataset with Date, Product, Region, and Sales Amount. You want to analyze the total sales for specific regions and products.
Steps:
- Drag Region to the Filters area and Product to the Rows area of the PivotTable.
- Apply a Filter for Region: Click the drop-down next to Region in the Filters area and select the region you want to analyze (e.g., “North America”).
- Apply a Value Filter: To analyze only products with sales greater than $500, click the drop-down next to Sales Amount in the Values area and select Value Filters > Greater Than, then enter 500.
- Use Slicers: To add more interactivity, insert a slicer for Product and Region so you can click to filter your PivotTable dynamically.
Conclusion
Filtering data in a PivotTable is a powerful way to focus on specific parts of your dataset. By using filters in the Rows, Columns, Values, or Filters areas, as well as slicers and value filters, you can refine your analysis and extract the most relevant information from your data.