Nov 02, 2015 Hi I'm trying to create a dashboard for the company I work with to display all the analytics of app downloads. I've tried doing this in my original Excel 07 but after watching several YouTube videos it looks like I'll need to do it on Excel 2011. 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).
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 Dashboard 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
- Click anywhere inside the table and select your Pivot Table
- 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
- With the Left Mouse Click
- By dragging the left mouse Button
- With the help of the CTRL Keyboard
- With the help of the Shift Keyboard
Topic 3: Add Columns Different Ways to Filter an Excel Slicer to Slicer Buttons
- Select your Slicer
- Go to Slicer Tools > Options > Buttons > Columns
- Select the of Columns
Topic 4: Slicer Styles & Settings
- Select your Slicer
- Go to the Slicer Tools > Options > Slicer Styles
- Select a style that you’d prefer
Topic 5: Connect Slicers to Multiple Excel Pivot Tables
- Create 2 Pivot Tables. Click anywhere in your data set
- To setup Pivot Table 1: Insert > Pivot Table > New Worksheet/Existing Worksheet
- Click on the 1st Pivot and Select the Column you want to use as a Filter for the Slicer
- Then, Click OK
- Now Click on the 2nd Pivot and Select the Column you want to use as a Filter for the Slicer
- Then, Click OK
You can do this by going to PivotTable Tools > Analyze/Options > Insert Slicer
[xyz-ihs snippet=”Excel-Course”]
Topic 6: Lock the Excel Pivot Table but NOT the Slicer
- Go to Slicer and click on a Slicer
- Select the other Select the other slicers by holding the CTRL key
- Select Size & Properties by right-clicking on a Slicer
- “Uncheck” the Locked box. This is present under Properties and then press Close
- Select Review > Protect Sheet. Go to the ribbon menu
- “Uncheck” the Locked Cells that were Selected and “Check” the Unlocked Cells that were selected. Now, use the Pivot Table Reports
- Enter a password if you wish to(optional)
- 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.
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:
Download Excel Sample File
- Click anywhere inside the Pivot table to activate Pivot Tool Options
- Go to Analyze (Excel 2013 & 2016)
- 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)
- Select the Year and Month as the Pivot Table Fields, in the Insert Slicer Dialog box.
- Then, ClickOK
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:
- With the Left Mouse Click:
- Using your left Mouse Button, select the items from the Slicer
- By dragging the left mouse Button:
- Using your left Mouse Button, select the items from the Slicer
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.
- 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
- 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
You just learned the different ways to filter an Excel Slicer.
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 toSlicer 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.
- Select your Slicer
- Go to Slicer Tools > Options > Buttons > Columns
- Step 3: Select no. 3 in the Columns.
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.
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.
- Select your Slicer.
- Go to the Slicer Tools > Options > Slicer Styles
Select 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
[xyz-ihs snippet=”Excel-online-Course”]
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:
Download Excel Sample File
Tip: Name the Table from where you’re going to take the Data. We have named it as Table 1.
Use Slicers In Excel
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
Create Pivot Table1:
Selecting Material Type as Rows and Sum of Book Value as Values. Aquasure xpert water purifier manual.
- Click anywhere in your data set
- Go to Insert. Select Pivot Table
- Select New Worksheet
(In the Create Pivot Table Dialog Box > Select a Table/Range, Select the New Worksheet Checkbox)
Create Pivot Table2:
- Selecting Plant as Rows and Sum of Book Value as Values.
- Click anywhere in your data set
- 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
- You’ll see both the Pivot Table in the same Worksheet
Steps to insert Slicer for Pivot Table1:
- Click on the first Pivot Table.
- Go to the PivotTable Tools > Analyze/Options > Insert Slicer
- In the Insert Slicer Dialog Box, Select Monthsas a Filter for the Slicer
- Click OK
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 Yearas a Filter for the Slicer
- Click OK
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:
- Right-click on the first Slicer (in our case Month)
- Go to Report Connections (Excel 2013 & 2016) and if you have got an older version of Excel (2010)
- In the Report Connection dialog Box, tick/check on Pivot Table 3 box
- Press OK
- Note: Repeat the same steps to connect the second Slicer to the Pivot Table (Year Slicer in this case).
- Right-click on the second Slicer (in our case Year)
- Go to Report Connections (Excel 2013 & 2016) and if you have got an older version of Excel (2010)
- In the Report Connection dialog Box, tick/check on Pivot Table 2 box
- Press OK
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.
[xyz-ihs snippet=”Excel-Curriculum”]
Steps by which you can lock the Pivot Table but not the Slicers:
Slicers In Excel For Macbook Pro
- Select both the Slicers. (You have to hold the CTRL key and select the other slicer)
- Right-click on any of the selected Slicers
- Go to Size & Properties
- Under Size & Properties “Uncheck” the Locked box.
- Select Review Tab> Protect Sheet
- “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)
- Press OK
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.