Effectively using advanced filters in pivot tables

In this tutorial, you will learn how to perform advanced filtering in Pivot Tables in Excel. Pivot Tables are an extremely powerful tool for data analysis that allows you to quickly and efficiently analyze large amounts of data. By using advanced filters, you can filter specific data according to your criteria and thus conduct more precise analyses. Let's dive into the details together.

Key Insights

Advanced filters can apply specific criteria to efficiently search your data in Pivot Tables. This includes filtering by multiple conditions, such as specific months or shifts, and setting value limits.

Step-by-Step Guide

To utilize advanced filters in Pivot Tables, follow these steps:

First, you need a suitable data source. In our example, we are using data from an automotive supplier. Open Excel and load your data. To quickly filter the data, you can use the shortcut "Ctrl + Shift + L" or select the filter option in the ribbon under "Data." This will activate the data filter.

Now, create your Pivot Table by pressing "Ctrl + A" for all data and then "Ctrl + T" to export the data as a Pivot Table to a new worksheet. You should now see a blank Pivot Table in front of you.

To generate a meaningful overview, drag relevant data fields into the rows and columns areas. For our analysis, we focus on monthly quantities. To remove the years and quarters, only drag the monthly data into the Pivot Table. Make sure to also include the shifts (early, late, night) to have more granularity in your data.

You can customize the layout of your Pivot Table by clicking on "Report Layout" in the design. Change it to the tabular format to present your data more clearly. Now, the data is organized neatly, and you can further filter it.

To use the advanced filter, first click inside the Pivot Table. Note that you cannot select the advanced filter directly from the Pivot Table. Here's the trick: Click outside the table in an area that is not part of the Pivot Table. This allows you to click and use the filter.

Now, copy the headings of the Pivot Table. In our example, we are copying the first three headers that will help us in setting up the filter criteria. These steps are crucial to pre-select specific parameters.

Go to the criteria where you can specify that you only want to see January displayed. Furthermore, you can specify to only show the late shift. Additionally, you want to see only quantities under 50,000. Make sure you have the correct header for this criterion as well.

Now open the menu for the advanced filter. Here you specify the range that covers the entire data range of the Pivot Table. Supportively, you can select the complete field by pressing "Ctrl + A."

Now determine the criteria range by specifying the relevant columns with the headers and ensuring that the right column has an empty cell range. Then click "OK" to apply the filtering.

Once you confirm this, you will see the filtered values in the Pivot Table: Only late shifts, values less than 50,000, and only the data for January will be displayed. This is an effective way to quickly extract the desired information.

Alternatively to filtering, you can also copy the filtered data to another location. This can be very useful to prepare the relevant values cleanly for further analysis. You can simply paste this data into an empty cell below.

For the final evaluation, you can also copy the filtered values as value copies next to your original analysis. This way, you get clarity about your data and keep an overview of the filtering.

This was an introduction to using advanced filters in pivot tables. I encourage you to apply the learned techniques in your own case studies and analyses.

Summary

The advanced filtering function in pivot tables allows you to filter specific data according to your criteria and carry out more precise data analyses.