Pivot Table Slicers In Excel

Slicers in Excel are often termed as Visual Filters. With Pivot table Slicers in Excel, you can filter Pivot Tables. Besides, Report Filter can also be used to do the same task. Slicers were first introduced in Pivot Tables in Excel 2010 for Windows. And it came in Excel for Mac in 2016.

Slicers in Excel are the Buttons, using which you can filter Data (of the Pivot Table or the Pivot Chart). Click on the data type that you want. Slicer in Excel will make it a lot easier for you. It will help to comprehend the distinctness shown in the Filtered Pivot Table. Adding interactivity in Reports has always been beneficial.

In case, if you love Dashboarding and filtering, Excel Slicers are the best to do so.

A Pivot Table Slicer helps to customize the Pivot Table. Unfortunately, Slicer helps to filter and connect many Pivot Tables as per your wish.

Topic 1: Insert a Slicer

  1. Click anywhere inside the table and select your Pivot Table
  2. Go to Analyze (Excel 2013 & 2016) and click on Insert Slicer. Go to the Options tab and click on Insert Slicer if it is an older version of Excel. It will add pivot table in Slicer.

 Topic 2: Different Ways to Filter an Excel Slicer

  1. With the Left Mouse Click
  2. By dragging the left mouse Button
  3. With the help of the CTRL Keyboard
  4. With the help of the Shift Keyboard

 Topic 3: Add Columns Different Ways to Filter an Excel Slicer to Slicer Buttons

  1. Select your Slicer
  2. Go to Slicer Tools > Options > Buttons > Columns
  3. Select the of Columns

 Topic 4: Slicer Styles & Settings

  1. Select your Slicer
  2. Go to the Slicer Tools > Options > Slicer Styles
  3. Select a style that you’d prefer

 Topic 5: Connect Slicers to Multiple Excel Pivot Tables

  1. Create 2 Pivot Tables. Click anywhere in your data set
  2. To setup Pivot Table 1: Insert > Pivot Table > New Worksheet/Existing Worksheet
  3. Click on the 1st Pivot and Select the Column you want to use as a Filter for the Slicer
  4. Then, Click OK
  5. Now Click on the 2nd Pivot and Select the Column you want to use as a Filter for the Slicer
  6. Then, Click OK

You can do this by going to PivotTable Tools > Analyze/Options > Insert Slicer

 Topic 6: Lock the Excel Pivot Table but NOT the Slicer

  1. Go to Slicer and click on a Slicer
  2. Select the other Select the other slicers by holding the CTRL key
  3. Select Size & Properties by right-clicking on a Slicer
  4. Uncheck” the Locked box. This is present under Properties and then press Close
  5. Select Review > Protect Sheet. Go to the ribbon menu
  6. Uncheck” the Locked Cells that were Selected and “Check” the Unlocked Cells that were selected. Now, use the Pivot Table Reports
  7. Enter a password if you wish to(optional)
  8. Press OK

Since Slicer is something which every user should be aware of but do you know pivot table, flash fill, VBA? If not, why don’t you become expert in excel? Beside that Watch our Beginner to Advanced Excel Course which makes you 4x Faster

Let’s take the examples and see how to do this in detail:

Do you know anything about Conditional Formatting with Formulas? Sometime back, I wrote a blog on Conditional Formatting with Formulas. You can learn a lot from it. Click on the link to get directed to the blog.

How to Insert a Slicer in Excel?

Slicers in Excel, allow you to see what items have been selected within a Pivot Table. You have to understand Slicers’ in order to woo your team.

Let’s look at how we can insert a Slicer in Excel:How to Insert Slicer in Excel

DownlaodsDownload Excel Sample File

  1. Click anywhere inside the Pivot table to activate Pivot Tool Options
  2. Go to Analyze (Excel 2013 & 2016)
  3. Click on Insert Slicer (Go to the Options tab. Click on Insert Slicer to add pivot table slicer, if it is an older version of Excel)How to Insert Slicer in Excel
  4. Select the Year and Month as the Pivot Table Fields, in the Insert Slicer Dialog box.
  5. Then, Click OK

As a result, You can see the slicer in front of you.

Since you’ve obtained a Slicer. Now you can play around with your Data.

Conclusion: You just learned how to Insert Slicer in Excel.

TIP’s: In order to select more than one item, hold down the CTRL key to select multiple items in your Slicer.

What are the Different Ways to Filter an Excel Slicer?

Since you already know, ‘Slicer is a new feature which was introduced in Excel 2010 and in Mac for Excel in 2016. You must learn what are ways to Filer an Excel Slicer.

Excel Slicers are really wow. And, there isn’t one but several ways to filter an Excel Slicer.

Try it now by downloading this free workbook:

Downlaods

  • With the Left Mouse Click:
    • Using your left Mouse Button, select the items from the Slicer
      Select Item From Slicer
    • By dragging the left mouse Button:

Select an array of items. Click on the left mouse button and now drag it. Drag it in the motion Downwards and Upwards within the Slicer.

Selecting and Dragging Items In Slicer

  • With the help of the CTRL Keyboard:

In order to select more than one item, hold down the CTRL key to select multiple items in your Slicer

Select Image & Click Left Mouse

  • With the help of the Shift Keyboard:

Select a range of items, hold down the Shift key on your Keyboard to select a range of items in your Slicer

Shift Keyboard

You just learned the different ways to filter an Excel Slicer.

How to Add Columns to Slicer Buttons?

Adding an Excel Slicer to your Pivot Table always defaults to one column. This displays the items in a vertical layout.

If your Slicer displays the months i.e. from January to December (1-12), the layout of the buttons can be change and you can show this in 3 separate columns, and this will be considered a “Quarterly View”.

In order to add columns to Slicer Buttons:

Click on your Slicer. Go to Slicer Tools > Options > Buttons > Columns.

With the help of the Scrollbar you can select the no. of Columns you need. Therefore, You can increase as well as decrease the no. of columns using the Scrollbar. So now, you have to manually type the number of columns you need to show your Slicer.

Downlaods

  1. Select your Slicer
  2. Go to Slicer Tools > Options > Buttons > Columns 
  3. Step 3: Select no. 3 in the Columns.

Before-After-Slicer

Well, you can clearly see in the above image, your Column has a 3-Column Layout.

You just learned How to Add Columns to a Slicer Button.

Slicer Styles & Settings

There are quite a few and distinct Slicer Styles. You can find these when you click on an Excel Slicer.

You can find the Slicer Tools tab in the Ribbon under Options > Slicer Styles.

As you read it earlier, you can even add columns into a Slicer.

Check out for more Slicer Options by Right Clicking in a Slicer.

Downlaods

  1. Select your Slicer.
  2. Go to the Slicer Tools > Options > Slicer Styles

How to change styleSelect a style that you’d prefer.

Wow! As you can see, your slicer has got a completely different style now.

As a result, you just learned about Slicer Styles & Settings 

How to Connect Slicers to Multiple Excel Pivot Tables? 

Normally when you insert an Excel Slicer it is only connected to the Pivot Table that you are inserting it from.

Now imagine, if you’ve obtained Multiple Pivot Tables from the existing Dataset. Which step can you possibly take to connect all the Pivot Tables with the Slicer?

In such cases, you’ll want all the Pivot Tables to change with just a Press of a Button.

Learn how to connect a Slicer to Multiple Pivot Table?

This can be done with the Report Connections in Excel 2013 & 2016 and if you’ve got an older version of Excel i.e. Excel 2010. In that case, PivotTable Connections within the Slicer can be used.  Let’s see how this can be done:

DownlaodsDownload Excel Sample File

Tip: Name the Table from where you’re going to take the Data. We have named it as Table 1. 

If you’ve not named the Dataset, then you can follow these simple steps to do so:

  • Select the entire Data set
  • In the Name Box, type the name of the selected Data set

Name-the-data-set

Create Pivot Table1:

Selecting Material Type as Rows and Sum of Book Value as Values.

  1. Click anywhere in your data set
  2. Go to Insert. Select Pivot Table
  3. Select New Worksheet

(In the Create Pivot Table Dialog Box > Select a Table/Range, Select the New Worksheet Checkbox) 

the Create Pivot Table Dialog Box Select a TableRange, Select the New Worksheet Checkbox

Create Pivot Table2:

  1. Selecting Plant as Rows and Sum of Book Value as Values.
  2. Click anywhere in your data set
  3. Go to Insert. Select Pivot Table
    • In the Create Pivot Table Dialog Box > Select a Table/Range (We have selected Table1 as the Table Name)
    • Select the Existing Worksheet Checkbox (Assigning the location ‘Pivot table 1’! $A$11)
    • Then click OK
  4. You’ll see both the Pivot Table in the same Worksheet

Pivot table with slicer

Steps to insert Slicer for Pivot Table1:

  1. Click on the first Pivot Table.
  2. Go to the PivotTable Tools > Analyze/Options > Insert Slicer
  3. In the Insert Slicer Dialog Box, Select Months as a Filter for the Slicer
  4. Click OK

Pivot table picture 1

As you can see the Month Slicer has been inserted in front of you.

You’ve to repeat the same steps to insert a Slicer for the second Pivot Table (Year Slicer in this case).

  • Click on the second Pivot Table.
  • Go to the PivotTable Tools > Analyze/Options > Insert Slicer
  • In the Insert Slicer Dialog Box, Select Year as a Filter for the Slicer
  • Click OK

Pivot table picture 2

As you can see the Year Slicer has been inserted in front of you.

Right now, you have a slicer in your worksheet. With this, you can filter the pivot table in which you insert it.

Steps to Connect Multiple Slicer to a Pivot Table:

  1. Right-click on the first Slicer (in our case Month)
  2. Go to Report Connections (Excel 2013 & 2016) and if you have got an older version of Excel (2010)
  3. In the Report Connection dialog Box, tick/check on Pivot Table 3 box
  4. Press OK
    • Note: Repeat the same steps to connect the second Slicer to the Pivot Table (Year Slicer in this case).
  5. Right-click on the second Slicer (in our case Year)
  6. Go to Report Connections (Excel 2013 & 2016) and if you have got an older version of Excel (2010)
  7.  In the Report Connection dialog Box, tick/check on Pivot Table 2 box
  8. Press OK

Connecting Multiple slicer to pivot table

On selecting each Slicer’s items, you’ll find the Pivot Table will change.

How to Lock the Excel Pivot Table but not the Slicer?

At times, if you share an Excel Pivot Table with your Colleagues there is a high risk of your Data-exposed. Howsoever, you wouldn’t want any of the Users to have the access to your Data. As a result, they might end up messing up with your Pivot Table Layout and Format.

In these cases, you should lock your Pivot Table. Therefore, this will only allow the user(s) to make a selection of the Slicers. Here, your Report will look interactive. Also, this is even secure from Excel novices like your Boss.

Steps by which you can lock the Pivot Table but not the Slicers:

  1. Select both the Slicers. (You have to hold the CTRL key and select the other slicer)
  2. Right-click on any of the selected Slicers
  3. Go to Size & Properties
  4. Under Size & Properties Uncheck” the Locked box.
  5. Select Review Tab> Protect Sheet
  6. Uncheck” the Locked Cells that were Selected and “Check” the Unlocked Cells. Now, use the Pivot Table Reports
    • You can Enter a password if you wish to(optional)
  7. Press OK

Lock pivot table but not slicer

Seems like, you just read and learned about Slicers in Excel. The tutorial covered how you can insert a Slicer. Also, the different ways to filter an Excel Slicer. Furthermore, How to add Columns to a Pivot table Slicer, Slicer settings and styles . How to connect slicers to Multiple Pivot Tables in Excel. And, how to lock the Pivot Tables and not the Slicer in Excel.

Related Tutorials

How to use AVERAGEIFS FUNCTION in Excel
December 10, 2018
How to use AVERAGEIF FUNCTION
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018