What do you Mean by Slicers in Excel?
Slicers is a very useful feature in excel that is used to use multiple auto filters in a data table, if a user has to use filter on every column to find a date then it involves a lot of clicks while inserting a slicer makes it easier for the user as it can be done by few clicks, slicers are available in the insert tab in the filters option.
#1 How to Insert Slicer to Your Regular Excel Table?
The slicer can be inserted to your excel tables as well. Now look at the below normal data range, you will not see any kind of Slicer option for this normal data range.
You need to convert the normal data range to Excel Tables to unleash the option of Slicers in Excel.
- Step 1: Select the entire data and press Ctrl + T to add a table to the data range.
- Step 2: Click on OK it will create a table for you.
- Step 3: As soon as the table has been created you will see a new tab in the ribbon called Design, under this tab you can see Slicers option (Only from excel 2013 onwards).
- Step 4: Select the option of Insert Slicer. It will show you all the available headings in the table.
- Step 5: Select the required column you want to filter out the data quite often.
Note: You can select all the headings as well. But I have selected the COUNTRY column heading only.
This is the slicer I have inserted for the heading Country. It will list out all the unique values from the list. If you click on a specific country data table will display only the selected country’s data.
Now I have selected the country name Canada and data table showing only Canada country’s data.
#2 How to Insert a Slicer to Your Pivot Table?
I am sure you are smiling at the beautiful option of Slicers and its cool features. Don’t be overwhelmed by the cool feature of Slicer because I have a lot more in it to reveal many things. Note: Download the workbook to follow me.
When we apply the pivot table which has many fields it often failed to convey the correct message due to too many fields in it. I am sure you have also experienced this in your daily work. But inserting a slicer to your table makes the report more user-friendly.
I have a large data table that includes as many as 10 headings to it and has more than 700 rows of data.
I want to get a summary of this large data in terms of its gross sale. I have applied the pivot table to summarize this large data.
I want to see the summary report in terms of Country-wise, Product-wise, Year-wise, and Segment-wise.
Pivot Table showing me the summarized report, because data has many fields report is not user-friendly as of now. By inserting slicer we can create user-friendly filter options.
- Step 1: Place a cursor inside the pivot table. Remove all the fields except Country and YEAR.
- Step 2: Go to Analyze > Insert Slicer
- Step 3: Once the slicer is selected it will show all the headings. Select Segment and Product.
- Step 4: Now you will see the slicers for these two selected headings.
Now the report is showing the summary of all the segments and for all the products. If you wish to filter out the report only for the Segment Government and for the Product Carretera, you can select the options from the slicers. Pivot table started to show the report only for the applied filters. Only GOVERNMENT & CARRETERA has been highlighted in the Slicer.
#3 How to Add Slicer for Two Pivot Tables
We can apply the same slicer for two pivot tables. Now take a look at the below image where I have two pivot tables.
First pivot table showing the report Segment-wise and second pivot table showing the report Country-wise. I need to analyze Month-wise for both the pivot tables. I will insert Slicer for one first pivot table as of now.
In the above image, I have selected February month as the filter and first pivot table showing the result only for the month of February. However, a second pivot table is still showing the overall report for all the months.
In order to link the Slicer with both the pivot tables right-click on Slicer and select the option called Report Connections.
Once you click on Report Connections it will show you the list of off same data table’s all the pivot tables in the workbook. Select the number of pivot tables you need to link to this Slicer. In this case only two pivot tables.
Now go back and select the month name you to display the report.
Now both the pivot tables are showing the report for the month of February only.
#4 How to Adjust Slicer to Fit your Window?
One of the common problems in SLICER is a spacing problem. Now, look at the below slicer table for MONTHS.
This Slicer showing only the first six months in the present display. If I want to choose reaming months I need to scroll down and select and will take some time off my productivity. But I can do some alignment change to make it display as per my convenience.
- Step 1: Select the Slicer and go to Options.
- Step 2: Under this option go to Columns and make it 2.
Now it will show the result in two columns instead of one column. This will be much easier than the first one to select the months.
Things to Remember
- In the case of linking pivot tables, you need to aware of pivot table names. It is always a better technique to give names to your pivot tables.
- We can control the dashboards through Slicers.
- Slicer and Pivot Tables are interdependent.
This has been a guide to Slicers in Excel. Here we discuss how to create, insert and use Slicers in Excel Table, Pivot Table and how to adjust Slicers to Fit Window along with practical examples and downloadable excel template. You may learn more about excel from the following articles –