Pivot tables are a particularly useful tool when we have to work with a large amount of data and we want to segment it so we can see it and use it in the most effective way possible. To the add a filter to a pivot table, we make this even easier.
On a previous occasion we already talked to you about how to create pivot tables in Excel, but today we are going to continue advancing in this matter so that your tables become increasingly more professional and it is easier for you to analyze the information contained in them.
Table of Contents
Why are pivot tables so useful?
Learning to use Excel and get the most out of it is not always easy. This is because this office tool It has a large number of features, and it is difficult to know them and know how to apply them all. But, if there is something you have to know how to do, it is to create a dynamic table and then apply the corresponding filters to segment the information.
If you still have doubts about the usefulness of these tables, pay close attention:
- Data summary. Through these tables we can create files with large amounts of information and make it more manageable. By applying different filters we access key information in just a few seconds.
- Grouping and segmentation. Understanding data is much easier when we group it based on one or more categories. For example, if we do accounting for a business, it will be interesting to group income and expense data based on the quarter in which they occurred.
- Custom filters. You can create all the filters you want and segment the information as much as you want. The more specific the filters are, the easier and faster it will be to access specific information.
- Data comparison. A dynamic table compares different data with each other, something that can be very useful when making decisions.
- Trend analysis. If you go beyond comparing data, analyzing the figures you can detect certain trends. For example, if you are selling more units of a certain item.
- Automatic update. The table is automatically updated when there is a change in any of its data. This guarantees access to real-time information at all times.
- Time saving. With pivot tables you don't have to complicate your life by creating complicated formulas that summarize the data. This saves time and reduces the chances of making a mistake.
- Custom reports. As you learn to use pivot tables and filter their data, you'll be able to create custom reports in just a few minutes.
- Interactive exploration. Switching from one filter to another allows you to see different data in a table that contains a lot of information. By playing with the fields and filters you can do an in-depth analysis from different perspectives.
How to add filter to a pivot table
Now that you know how useful this tool can be, it's time to analyze in detail what to do to put a filter inside a pivot table. Let's go step by step.
Prepare the information
Before starting to work with a table we have to make sure that we have the information well organized in an Excel sheet. Checking that each column has a heading that clearly identifies it.
This, which may seem unimportant, is actually not, because a good header will allow Excel to recognize the information well and work with it better.
Select the data
This step is as simple as opening the spreadsheet and Click on any cell within the range of data that you are going to include in the dynamic table. To make it a little easier, you can use the pivot table wizard by clicking on the tab "Insert" and then "Dynamic table", a wizard will appear to guide you through the process.
If you are an advanced Excel user you can make the table without assistance. Manually defining the range of data you want to work with.
Once the table is created, you have the option of leaving it on the same sheet or taking it to a new one.
Apply the filter
At this point you have already created your dynamic table and have all the fields that contain the information you want to work with well located. It's time to add a filter to a pivot table. To do this, follow these steps:
- Select the filter field. Drag a field into the “Filter Area” section to create a filter box at the top of the table. It is a drop-down box or list with the fields that represent the different categories by which you can segment the data.
- Open the filter box. Click on the drop-down arrow for the field you want to apply as a filter and you will be shown new options available to apply to that field.
- Select filter options. Within that dialog box, choose the boxes that correspond to the options you want to include in the filter. In some cases you may see multiple selection options. Choose what best suits your needs.
- Apply the filter. After choosing the filter options you have to click on "Accept" or "Apply" for them to take effect. The table will then update automatically and Excel will show you only the data that matches the search criteria you have specified.
- Modify or delete the filter. If you need to delete the created filter, or change it to another one, reopen the filter box, click on the drop-down arrow and adjust again according to your preferences.
The good thing about filters applied to pivot tables is that they allow you explore information and analyze it according to your needs. Saving you a large amount of time and without the need to make modifications to the original data. Precisely for this reason, we are facing one of the most important functionalities that Excel offers us.
As you have seen, adding a filter to a pivot table is not as complicated as it seems. Once you have done it a couple of times you will be a true expert in this functionality. Can you tell us your experience with these tables?