fbpx

The Ultimate Guide to Excel Pivot Table Slicers

Yodalearning>Tutorials>The Ultimate Guide to Excel Pivot Table Slicers

Slicers in Excel are often termed as Visual Filters. With 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.

Other Filters also you can do the same. Let’s consider the Report Filter. The difference is, here you have to click several times to pick a particular division. Whereas, with Slicers, it’s getting done with a simple click.

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

 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. Click OK
  5. Click on the 2nd Pivot and Select the Column you want to use as a Filter for the Slicer
  6. 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

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? Watch our Beginner to Advanced Excel Course Free Videos

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 are used as the Buttons, using which you can filter Data. Slicers in Excel, allow you to see what items have been selected within a Pivot Table. You must 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

Step 1: Click anywhere inside the Pivot table to activate Pivot Tool Options

Step 2: Go to Analyze (Excel 2013 & 2016)

Step 3: Click on Insert Slicer (Go to the Options tab and click on Insert Slicer if it is an older version of Excel)

How to Insert Slicer in Excel

Step 4: Select the Year and Month as the Pivot Table Fields, in the Insert Slicer Dialog box.

Click OK

Woho! You can see the slicer in front of you.

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

You just learned how to Insert Slicer in Excel.

TIP: 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 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:

In order to 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 changed and you can show this in 3 separate columns, and this would 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 Scroll bar you can select the no. of Columns you need. You can increase as well as decrease the no. of columns using the Scroll bar. Now, you have to manually type the number of columns you need to show your Slicer.

Downlaods

Step 1: Select your Slicer

Step 2: Go to Slicer Tools > Options > Buttons > Columns 

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

Step 1: Select your Slicer.

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

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.

Step 1: Click anywhere in your data set

Step 2:  Go to Insert. Select Pivot Table

Step 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:

Selecting Plant as Rows and Sum of Book Value as Values.

Step 1: Click anywhere in your data set

Step 2:  Go to Insert. Select Pivot Table

Step 3: 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)

And then click OK

Step 4: You’ll see both the Pivot Table in the same Worksheet

Pivot table with slicer

Steps to insert Slicer for Pivot Table1:

Step 1: Click on the first Pivot Table.

Step 2: Go to the PivotTable Tools > Analyze/Options > Insert Slicer

Step 3: In the Insert Slicer Dialog Box, Select Months as a Filter for the Slicer

And then 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).

Step 1: Click on the second Pivot Table.

Step 2: Go to the PivotTable Tools > Analyze/Options > Insert Slicer

Step 3: In the Insert Slicer Dialog Box, Select Year as a Filter for the Slicer

And then 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:

Step 1: Right-click on the first Slicer (in our case Month)

Step 2: Go to Report Connections (Excel 2013 & 2016) and if you have got an older version of Excel (2010)

Step 3: In the Report Connection dialog Box, tick/check on Pivot Table 3 box

And then press OK

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

Step 4: Right-click on the second Slicer (in our case Year)

Step 5: Go to Report Connections (Excel 2013 & 2016) and if you have got an older version of Excel (2010)

Step 6: In the Report Connection dialog Box, tick/check on Pivot Table 2 box

And then 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, when you share an Excel Pivot Table with your Colleagues there is a high risk of your Data getting exposed. Howsoever, you wouldn’t want any of the Users to have the access to your Data. This is only because they might end up messing up with your Pivot Table Layout and Format.

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

Look at the mentioned steps by which you can lock the Pivot Table but not the Slicers:

DOWNLOAD UNLOCKED EXCEL WORKBOOK

DOWNLOAD LOCKED EXCEL WORKBOOK

STEP 1: Select both the Slicers. (You have to hold the CTRL key and select the other slicer)

STEP 2: Right-click on any of the selected Slicers

STEP 3: Go to Size & Properties

STEP 4: Under Size & Properties Uncheck” the Locked box.

STEP 5: Select Review Tab> Protect Sheet

STEP 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)

STEP 7: Press OK

Lock pivot table but not slicer

You just read and learned about Slicers in Excel. The tutorial covered how you can insert a Slicer. The different ways to filter an Excel Slicer. How to add Columns to a 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.

I recently wrote a blog on Degree Symbol in Excel. If you wish to learn “How to use degree symbol in Excel” Click here

Yoda learning Group

Related Tutorials

How to Wrap Text in Excel Automatically and Manually
June 6, 2018
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
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
Insert Symbol Dialog Box to Insert or Type degree symbol in Excel
May 7, 2018
CHAR Formula to Insert or Type degree symbol in Excel
May 7, 2018
How to type degree symbol in Excel using Keyboard Shortcut
May 7, 2018
HOW TO FREEZE ROWS AND COLUMN IN EXCEL?
May 4, 2018
What are Reference Styles? How to Use R1C1 Reference Style in Excel
May 3, 2018
How to use the Histogram Tool in Excel (Step By Step Guide)
April 24, 2018
What is Flash Fill ? How to use flash fill in Excel ?
April 23, 2018
How TO TRACK CHANGES in MS Word
April 23, 2018
Undo – Redo Shortcuts Keys in Excel
April 18, 2018
How To Remove Blank Rows – The COUNTBLANK Function
April 9, 2018
How to match names in Excel where spelling differ or vary
April 9, 2018
Learn How to enable or disable scroll lock in Excel
April 7, 2018
How To Make Table of Contents (TOC)
April 2, 2018