Slicers are very useful feature in excel which 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.
Slicers in Excel (Table of Contents)
- #1 – Slicer in Excel Table
- #2 – Slicer in Pivot Table
- #3 – Add a Slicer to two Pivot Tables
- #4 – Adjust Slicer to Fit your Window
What are the Slicers in Excel?
In our early article, we saw option of FILTER the data by clicking on the drop-down list of the required column. But Slicers will give you visual filter options to filter out the specific data.
Slicers can be added to two kinds of data in excel. If you are using excel 2010 you can add a slicer to only to your pivot table but if you are using excel 2013 and later version you can add slicers to both your pivot table and regular tables.
#1 – How to Add Slicer to Your Regular Excel Table?
Slicer can be added 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 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 Add 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 your daily workplace. But adding a slicer to your table make the report more user-friendly.
I have a large data table which 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 adding 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 problem 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 of my productivity. But I can do some alignment change to make it display as per my convenient.
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 About Slicers in Excel
- 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, add 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 –