Slicers in Excel

What Are Slicers In Excel?

Filtering the data in Excel using an auto filter is one of Excel’s most commonly used tools. However, Excel introduced a visual filter called Slicer in the 2010 version. Slicers in Excel help us to filter the data from a visual filter for pivot tables, pivot charts, and Excel Table formats. Unlike auto filters, slicers will display the values in their list. However, users must click on the value when filtering from the pivot table or chart.

Once the slicer is selected, we can customize it to change the color, size, and number of columns to show in the slicer, along with many other custom formatting options. For instance, consider the following pivot table showing the pricelist of eatables. But, first, let us understand how slicers in Excel work.

Slicers in Excel Intro

We can see two slicers for columns Region and City, which help us filter the data for the selected region and city. We have filtered the East region from the Region slicer and the ‘New York’ as the City slicer. The pivot table shows the summary for the selected region and city.

Likewise, we can filter data using slicers in Excel.

Key Takeaways
  • Slicers allow users to filter the data with a visual selection.
  • It can filter the other slicer, so the drill-down is dynamic.
  • If the slicer is inserted for one pivot table by default, it will filter only that particular pivot table summary. However, by changing the report connections, we can link one slicer to impact multiple pivot tables.
  • We cannot hide any items from the slicers.
  • Slicer and pivot tables are interdependent. If we filter one value in the pivot table, it will also filter the same value in the slicer.

How To Use/Insert Slicer In Excel?

Now, we know that slicers in excel are used to filter data. So, let us learn how to use slicers in excel with the following example.

Let us consider the following data.

How to Use

We need to format and convert the above data into an Excel Table using the following steps:

Step 1: Select any of the cells in the data range.

Step 2: Press the Excel table shortcut keys, Ctrl + T.

Step 3: The Create Table window pops up. Enter the data in Where is the data for your table? Box.

 How to Use.1

Step 4: Click OK.

We can see that the data is converted into Excel table format.

Slicers in Excel How to Use.2

Once the data is converted into Excel table format, the Table Design tab appears in the Excel ribbon.

Slicers in Excel How to Use.3

Under the Table Design tab, click on the Insert Slicer option.

Slicers in Excel How to Use.4

The Insert Slicer window with the list of all the available columns in the Excel table pops up.

How to Use.5

Please select the columns for which we need to insert the slicer.

How to Use.6

After selecting the desired columns, click OK.

Immediately, we can see slicers for the selected columns.

How to Use.7

Now click on any of the cities from the city slicer, and the selected city data will be filtered in the Excel table.

How to Use.8

Another thing about the slicer is that when we select a particular city, it will filter the other slicer as well, i.e., Region Slicer.

How to Use.9

As we can see, we can select only the ‘East’ region from the region slicer, and the other part, ‘West,’ is hidden.

Likewise, we can filter data using slicers in Excel.

Slicer In Excel Examples

Example 1: Insert Slicers For Pivot Tables

We use slicers when we create a pivot table to summarize the data. So, let us take the same data from the above example to build the pivot table summary report.

Now, we will use the data already converted into Excel table format.

Slicers in Excel How to Use.2

Using the above data, insert a pivot table to show the monthly sales summary, as shown in the following image.

Slicers in Excel Example 1

We have the monthly sales summary. After inserting the pivot table, we can insert slicers to slice the data based on the selection we make from the slicer.

Why Slicers: The real question is why we need slicers when we can drag and drop fields to see the summary. For instance, if we want to see the product-wise sales based on month, then we can use the product column in the columns section of the pivot table.

Slicers in Excel Example 1.1

The pivot table has become wide and hard to read. Now, if we need to analyze based on the city, we can include the city column in the pivot table.

Example 1.2

We can see that increase in the number of fields makes it more complex to understand.

So, to avoid the difficulty, we should use slicers to allow the user to slice the data part by part.

To insert a slicer for the pivot table, follow the steps listed here:

Step 1: Select any of the cells in the pivot table area.

Step 2: As soon as we select the cell in the pivot table range, both the Pivot Table Analyze and Design tabs appear in the ribbon.

Example 1.1

From the pivot table analyze tab, click on the Insert Slicer option.

Slicers in Excel Example 1.3

Step 3: It will bring the insert slicer window with all the dimensions from the table (columns). We can select one or more dimensions to play around with the pivot table.

For instance, only the city slicer will be inserted if we select City.

Similarly, if we select both city and product, we will get the slicers for both columns.

Example 1.3-1

Step 4: Click OK, and we will get a slicer for City and Product columns.

Example 1.4

Slicers automatically give only the unique values from both columns and list them in the slicer box.

Working With Slicers

From the inserted slicer, we can filter data. For instance, if we want to see the sales for the produce Carrot, then click on this product in the product slicer.

Example 1.5

We can see that only the selected product gets a different color than other products. Now, we can see the monthly sales only for Carrot.

Now, assume we need to get the sales for one city, i.e., New York.

So, select this city from the city slicer.

Example 1.6

Now, we are seeing monthly sales for Carrot and New York.

If we want to select multiple cities or products, hold the control key and click on the items we want to slice.

Example 1.7

Now we can see the monthly products, Bran and Carrot, for the cities of New York and San Diego.

If we want to remove the slicer selection, click on the filter icon with a red cross mark at the top right corner of the slicer.

Example 1.8

Alternatively, we can use the Excel shortcut keys ALT + C to remove selections from the slicer. Select the slicer, hold the alt key, and press the C key.

When we want to deal with multiple pivot tables, we may insert slicers for each. For instance, look at the following two pivot tables.

Example 2

We have two pivot tables, Pivot Table 1 and Pivot Table 2.

For Pivot Table 1, let us add slicers in excel for the city column.

Similarly, for Pivot Table 2, let us add slicers in excel for the Category column.

To check the functionality, let us first select any categories from Pivot Table 1 slicer.

Example 2.1

Though we have selected the Bars category, it filtered only Pivot Table 2 and not Pivot Table 1. Similarly, when we select the city from the city slicer, it will filter only Pivot Table 1 and not Pivot Table 2.

 Example 2.2

Though the two slicers are inserted for two different pivot tables, they do not have any connection with the other pivot table.

When we insert the slicers for two different pivot tables by default, it does not have any connection between pivot tables. Therefore, we need to build a connection between the two tables.

Right-click on the city slicer and click on Report connections… option.

Example 2.3

It will connect the slicer’s report with the pivot tables in the Report Connections (City) window.

Slicers in Excel Example 2.4

As we can see, the city slicer only connects with Pivot Table 1, so check the box of Pivot Table 2 to link the city slicer to Pivot Table 2.

Example 2.5

Click OK.

Now, the city slicer can slice Pivot Table 2 as well.

Example 2.6

Similarly, we need to build a connection from the Category slicer to Pivot Table 1.

Right-click on the category slicer and click on the Report Connections… option.

Example 2.7

Now, link a connection to Pivot Table 1.

Example 2.8

Click OK.

Now, both the slicers connect with the pivot tables, and one slicer will filter both tables and slicers.

Example 2.9

We have selected Crackers from the category slicer, and it has filtered both the pivot tables.

How To Edit Slicer In Excel?

We can edit the slicers to make them look beautiful. But, first, we need to follow the below steps to format the slicer.

1. Remove Header From The Slicer

Adjusting the slicer position in the dashboard is very important because we always end up without having enough space to fit the slicer in the dashboard. In those cases, we can remove the slicer header.

For instance, look at the following slicer.

Slicers in Excel Edit 1

We have a product header here. To remove it, right-click the slicer and click on Slicer Settings… option.

Edit 1-1

In the slicer settings window, uncheck the Display Header check box.

Edit 1-2

Click on OK, and we will no longer see the slicer header.

Slicers in Excel Edit 1-3

The problem is we cannot clear filters while removing headers.

2. Change Columns And Rows

We will have to scroll down the slicer when many values are filtered. For instance, in the following slicer, we need to scroll down to see other products.

Slicers in Excel Edit 2

To avoid scrolling down, we can split the data into multiple columns. To do that, select the slicer. Then, the Slicer tab in the Excel ribbon appears.

Under this tab, change the columns to 3.

 Edit 2-1

It will change the slicer to 3 columns, and all the rows will be distributed equally.

Edit 2-2

Now users can easily make the selections without any trouble.

3. Hide Values With No Data In The Slicer Box

When we work with multiple slicers, one slicer filters the other, so some items may not have data when we select one slicer.

For instance, look at the following two slicers.

Slicers in Excel Edit 3

We selected ‘West’ and filtered the product slicer in the region slicer. However, not all the products have the data for the ‘West’ region.

As we can see, ‘Banana’ and ‘Pretzels’ are shaded and not allowed to select. So, instead of showing no data, we can hide the items in such situations.

Right-click on the product slicer and click on Slicer Settings… option.

Slicers in Excel Edit 3-1

Check the Hide items with no data box in the slicer settings window.

Edit 3-2

Click OK, and we no longer see items without data.

Edit 3-3

4. Insert Slicer From Pivot Table Fields

Inserting slicers for the pivot table is done via the PivotTable Analyze tab in the ribbon.

Slicers in Excel Example 1.3

The above method will bring us all the columns from the table. However, if the list of columns is huge, it won’t be easy to find the particular column to insert the slicer.

Instead, we can right-click on the desired column from the pivot table fields and click on Add as a slicer.

Edit 4

Now the selected column will be added as a slicer.

5. Change The Slicer Styles

We can change or design the style of the slicer. Once the slicer is inserted, a new tab called the Slicer appears. It helps us format the slicer.

Edit 5

From the list of available styles, we can choose any one of the styles.

Slicers in Excel Edit 5-1

If we want to build a new style, we can click on New Slicer Style… option.

Slicers in Excel Edit 5-2

By using this option, we can build a new slicer style.

6. Align The Slicers

When we insert multiple slicers, it is important to align them to make them look clean and clear. For instance, look at the following image.

Edit 6

We have three slicers, but they are not aligned properly. To align them properly, select all three slicers using the control key.

Edit 6-1

Now go to the Slicer tab under the Arrange category.

 Edit 6-2

It will align all the selected slicers on top.

Slicers in Excel Edit 6-3

Like this, we can do the alignment of the slicers.

Excel Slicer Not Working

If the slicer is not working, there are two possible reasons.

  1. File Saved in XLS Format: If the file we are working on in Excel is saved as an XLS file extension, then our slicers might not work properly.
  2. Excel File in Compatibility Mode: If the Excel file is in compatibility mode, slicers may not work.

Important Things To Note

  • Slicers are available from Excel 2010 versions only.
  • They can be inserted only when the pivot table is inserted or if the data table is converted to Excel Table format.
  • One slicer can contain one column of information only. However, users can modify this to their needs by increasing the number of columns.
  • ALT + C are the Excel shortcut keys to remove all the selected values from the slicer. To apply this shortcut key, we first need to select the slicer.

Frequently Asked Questions (FAQs)

What is slicers in Excel?

Slicers in Excel help filter data in pivot tables and charts.

For example, we have the following pivot table in Excel.

The pivot table shows the summary based on the category. Further, we can insert a slicer for the city column to filter the data based on the city.

Go to the PivotTable Analyze tab by selecting any of the cells in the pivot table and clicking on the Insert Slicer option.

Slicers in Excel Example 1.3

It will bring the list of columns with the data set or table. Choose the desired column (City) and then click OK.

FAQ 1.1

Now we have the slicer for the city column.

FAQ 1.2

Click on any city and pivot tables to show the summary only for the selected city.

FAQ 1.3

For instance, in the above city slicer, we have selected the city of Boston. As a result, we can see that the pivot table shows numbers only for the city of Boston.

How to connect two slicers in Excel?

While working with two slicers from two pivot tables, we must create a report connection between pivot tables.

Right-click on the slicer we want to connect and click on the Report Connections… option.

Example 2.3

It will show us the list of pivot tables connected and not connected to the slicer.

Slicers in Excel Example 2.4

Check the boxes of the pivot table connections we need to connect.

How to lock the slicer in Excel?

To lock the slicer, right-click on the slicer and click on Size and Properties.

Slicers in Excel FAQ 3

The Format Slicer window appears on the right side of the Excel screen.

FAQ 3-1

Click on Properties and check the Locked box.

FAQ 3-2

How to hide the slicer in Excel?

To hide any of the slicers, go to the Home tab. Then, under the Editing group, click on the Find & Select option and select the Selection Pane… option.

Slicers in Excel FAQ 4

We can see the list of slicers we have created in excel.

If we want to hide any slicers, click on the eye icon.

Slicers in Excel FAQ 4.2

It will hide the Region slicer from the workbook.

Download Template

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

This has been a guide to What are Slicers in Excel. Here, we explain how to create, use and edit slicers with examples and a downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *