Excel pivot table is a fantastic tool to summarize large datasets and analyze them accordingly. When working with pivot tables, you may need to filter your pivot table sometimes. In this regard, you can get some extra advantages by inserting slicers. In this article, I will discuss pivot table slicers.
What Is Pivot Table Slicer?
A Slicer is an interactive control. A Slicer makes it easy to filter data in a Pivot table.
Suppose, you have a large dataset’s pivot table containing your sales and profits in individual quarters, months, states, and regions.
So, now you can filter your sales or profits or both according to quarters, months, states, or regions by using slicers. If you want to filter the whole pivot table according to QTR- 1 and Oregon state, then you can insert slicers accordingly and get the outcome as follows:
Add Pivot Table Slicers in Excel 2010/2013/2016/2019/365 Versions: 3 Ways
Say, you have a large dataset containing your sales and profits at individual quarters, months, regions, and states. Now, you want to insert a slicer for easy filtering in the dataset’s pivot table. You can follow any of the 2 simple ways below to achieve this.
1. Using Pivot Table Fields List
You can simply use the pivot table’s fields list to insert a slicer. Follow the steps below to accomplish this.
- First and foremost, select your whole dataset (B4:G82 here).
- Subsequently, go to the Insert tab >> Pivot Table tool >> From Table/Range option.
- As a result, the PivotTable from table or range window will appear.
- Here, refer to your dataset range, put the radio button on the New Worksheet option, and click on the OK button.
- Thus, there will appear a new pane named PivotTable Fields on the right side of your Excel sheet.
- Following, tick all the pivot table fields, and you will get the pivot table of your whole dataset.
- As a result, the pivot table will now look like this.
- Now, if you want to filter through Qtr, right-click on the Qtr field in the pane and choose the option Add as Slicer from the context menu.
- Similarly, if you want to add another filter through State, add the State field as another slicer.
- Now, say you want to filter for Illinois state and Qtr- 2 sales and profits. So, click on the Illinois state button and the Qtr- 2 buttons.
Consequently, you can see your enormous dataset’s pivot table got shortened, and it is only showing the values of sales and profits for Illinois state in Qtr- 2. For instance, the outcome would look like this.
2. Using Pivot Table Analyze Tab to Insert Slicer
Another simple thing you can do to insert a slicer is to use the Pivot Table Analyze tab. Go through the steps below to do this.
- At the very beginning, follow the first 7 steps from the previous method to learn how to insert a pivot table from a table or data range.
- Afterward, select any cell inside the pivot table >> go to the Pivot Table Analyze tab >> Filter group >> Insert Slicer tool.
- At this time, the Insert Slicers window will appear.
- Following, tick on the State and Qtr.
- Last but not the least, click on the Ok button.
Thus, you can insert a slicer into the pivot table and filter it accordingly. Now, if you want to get the values for Illinois state at the Qtr-2, then click on the Illinois and Qtr- 2 buttons. Finally, for example, the output should look like this after the slicing.
3. Insert Slicers in Excel 2010
In Excel 2010, you can add slicers only in pivot tables. You can do this using any of the two given workflows below:
- Go to Insert tab >> Insert Slicer tool.
- Go to the Options tab >> Sort & Filter group >> Insert Slicer tool.
Use of Pivot Table Slicers in Excel
- If you want to select multiple criteria inside a slicer, you can do that by holding the Ctrl key and selecting each criterion one by one.
- The icon on the top right corner of a slicer is the Clear Filter button. You can clear all the filters of your slicer by clicking on this button or pressing the Alt+C key.
- The button beside the Clear Filter button is the Multi-Select toggle button. You can click on this button and then select multiple filters from the slicer. In this case, you won’t need to hold the Ctrl key to select multiple items. Again, if your multi-selection finishes, you can click it again to turn off the multi-selection process. You can use the Alt + S key to do this too.
- Besides, you can connect slicers to multiple pivot tables using the Report Connections… option.
- As slicers respond instantly and dynamically, you can create interactive charts using slicers in pivot tables.
- You can also resize slicers and change the style of slicers using the Slicer tab.
Excel Pivot Table Slicers Vs. Filter
Now, sometimes, it might appear to you that the pivot table slicers and filters are the same. But, actually, they aren’t. That’s why I am describing some differences between them.
- Filters generally work on a single pivot table, whereas slicers can work on multiple pivot tables.
- Slicers can make more dynamic and interactive dataset summaries and charts than filters.
- You will need more code to implement slicers than filters when it comes to VBA applications.
- Slicers are easier and quicker to use than filters.
- Filters generally take only one cell of your Excel file, whereas slicers can take multiple cells of your Excel sheet.
- For automation and touchscreen purposes, slicers are a better choice than filters without any doubt.
How to Fix If Pivot Table Slicer in Excel Is Not Working
Sometimes, you might face a problem working with a pivot table slicer. The slicer option might not be available or might be greyed out. This may happen mainly because of the reasons below:
- If your Excel file is older than Excel 2003 version. Because before Excel 2003, there was no slicer tool in Excel.
- If your Excel file is in Compatibility mode for any reason.
- If your Excel file is saved in the .xls format.
You can check if your Excel file is in Compatibility mode by following the ways given below.
- You can notice the top bar of the Excel sheet where the Excel file’s name and mode are noted.
- You can go to the File tab >> Info Option >> Check for Issues button >> Check Compatibility option.
Now, if your Excel file is in the Compatibility mode, you will find a button named Convert in the Info window. Subsequently, click on the Convert button and you will be able to use the slicer in your Excel file.
In Excel 2013, Slicers are no longer limited to pivot tables. The slicer can also be applied to a table.
Download Practice Workbook
You can download our practice workbook from here for free!
In this article, I have discussed pivot table slicers at large. I hope you found this article helpful and informative. Please drop comments, suggestions, or queries if you have any in the comment section below.