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.

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.

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.

Shimon Brathwaite

When you apply filters to a table, all the original data is still there; it’s just hidden from view. To remove the filters you added, simply click the icon of the red X over a funnel on the top right of each slicer. When you do, all the original data reappears in the table.

Shimon Brathwaite

Slicers can also be used to filter the data displayed on charts generated from the same table. To illustrate this, we’ll add a chart to the spreadsheet. Highlight the entire table, go to the Ribbon’s Insert tab, click the pie chart icon, and select the first 2D pie chart in the popup.

Shimon Brathwaite

Now, you will have a pie chart that shows all of the data presented in the table.

Shimon Brathwaite

Not very useful, is it? With so many different items displayed in the chart, the viewer is overwhelmed and can glean no insights from it.

Let’s filter this data using the slicers we created to show just 2020 accessory sales. The result is much easier to understand quickly:

Shimon Brathwaite

To delete the chart, right-click it and select Cut.

If you want to remove any slicer from your spreadsheet, first clear the filters on your slicers to restore the table back to normal. Then delete the slicers by right-clicking each one and selecting the Remove option.

Shimon Brathwaite

Remove all the slicers, leaving only the table you created initially.

How to use slicers with PivotTables

In addition to using slicers on regular tables, you can also use them on PivotTables. To begin, highlight your table of data, go to Insert in the Ribbon toolbar, and select PivotTable. Select OK on the popup.

Shimon Brathwaite

You will be directed to a new page with a blank PivotTable on the left and a PivotTable Fields sidebar on the right. To populate the information, first check the checkbox next to Sales (US Dollars) in the sidebar. This places it in the Values area at the lower right. Next, drag the Year item down to the columns area in the sidebar. Then drag Category and Product down to the Rows area — place Category first and Product second.

Shimon Brathwaite

These actions populate the PivotTable on the left. The sales data is grouped first by category, then by product, with each year’s data appearing in a separate column. (For more details about working with PivotTables, see our PivotTables tutorial.)

Now follow the same steps as before to add a slicer to this PivotTable. Click anywhere on the table, go to the Insert tab and select Slicer. Check all the checkboxes and select OK.

Four slicers appear on the sheet. You can now use the slicers to filter the data in the PivotTable just as you did the data in the regular table earlier.

Shimon Brathwaite

As always, you can click the X icon at the upper right of the slicer to remove its filtering.

How to use slicers across multiple PivotTables

Now, there is a unique feature that can be used when you have two or more PivotTables in an Excel workbook. Slicers can be used across multiple PivotTables as long as they are based on the same data set.

So let’s try this: return to your original worksheet, create a second PivotTable, and populate the data. In the real world, you likely wouldn’t want to create a second PivotTable that’s identical to the first one; PivotTables are generally used to focus on particular subsets of data. So when you create the second PivotTable for our demo, check the Sales (US Dollars) checkbox, then drag Category and Year to the Rows area.

Shimon Brathwaite

Then, return to the first PivotTable you created, which already has slicers. Select the Year slicer, navigate to the Slicer tab in the Ribbon, and select Report Connections. (Alternatively, you can right-click the Year slicer and select Report Connections from the menu.) In the popup that appears, check the checkmark next to the second PivotTable you created and hit OK.

Shimon Brathwaite

Now, any change that you make with the slicer in the first sheet will be transferred to the other sheet as well. Try it out by changing the Year slicer to include only 2018.

Shimon Brathwaite

In this way, you can have multiple sheets with different views and data visualizations that dynamically change and remain in sync with one another.

If you ever need to disconnect a slicer, simply select that slicer, open the Report Connections dialog box, and deselect the PivotTable that you want to disconnect.

Shimon Brathwaite

And that’s all you need to know to get started with slicers. Time to start slicing your data!

Further reading:

• Excel basics: Get started with tables
• How to use PivotTables and PivotCharts in Excel
• How (and why) to use conditional formatting in Excel
• Microsoft cheat sheets: Dive in Windows and Office apps