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.

YearCategoryProductSales (US Dollars)2019ClothingSocks80,0002018AccessoriesChains50,0002020AccessoriesNecklaces40,0002018EquipmentBasketballs30,0002020EquipmentSoccer Balls20,0002019ClothingPants30,0002018EquipmentFootballs40,0002018AccessoriesRings60,0002019EquipmentSoccer Balls30,0002018ClothingUnderwear30,0002020EquipmentBasketballs50,0002019AccessoriesChains80,0002020ClothingUnderwear25,0002020ClothingSocks30,0002018ClothingHat45,0002018EquipmentSoccer Balls35,0002017ClothingSocks40,0002020AccessoriesRings70,0002019ClothingShirts30,0002018ClothingPants30,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.




Convert the data into table format.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>


Convert the data into table format.
Shimon Brathwaite

Convert the data into table format.
Shimon Brathwaite

aria-hidden='true'>
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.




Check the box next to each slicer you want to create.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>


Check the box next to each slicer you want to create.
Shimon Brathwaite

Check the box next to each slicer you want to create.
Shimon Brathwaite

aria-hidden='true'>
Shimon Brathwaite

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




Drag and drop the slicers so that each is fully visible.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>


Drag and drop the slicers so that each is fully visible.
Shimon Brathwaite

Drag and drop the slicers so that each is fully visible.
Shimon Brathwaite

aria-hidden='true'>
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.




You can format slicers using the Slicer tab in the Ribbon toolbar.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>


You can format slicers using the Slicer tab in the Ribbon toolbar.
Shimon Brathwaite

You can format slicers using the Slicer tab in the Ribbon toolbar.
Shimon Brathwaite

aria-hidden='true'>
Shimon Brathwaite

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




The table data has been filtered according to our selections, showing 2018 equipment sales only.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>


The table data has been filtered according to our selections, showing 2018 equipment sales only.
Shimon Brathwaite

The table data has been filtered according to our selections, showing 2018 equipment sales only.
Shimon Brathwaite

aria-hidden='true'>
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.




The table data has been filtered according to our selections, showing 2018 equipment sales only.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>


The table data has been filtered according to our selections, showing 2018 equipment sales only.
Shimon Brathwaite

The table data has been filtered according to our selections, showing 2018 equipment sales only.
Shimon Brathwaite

aria-hidden='true'>
Shimon Brathwaite

If you want to show more data in the table, you can select multiple items within a slicer. For example, in the Category slicer, click Clothing, hold down the Ctrl key in Windows or the ⌘ key on a Mac, and then click Equipment. With both of those items selected, our example table now shows all clothing and equipment sales for 2018.




The table now shows clothing and equipment sales from 2018.

class='close-button' data-wp-on--click='actions.core.image.hideLightbox'>