There are plenty of great reasons to use pivot tables in Excel, from summarizing large amounts of data to making charts out of them (I personally love pivot tables because they make it so easy to do the latter). However, there’s one caveat that you need to keep in mind: if you use too many filters in your pivot table, it could take forever to refresh and could even cause Excel to crash. This article will show you exactly how much you can filter before things start getting wonky with your pivot table, as well as tips on how to fix any issues that may arise due to excessive filtering.
Use an Excel Dashboard
If you’re working with large data sets in Excel, chances are you’ll need to use a pivot table at some point. Pivot tables are a great way to summarize and analyze data, but they can be tricky to filter. In this post, we’ll show you how to filter a pivot table in Excel the right way.
- Open your dashboard by clicking on Dashboard from the View tab. You should see an empty dashboard which is ready for customization.
- Select the Create a new workbook option from the Create tab and then select Blank Workbook from the New button in the dialog box that pops up next:
- Now open up your workbook by clicking on it in your Recent Workbooks list or by clicking on its title in the Navigation pane:
- On the Ribbon menu, click on Insert Chart to insert a chart in your dashboard.
- Click on Design Change Colors… from the Chart Tools Design tab to change the colors of this chart:
- Press Ctrl+A on your keyboard to select all objects in the dashboard and then press Ctrl+C followed by Ctrl+V twice so that there are three copies of your chart present in total:
- To create a second workbook, click on the Add Sheet icon in the upper-left corner of your screen and then choose Add Sheet From Existing Sheet:
- Choose one of the charts as your starting sheet and then click OK to copy it into another sheet in your dashboard:
- Repeat steps 7-8 two more times until you have four sheets containing one copy each of your original chart in them – don’t forget to name each sheet as appropriate!
- Now head back to your first workbook by clicking on its title in the Navigation pane:
- With the first workbook selected, go to Data > Data Tools > Options > Show ranges in formulas and check it off before pressing OK:
- Now go back to your second sheet
Use the Quick Analyze Options
When you want to analyze your data, the first thing you need to do is filter it. Excel offers a few ways to do this, but the best way is to use the Quick Analyze options. These options let you quickly and easily filter your data so that you can focus on the information that’s most important to you. Here’s how to use them
- Click any column header for which you would like to filter data (e.g., region).
- Click the Quick Analysis button at the top of your screen.
- Select Filter.
- In the drop-down menu next to Field, select Region.
- In the drop-down menu next to Where Condition, select Equal To or Is Greater Than/Less Than.
Use Formulas and Functions
You can use formulas and functions to filter your pivot table in Excel. This is the most versatile method, as it allows you to use any criteria you want. However, it can be a bit complex if you’re not familiar with formulas and functions. For this tutorial, we’ll show you how to do this using three very common ones: SUMIFS, COUNTIFS, and COUNTBLANK.
Use Shortcuts
If you’re working with large data sets in Excel, you know how important it is to be able to filter information quickly and easily. pivot tables make it easy to do just that. But there are two types of filtering: using the field list at the top of the table or using quick filters, which live on the column headers. Here’s how to use them for quick filters, right-click any column header and select Filter.
You’ll see a new window appear where you can specify what type of records you want to be displayed. The options are Show All Records, Hide Empty Fields, Filter Out Duplicates, and Filter By Group.