How to use slicers in Excel

Spreadsheets’ greatest strength — the wealth of data they contain — also makes them nearly indecipherable at a glance. That’s why Microsoft provides numerous ways to filter, format, and highlight data in Excel.

In previous articles, we’ve explained how to use conditional formatting, tables, and PivotTables and PivotCharts to show the most important data in a spreadsheet. In this Excel tutorial, we’ll cover slicers.

What is an Excel slicer?

A slicer is an easy-to-use tool that lets you filter and dynamically change data based on your selected criteria. It’s a great tool for drilling down on information that you want to focus on. Once you’ve set up a slicer in an Excel worksheet, you (or anyone viewing the spreadsheet) can simply click buttons in the slicer to zero in on one or more particular groups of data within the larger data set.

In Excel, both tables and PivotTables include built-in filtering tools, but they can be a little clunky to use. Slicers offer a more user-friendly way to filter data, making them especially useful for spreadsheets you’re sharing with co-workers, executives, or clients.

Where can you use slicers in Excel?

You can apply slicers to any table or PivotTable in Excel. What’s more, you can create multiple slicers for the same table or PivotTable, so anyone viewing the sheet can see which subsets of data you want them to focus on, and then they can click on the slicer buttons to further home in on specific data.

You can also use slicers to filter the data in charts. And if you have more than one PivotTable based on the same data set, you can use the same slicer for all the PivotTables.

In this article, we will walk through how to create and format slicers, use them to filter data, and connect them to multiple PivotTables. We’ll give instructions for Excel for Windows, but the steps are very similar if you’re using Excel in macOS or on the web.

If you want to follow along with the demo, the sample data is below. Simply copy and paste it into a blank Excel file to get started.

Year	Category	Product	Sales (US Dollars)
2019	Clothing	Socks	80,000
2018	Accessories	Chains	50,000
2020	Accessories	Necklaces	40,000
2018	Equipment	Basketballs	30,000
2020	Equipment	Soccer Balls	20,000
2019	Clothing	Pants	30,000
2018	Equipment	Footballs	40,000
2018	Accessories	Rings	60,000
2019	Equipment	Soccer Balls	30,000
2018	Clothing	Underwear	30,000
2020	Equipment	Basketballs	50,000
2019	Accessories	Chains	80,000
2020	Clothing	Underwear	25,000
2020	Clothing	Socks	30,000
2018	Clothing	Hat	45,000
2018	Equipment	Soccer Balls	35,000
2017	Clothing	Socks	40,000
2020	Accessories	Rings	70,000
2019	Clothing	Shirts	30,000
2018	Clothing	Pants	30,000

How to create and format slicers

To begin, highlight the entire table. Then, in the Ribbon toolbar at the top of the screen, select Insert and then Table. On the popup that appears, make sure “My table has headers” is checked and select OK.

Shimon Brathwaite

Now that we have a table, simply click on any cell in the table and then select Insert > Slicer. The popup that appears lets you select which slicers you want to create, with each option corresponding to one of the headers in your table. In this case, select all the checkmarks and click OK.

Shimon Brathwaite

Four slicers appear on the sheet. You can spread them out on the page so they are easier to read.

Shimon Brathwaite

Notice that the buttons within each slicer reflect the data in the table. For instance, there are four different years that appear in various rows in column A. Those four years are represented as buttons in the Year slicer. Likewise, all the categories from column B appear in the Category slicer, and so on.

You can change each slicer’s colors to make it easier to differentiate among them or just for aesthetic reasons. To do so, click one of the slicers, click the Slicer tab on the Ribbon toolbar, and select a new color from gallery that appears. In our example, we’ll click the Category slicer and select the orange color scheme.

Shimon Brathwaite

As a final formatting task, change the colors of the remaining slicers to match the image below:

Shimon Brathwaite

How to filter data with slicers

Now we’ll demonstrate the power of slicers in a table. To begin, let’s filter the table to show only data related to 2018 equipment sales.

To do this, click 2018 in the year slicer. This will deselect 2017, 2019, and 2020, leaving only 2018 selected. Then in the Category slicer, click Equipment to deselect everything except Equipment.

The result? The table now shows only three rows, all of which contain equipment sales from 2018.