Excel Slicer (Table of Contents)
What is Slicer in Excel?
Slicers in Excel is a tool which is used to filter the data as per our need by slicing off a portion of data from the created table using 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, 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 in 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 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 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 Table Tools option named as Table Design.
Step 2: Click on Design tab and you’ll be able to see a series of options available under it. Click on Insert Slicer button under Tools section inside the Design tab. It will allow you to add slicers on the table.
Step 3: You click on the Insert Slicer button under 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 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 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 slicer to excel table.
Example #2 – How to Insert Slicers for Pivot Table Data
Suppose we have a 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 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 then 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 on 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 Insert Slicer button present under the Filters section in Analyze tab to insert a slicer.
Step 6: As soon as we click on Insert Slicers button, a new window pops up which have 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 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 the things up with some points to be remembered.
Things to Remember About Slicer in Excel
- Slicers are nothing more than the 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 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 downloadable excel template. You can also go through our other suggested articles –