Generally, a slicer is something that lets you divide anything into parts or a chunk cut from the bigger piece. Whereas, in Excel, a Slicer means to cut out in technical terms and filter the data A a pivot chart or pivot table. Instead of using a pivot filter, you should use a slicer for your convenience. So, let’s dive in to find how to use slicers in excel.
Also, note that Slicers are visual filters. Excel Slicer tool is helpful in any way and you can use it whenever you want convenience. After all, a Slicer is the main thing when it comes to cutting out technical fields.
Before moving on to the main topic, let’s have a quick overview of Slicer components.
Slicer Components
Usually, a slicer shows the following components:
- A slicer header specifies the class of the items available in the slicer.
- An unselected filtering button shows that the item is not in the filter.
- A selected button shows that the item is in the filter.
- By selecting all items in the slicer, a Cleat Filter button helps remove the filter.
- Scrolling is enabled with a scroll bar when you have more than one item visible in the slicer.
- With resizing and border moving tools, you can modify the size and location of the slicer.
How to Add Slicer in Excel?
Here you will find some examples to understand how you can insert slicer in Excel.
Adding Slicer under Tables in Excel
Starting from two years old sales data, you can see in the table given below:
Follow the steps given below to see how the slicer is used in the table.
Step 1:
Click on the Insert tab and then choose the Table given in the menu under the Tables option.
Now, select all the data throughout A1 to E93 and add a table for these ranges. A new window will pop up with the name “Create Table” with all the selected ranges to add Table. Press the OK button.
Match your table with the figure given below.
Once you add a table, you can easily see a new tool used on the right side of the top ribbon pane under the Table Tools option named Table Design.
Step 2:
Open the Design tab and you will find a sequence of options under it. Choose the Insert Slicer button placed under the Tools option in the Design tab. That way you can add slicers to the table.
Step 3:
Click on the Insert Slicer button placed under the option Tools in the Design tab. After this step, you will see the Insert Slicer window. In this window, you will find all the available columns in the table and each one of them is ready to use as a slicer.
You can choose Country as a slicer and click the OK button after selection.
Step 4:
Under your Excel table, you will find a slicer added with country labels.
Step 5:
To filter the data, you are free to choose any country button. Suppose, you want to see the data linked with India (Country), all you need to do is just click on the India slicer button. For all the rows, a filter on the table is applied with India as a country.
That’s it. This is how you can use slicers in Excel.
Adding Slicer for Pivot Table Data
Let’s start working on the customer-wise sales data for 2018. The day-by-day data is given in the image below. In this data, you will see a collection of 100 rows. Here is the figure given below to understand.
Using Excel PivotTable, you need to slice this data.
Step 1:
Choose the Insert the tab on the top ribbon of the Excel sheet you are working on. Under the Tables option, you will see an option named PivotTable. Click on it to add pivot on the sheet you are working on.
Step 2:
Once you open the PivotTable, a new window will pop up with the name Create PivotTable. Select the data according to the Table and choose the location where you need to add the pivot.
Click the OK button.
Have a look at the image given below.
Step 3:
After pressing the OK button, the pivot table will be added to the chosen data ranges successfully. Now, it is time to select the columns that you want to see under the pivot layout. For instance:
1. Rows: Customer, Country
2. Values: Quantity, Sales, Margin
Step 4:
Right after creating a pivot table, you can find two new tabs working on the Excel ribbon named Analyze and Design. Now, click on the Analyze tab.
Step 5:
For analysis, you have multiple options readily available under this tab. Press the Insert Slicer button placed under the Filters option to add a slicer.
Step 6:
Once you click on the Insert Slicers button, you will find a new window that pops up with all the column names available on the basis of added slicers on the pivot. Check anyone to whom you want to use as a slicer. Press the OK button.
Here is the slicer added of customers to your pivot.
You can even choose the particular customers under the slicer of customers simultaneously. To apply the filters, you need to press the Ctrl button while clicking on the customers one by one.
Things to Consider About Slicer in Excel
Remember that Slicers can be applied easily on Tables, Pivots, or Charts as they are no more than vibrant filters.
Under slicer, more than one item can be selected. Press the Ctrl button while clicking the slicers one-by-one against which you want to slice data.
