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.
Convert the data into table format.
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.
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.
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.
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.
Shimon Brathwaite
