Excel Slicer (Table of Contents)
What is Slicer in Excel?
Slicers in Excel is a tool that is used to filter the data as per our need by slicing off a portion of data from the created table using the Pivot Table option in Excel. To apply Slicer, first create a Pivot table in the form of a table which is available under the Insert menu option. Then, in the same Insert menu tab, select Slicer, which is available under the Filters section. In the slicer connection box, we will be able to see an Insert Slicer box with all the available data headers used in creating that Pivot Chart. Now, if we select any or multiple fields, we will be able to get a slicer box on the screen. There click on any data to filter the table.
How to Insert Slicer in Excel?
Let’s understand how to Insert Slicer in Excel with some examples.
Example #1 – How to Insert Slicer under Tables in Excel
Suppose we have data of sales for the past two years as shown below:
We will see how the slicers can be added for this data. Follow the steps given below:
Step 1: Click on Insert tab > select Table under the Tables option menu.
- Select all the data across A1 to E93 and insert a table for these ranges. It will pop up a new window called Create Table with all the ranges we have selected to insert Table. Click on the OK button.
Your table should look like the one shown in the screenshot below.
As soon as you insert a table, you’ll be able to see a new tool added on the right-hand side of the upper ribbon pane under the Table Tools option named as Table Design.
Step 2: Click on the Design tab, and you’ll be able to see a series of options available under it. Click on the Insert Slicer button under the Tools section inside the Design tab. It will allow you to add slicers to the table.
Step 3: You click on the Insert Slicer button under the Tools option inside the Design tab, you’ll see an Insert Slicer window. Inside it, you can have all the columns present in the table and can use any of them as a slicer. I will choose Country as a slicer option and see what happens. Click the OK button after selecting the Country as a slicer.
Step 4: You can see a slicer added under your excel table with all country labels.
Step 5: You can use each country button to filter the data. For example, If I Want to see the data associated with India as a country, all I need to do is just click on the India slicer button and see the magic. It will apply a filter on the table for all the rows with India as a country. See the screenshot below.
This is how we can apply the slicer to the excel table.
Example #2 – How to Insert Slicers for Pivot Table Data
Suppose we have data of customer-wise sales for 2018 day by day, as shown in the screenshot below. This data accumulates 100 rows. See the partial screenshot below for your reference.
We would like to slice and dice this data first with the help of Excel PivotTable.
Step 1: Click on the Insert tab placed on the upper ribbon of the active excel sheet. You will find out an option called PivotTable under the Tables section. Click on it to insert pivot on the current working sheet.
Step 2: As soon as you click on the PivotTable button under the Tables section, a new window named Create PivotTable pops up. Select all the data as a Table/Range and select the location where you want the pivot to be added. Click OK once done. Please see the screenshot below.
Step 3: Once you hit OK, the pivot table will successfully be added to the selected data ranges. Now you can choose the columns which you wanted to see under pivot layout. I will choose the following layout option.
- Rows: Customer, Country
- Values: Quantity, Sales, Margin
Please see the screenshot given below.
Now, we need to add the slicers for this pivot table. Let’s see how we can do that.
Step 4: As soon as you create a pivot table, you’ll see two new tabs active on the excel ribbon, namely Analyze and Design. Click on Analyze tab out of those.
Step 5: There are different options available for analysis under this tab. Click on the Insert Slicer button present under the Filters section in Analyze tab to insert a slicer.
Step 6: As soon as we click on the Insert Slicers button, a new window pops up, which has all the column names present based on which you can insert slicers on your pivot. Tick the one which you want to add as a slicer. I would like to add the Customer column under slicers and hit the OK button. See the screenshot below.
You can see a slicer of customers being added to your pivot. See the screenshot below.
I can select the specific customer under slicer or multiple customers at the same time (hold the CTRL button and click on the customers one by one you want) apply the filters on the pivot table.
This is how we can insert slicers under PivotTable. This is the end of this article. Let’s wrap things up with some points to be remembered.
Things to Remember About Slicer in Excel
- Slicers are nothing more than dynamic filters, which can be applied on Tables, Pivots or PivotCharts.
- You can select more than one item under slicers. Hold the CTRL button and click one by one the slicers against which you want to slice data against.
This is a guide to Slicer in Excel. Here we discuss How to Insert Slicer in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –