Skip to main content
Skip to main content
Microsoft 365

Interacting with Slicers

In part 2 of this 3 part series introducing slicers, I’ll show you some of the ways you can interact with slicers. Part 1 can be found here.

Slicers reveal where the data is

Let’s say I want to slice on my customer data for yesterday, and I only want to see a list of customers with Partial College and Bachelors education levels. As I click on these two tiles to select them, they turn blue.

As it turns out, yesterday all my customers were either Partial College or Partial High School. I had no customers that held Bachelors or Graduate Degrees, so these tiles are grayed out.

My slicer would look like this:


For comparison, if I had created this with a report filter instead of a slicer, it would’ve looked like this:


Slicers play well with each other


Slicers can cross-filter, which means they interact with each other to show you where the data is. Start with one slicer that shows the number of customers in different countries:


Add a marital status and children slicer. I’m interested in the single customers with one child, so I click on “Single” and “1”. The chart updates instantly.


You may have noticed that “Canada” in Country and “4” in Children become grayed out. Now you know there are no single customers with one child in Canada, and no single customers with 4 children anywhere. The grayed out tiles have also dropped to the bottom, out of your way. This is particularly useful for slicers with more items, so the data you care about is at the top.


Related, but slightly different from cross-filtering is auto-selecting. When slicers are part of the same hierarchy, they auto-select each other. Examples of hierarchies are Year/ Month/Day and Country/State/City. Let’s drop the marital status and children slicers, and replace them with state and city.

Here, I’ve selected United States. Notice the states and cities in the US automatically become selected.


I can also pick from the state or city slicer. Here, I’ve clicked on Darmstadt, and the slicers auto-select Hessen and Germany.


Sharing your slicers

Recall the example in which I’m interested in the data for single customers with one child. I’ve already got a PivotChart with the number of customers – but now I want a separate PivotChart with profit data for the same customers. In Excel 2007, I would have had to create separate report filters for each. And if I decided I was instead interested in married customers with no children, I would’ve needed to manually update my selection in each report filter.

Slicers overcome this limitation – you can connect your slicers to multiple PivotTables, PivotCharts, and/or CUBE functions, and any selections you make automatically update everything the slicer is connected to.


Now let’s suppose these two PivotCharts were on separate worksheets in the same workbook, and you wanted the slicers to appear in each worksheet. By simply copying and pasting, you create a cloned slicer. When you change what you’ve selected in one slicer, all cloned copies of that slicer also update instantly.

Working with CUBE functions

So you’ve seen how slicers interact with PivotTables and PivotCharts. Let’s look at an example of how they work with CUBE functions.


Here I’ve added three slicers to my spreadsheet. When I write my CUBE function, connecting to slicers is easy. Slicers expose whatever members are selected as an Excel named range. Since named ranges appear in the formula auto complete dropdown, you can see what slicers you can connect to right in the auto complete dropdown.

Next time, I’ll show you how you can format slicers to make great looking reports.

You may also like these articles

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…