Excel Pivot Table Slicers: All Things You Need to Know

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.


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Pivot Table Slicer?

A Slicer is an interactive control. A Slicer makes your job 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 can get the outcome as follows:

Pivot Table Slicers


3 Ways to Add Pivot Table Slicers in Excel 2010/2013/ 2016/ 2019/ 365 Versions

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.

Sample dataset to insert slicer


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.

📌 Steps:

  • First and foremost, select your whole dataset (B4:G82 here).
  • Subsequently, go to the Insert tab >> Pivot Table tool >> From Table/Range option.

Insert a Pivot Table for your Dataset

  • 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.

Confirm the Inserting Pivot Table Information

  • 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.

PivotTable Fields Pane

  • As a result, the pivot table will now look like this.

Pivot Table

  • 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.

Add the Qtr Fields as Slicer in the Pivot Table

  • Similarly, if you want to add another filter through State, add the State field as another slicer.

Pivot Table Slicers

  • 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 at Qtr- 2. For instance, the outcome would look like this.

Filtered Pivot Table by Slicers


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.

📌 Steps:

  • 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.

Use the Pivot Table Analyze Tab to Insert Pivot Table Slicers

  • 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.

Insert Slicers Window

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.

Filtered Pivot Table by Slicers


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.

Clear Filter Button of Slicer

  • 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.

Slicer's Multi-Select Toggle Button

  • 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.

Pivot Table Slicer 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 touch screen purposes, slicers are a better choice than filters without any doubt.

How to Fix If Pivot Table Slicer 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.

Excel File in Compatibility Mode

  • You can go to the File tab >> Info Option >> Check for Issues button >> Check Compatibility option.

Check Excel File's Compatibility

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 thus you will be able to use the slicer in your Excel file.

Get Rid Of Compatibility Mode


Quick Notes

  • In Excel 2013, Slicers are no more limited to pivot tables. The slicer can also be applied to a table.

Conclusion

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.

Moreover, you can visit our website ExcelDemy to learn more about Excel-related content.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo