Slicers In Excel For Mac

by

Slicers are a visual way to filter data in a pivot table. With the Report Connections icon, you can have a slicer control multiple pivot tables on a dashboard—but only if all the pivot tables are from the same data set. Fortunately, there’s a separate way to control multiple pivot tables from different data sets, provided they have one field in common.

Jun 22, 2017 Slicers were first introduced in Pivot Tables in Excel 2010 for Windows and Excel 2016 for Mac. Pivot Table Slicers are a visual filter in the form of an interactive button. There are several cool things that you can do with Pivot Table Slicers, like customize them, filter them, connect them to multiple Pivot Tables plus much more! Slicers in Excel is a tool which is used to filter the data as per our need by slicing off a portion of data from the created table using Pivot Table option in Excel. To apply Slicer, first create a Pivot table in the form of a table which is available under the Insert menu option. Slicer is disabled in Excel 2016 for Mac I was trying to insert a slicer for the tabular data in the excel sheet and found that is is not enabled. When I have searched in forums I read that it is not available for Mac and is only for windows only.

Office 365, Excel for Windows 2010 & Excel for Mac Posts 27,184. Re: Exclude items from chart slicers (even if they have data). The slicer on this new field would. Hello, I've opened an excel dashboard I've put together on mac and discovered that the pivot slicers don't work - it is fine in Windows. Is there a setting that will allow the slicers to work? Many thanks, A PS - when attempting to filter the actual pivot tables I receive 'The pivot table.

For this example, you have a sales worksheet and a quality worksheet. You want to summarize both data sets in a dashboard. The goal is to have a slicer that can filter all the pivot tables by industry. Both data sets have a Sector field listing the industry sector.

Because this technique uses the Data Model, it only works in Windows versions of Excel. It won’t work on a Mac or Excel online.

Your first step is to create a new table that has a sorted, unique list of the industries found in either report. You might use an Advanced Filter for this, but an easy way is to copy the Sector column from both data sets to a new table and then use Data, Remove Duplicates to make sure each industry appears in the list just once.

Next, convert the original data sets and your new small table of sectors into a table. Select one cell in a data set and press Ctrl+T to create a table. Make sure My Table Has Headers is checked in the Create Table dialog box. Repeat for all three tables.

After creating a table, Excel uses names like Table1, Table2, and Table3. Using the box on the left side of the Table Design tab in the ribbon, rename the tables with descriptive names like Sales, Quality, and Sectors.

DEFINING RELATIONSHIPS

Click the Relationships icon on the Data tab to open the Manage Relationships dialog. Click the New button to create a relationship. The first relationship is from the Sales table to the Sectors table. Choose Sector as the related column in both tables.

Create a second relationship between the Quality table and the Sectors table.

To visualize the relationships, click the Manage Data Model icon on the Data tab, which opens the Power Pivot window. Click Diagram View in the top-right of the Home tab. You should see the two original tables with the Sectors table in the middle. Hover over either arrow to confirm that the tables are linked by the Sector field. Use File, Close to close the Power Pivot window and return to Excel.

BUILD PIVOT TABLES

Normally, to create a pivot table from the Sales data, you would select one cell in your Sales table first and choose Insert, PivotTable. Because you have defined relationships, however, the collection of tables and relationships comprise a Data Model. So go to a blank section of your dashboard and choose Insert, PivotTable. Excel will default to using the workbooks data model as the source.

Build any number of pivot tables. In the figure below, two pivot tables are based on the Sales data while the orange pivot table is based on Quality data.

CREATE THE SLICER

Slicers

In order for the slicer to control pivot tables coming from both the Sales and Quality data, the slicer must be built based on the tiny Sectors table. Although you might normally create slicers using the Slicer icon on the PivotTable Analyze tab, you should switch to the Slicer icon on the Insert tab when using the Data Model.

From the Insert tab, choose Slicer. Excel opens the Existing Connections dialog box. Choose the second tab (called Data Model) and choose Tables In This Workbook Data Model. Click Open. Excel opens the Insert Slicers dialog. There’ll be two tabs: Active and All. Choose the tab for All and scroll down to the Sectors table. Be careful in this dialog. The Sector field is listed three times, but for the technique to work, you must select the Sector field from the Sectors table.

With the slicer selected, go to the Slicer tab in the ribbon. Choose Report Connections. Initially, the slicer isn’t connected to any pivot table. Choose each pivot table in the workbook. Click OK.

FORMAT THE SLICER

Slicers always start with a single column of items. While a slicer is selected, use the resize handles to change the shape. Use the Columns setting on the Slicer tab in the ribbon to show the slicer items in more columns.

When you select from the slicer, all the pivot table reports will be filtered by the slicer. For example, the figure below shows a report for only the Manufacturing sector.

To select multiple items from the slicer, turn on the Multi-Select feature using the icon with three checkmarks in the top of the slicer. Or, hold the Ctrl key while selecting additional items.

SF SAYS

To add more slicers, you’ll need a tiny joiner table and two relationships for each slicer.

You may also like

Survey: Cybersecurity Threats

Coaching for Success

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

  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

[xyz-ihs snippet=”Excel-Course”]

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.

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

  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)
  4. Select the Year and Month as the Pivot Table Fields, in the Insert Slicer Dialog box.
  5. 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:

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.

  1. Select your Slicer
  2. Go to Slicer Tools > Options > Buttons > Columns
  3. 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.

Using A Slicer In Excel

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.

  1. Select your Slicer.
  2. 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.

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.

  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)

Free Excel For Mac

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

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 Monthsas a Filter for the Slicer
  4. 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:

  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

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.


Slicers In Excel For Mac Free

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:

  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

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.