FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Slicers In Excel

Updated on January 2, 2024
Article byJeevan A Y
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Do You Mean By Slicers In Excel?

Slicers are a useful Excel feature that uses multiple auto filters in a data table. However, if a user has to use the filter on every column to find a date, it involves many clicks, while inserting a slicer makes it easier for the user as few clicks can do it. Slicers are available in the “Insert” tab in the “Filters” option.

For example, consider the below table showing highest marks obtained by students in three semesters. Now, let us learn how to insert slicers and filter data easily.

Slicers in Excel Example - 1
  • Step 1: First, convert the data into a table. So, press Ctrl + T and  click on My Table has headers option. Then, click OK.
  • Step 2: Next, we will be able to see Insert Slicer option under the Table Design tab.
  • Step 3: We can see the Insert Slicers window. We can select as many filters as we want and click OK. In this example, we have selected semesters.

Now, we can see semester window with 3 options – 1, 2 and 3. When we select 1, Excel will filter the table and show only semester 1 data as shown in the below image.

Slicers in Excel Example - 2

Likewise, we can use slicers in Excel.

Key Takeaways

  • The Slicers in Excel are used to filter the data effectively.
  • Remember, we can use slicers in Excel only when the data is converted into a table. We can convert data using the shortcut keys Ctrl +T.
  • We must select My Table has headers if our table has headers.
  • Using slicers in Excel, we can select multiple filters. To multiselect slicers in Excel, simply use the shortcut keys Alt + S or press the multiselect option in the slicers window.

How To Use Slicer In Excel?

#1 – How To Insert Slicer To Your Regular Excel Table?

We can also insert a slicer into the Excel tables. Now, look at the below normal data range. You will not see any slicer option for this normal data range.

It would help if you converted the normal data range to Excel tables to unleash the option of slicers in Excel.

Below are the steps to insert a slicer into the regular Excel table:

  1. First, select the entire data and press “Ctrl + T” to add a table to the data range.

    Slicers Example 1

  2. Click on “OK.” It will create a table for you.

    Slicers Example 1-2

  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 the “Slicers” option (only from Excel 2013 onwards).


    Slicers Example 1-3

  4. Select the option of “Insert Slicer.” It will show you all the available headings in the table.


    Slicers Example 1-4

  5. Select the required column you want to filter out the data quite often.

    Note: You can select all the headings as well. But we have chosen the “Country” column heading only.

    Slicers Example 1-5

    Slicers Example 1-6

    It is the slicer we have inserted for the heading “Country.” It will list out all the unique values from the list. Clicking on a specific country data table will display only the selected country’s data.

    We have selected the country name “Canada,” and the data table shows only Canada’s data.

    Slicers Example 1-7

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 – How To Insert A Slicer To Your Pivot Table?

We are sure you are smiling at slicers’ attractive options and cool features. However, do not be overwhelmed by the cool features of the slicer because we have a lot more in it to reveal many things. Note: download the workbook to follow here.

When we apply the PivotTable, which has many fields, it often fails to convey the correct message due to too many areas. We are sure you have also experienced this in your daily work. But inserting a slicer into the table makes the report more user-friendly.

We have a large data table with as many as 10 headings and more than 700 rows of data.

Slicers Example 2

We want a summary of this large data regarding its gross sale. Therefore, we have applied the PivotTable to summarize this large data.

We want to see the summary report in terms of country-wise, product-wise, year-wise, and segment-wise.

Slicers Example 2-1

PivotTable showed us the summarized report because the data has many fields, and the report is not user-friendly as of now. However, by inserting a slicer, we can create user-friendly filter options.

Slicers Example 2-2
  • Step 2: Go to “Analyze” → “Insert Slicer.”
Slicers Example 2-3
  • Step 3: Once the slicer is selected, it will show all the headings. Select “Segment” and “Product.”
Slicers Example 2-4
  • Step 4: Now, you will see the slicers for these two selected headings.
Slicers Example 2-5

The report shows the summary of all the segments and all the products. If you wish to filter out the report only for the segment “Government” and the product “Carretera,” you can select the options from the slicers. The PivotTable begins to show the report only for the applied filters. Only “Government” and “Carretera” have been highlighted in the slicer.

Slicers Example 2-6

#3 – How To Add Slicer For Two Pivot Tables

We can apply the same slicer for two PivotTables. For example, see the image below, where we have two PivotTables.

Slicers Example 3

First, the pivot table shows the report segment-wise, and the second table shows the report country-wise. We need to analyze month-wise for both the PivotTables. We will insert a slicer for one first PivotTable as of now.

Slicers Example 3-1
Example 3-2

In the above image, we have selected February month as the filter. Therefore, the first PivotTable shows the result only for February. However, a second pivot table still leads the overall report for all the months.

To link the slicer with the PivotTables, right-click on “Slicer” and select the “Report Connections” option.

Example 3-3

Once you click “Report Connections,” it will show you the list of the same data table’s all the PivotTables in the workbook. Next, select the number of PivotTables you need to link to this slicer. In this case, only two PivotTables.

Example 3-4

Now, return and select the month name you want to display in the report.

Example 3-5

Now, both the PivotTables are showing the report for 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.

Example 4

This slicer shows only the first six months in the present display. So, if we want to choose the remaining months, we need to scroll down and select, and it will take some time off my productivity. But we can make some alignment changes to display as per my convenience.

  • Step 1: Select the “Slicer” and go to “Options.”
Example 4-1
  • Step 2: Under this option, go to “Columns” and make it “2.”
Example 4-2

Under this option, go to “Columns” and make it “2.”

Example 4-3

Important Things To Note

  • In the case of linking PivotTables, we must be aware of PivotTable names. Therefore, it is always a better technique to give names to the PivotTables.
  • We can control the dashboards through slicers.
  • The slicer and PivotTables are interdependent.

Frequently Asked Questions (FAQs)

1. What is slicers in Excel?

Slicers in Excel is an option used to filter data with multiple filters. We can easily create slicers in Excel by converting the data into a table.

We can create slicers in regular Excel tables as well as in pivot tables.

2. How to multi-select slicers in Excel?

After we apply slicers, we can filter data using more than one slicer in Excel.
 
For example, consider the below table showing prices of fruits in 4 quarters, Q1, Q2, Q3, and Q4. Now, let us learn how to insert slicers and filter data using multiple slicers.

Slicers in Excel Example - FAQ 2

• Step 1: First, convert the data into a table. So, press Ctrl + T and  click on My Table has headers option. Then, click OK.
 
• Step 2: Next, we will be able to see Insert Slicer option under the Table Design tab.
 
• Step 3: We can see the Insert Slicers window. We can select as many filters as we want and click OK. In this example, we have selected product.
 
Now, we can see product window with 5 options – Apple, Banana, Mango, Pear, and Strawberry. When we select Apple, Excel will filter the table and show only data containing Apple as shown in the below image.
 
Now, to multiselect, we have to select the option highlighted in the below image or we can also use the shortcut keys – Alt + S.

FAQ 2 - Step 3a

Now, Excel will show all the data based on the selected slicers.

FAQ 2 - Step 3b

Likewise, we can select multiple slicers in Excel.

3. What are the steps to delete slicer in Excel?

We can delete a slicer in Excel in 2 ways:

We can select the slicers window and press Delete.
Alternatively, we can press Ctrl and select the slicers window and press Remove option to delete slicers in Excel.

Download Template

This article must help understand Slicers In Excel with its features and examples. You can download the template here to use it instantly.

You can download this Slicers Excel Template here – Slicers Excel Template

This article is a guide to Slicers in Excel. Here, we discuss how to create, insert, and use slicers in Excel table, PivotTable, and how to adjust slicers to fit windows, along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles: –