Excel Slicer

What is an Excel slicer?

Excel Slicer

Excel Slicers are powerful for applying filters for Excel tables, pivot tables, and pivot charts. They are widely used to create attractive dashboards and summary reports because it is effortless to create, is visually pleasing and the users can use them anytime anywhere to filter their data faster and easier.

Slicer’s feature was not available in the early versions of Excel. They were initially introduced in Excel 2010 and became available in Excel 2013 and all the later versions.

Difference between Excel slicers and PivotTable filters

Slicers and pivot table filters perform identical operations, i.e., display some data and hide others. The difference between Excel Slicers and Pivot Table filters is as follows:

S.NO.Excel SlicersPivot Table Filters
1.Excel slicers have made the filtering process very easy. In the pivot table applying a slicer filter is as effortless as clicking a button.Pivot table filters are a bit complicated when compared to slicers. Filter fields don’t specify any simple method to tell what is being filtered when the user selects two or more elements.
2.Slicers can be tied to multiple pivot tables present in the same Excel worksheet.Filters can be connected to one pivot table only.
3.Slicers are also termed floating objects as they can be moved anywhere in the Excel sheet, such as you can keep the slicer next to your pivot table, pivot chart or within the chart area.Pivot Filters have limited access and can only be applied to columns and rows.
4.Slicers are user-friendly and provide an interface that can perform impeccably in various touch screen environments. Though it is not fully supported for Excel mobiles such as Android and iOS.Pivot table filters don’t work correctly on touch screens.
5.Slicers take up more Excel space.Pivot table report filters are compact
6.Automating Slicers is tough and requires technical skills and efforts.Automating Pivot table filters is simple and can be done easily through VBA

Insert slicer in Excel

Slicers can be easily inserted into your Excel Tables, Pivot Tables, and Pivot Charts. Follow the below step-by-step implementation to add Slicer to your Excel worksheet.

Add slicer to your pivot table

For implementing slicer in your Excel, firstly you need to create a pivot table and follow the below given steps:

  1. Click on your Excel pivot table.
    Excel Slicer
  2. Go to Analyze tab-> Filter group, and click the Insert Slicer option.
    Excel Slicer
  3. The Insert Slicers window will be immediately displayed. It will show various checkboxes where each represents a field of your pivot table. You can select one or multiple fields for which you wish to apply the slicer.
    Excel Slicer
  4. Click OK.

Result:

As an example, we have added only one slicer to filter our pivot table, i.e., by Product:

We can filter the product fields and can visualize the data of any particular. For instance, in the below screenshot, we have fetched the data for the Air Purifier Product using Slicer.

Excel Slicer

Add slicer to your Excel table

In the earlier section, we discovered how to create a slicer for pivot tables but do you know the latest versions of Excel allows the user to insert a slicer for an Excel table as well. Follow the below steps:

  1. Create an Excel Table and click on it.
    Excel Slicer
  2. On the Insert tab, in the Filter group, click the Insert Slicer.
    Excel Slicer
  3. The Insert Slicers window box will be displayed, tick off the check boxes the columns that you wish to filter. Click OK.
    Excel Slicer

Add a slicer for pivot chart

In addition to the pivot table and Excel table, you can also insert Sliver to your Pivot chart and can apply a filter to them. Though you can also create a slicer for your pivot table by following the above guidelines and further can use it to filter both the pivot table and the pivot chart.

Excel Slicer

To insert a slicer with your pivot chart follow the below steps:

  1. Create a pivot chart and click anywhere on it.
  2. Click on the Analyze tab. Under the Filter group, choose the Insert Slicer option.
    Excel Slicer
  3. The Insert Slicers window will be immediately displayed. It will show various checkboxes where each represents a field of your pivot table. You can select one or multiple fields for which you wish to apply the slicer.
    Excel Slicer
  4. Click on OK

Result:

This will integrate the slicer window in your worksheet:

Please note that you can customize the appearance of the slicer box in your pivot chart. For example: you can locate the slicer window within your pivot chart area as shown below.

Excel Slicer

To incorporate the Slicer box inside the chart area, we need to increase the overall chart area and need to decrease the plot area by dragging the borders, and finally drag and drop the slicer window to the empty space:

How to Move a slicer in

You can easily move and position your Slicer to place in Excel within the same worksheet. Place your cursor on the top of the slicer unless the cursor changes to a four-headed arrow.

Excel Slicer

Using the arrow drag the slicer to your preferred position.

Resize a slicer

Unlike Excel Tables, pivot tables, and pivot charts, you can resize the size of Slicer as well. The most common way to change the size of the Slicer is by dragging the edges of the Slicer.

The second way is by changing the Position and Layout setting and setting it to the required height and width for your Slicer. Right-click on the top of your Slicer. The window will appear. Select the Size and Properties option.

Excel Slicer

Again the Size and Properties window will be displayed. From the side pane, choose the ‘Position and Layout’ option. Set the various fields as per your requirement.

Excel Slicer

That’s it! The slicer will be set to the specified position in your Excel worksheet.

Lock the slicer position in Excel

Sometimes we need to fix the place of a slicer in your Excel worksheet. To fix the Slicer, you need to lock it in Excel. Follow the below steps:

  1. Create your Excel Slicer and right-click on the top of it. The following window will appear. Choose the Size and Properties option.
    Excel Slicer
  2. The Size and Properties windowpane will be displayed. On the side pane, select the Properties option.
    Excel Slicer
  3. It will display the object positioning option; select the third radio option, ‘Don’t move or size with cells box’. Close the window pane.
    Excel Slicer

After doing this, the position of your Slicer will be locked. Even if you add or delete a new row or column or add more fields to your pivot table, the position of your Slicer will remain intact.

How to remove a slicer from a worksheet

There are two ways to delete a slicer permanently from your Excel worksheet.

Method 1

  1. Click on the top of your slicer to select it, and from your keyboard, press the Delete key.
    Excel Slicer
  2. Your slicer will be successfully deleted from your Excel worksheet.

Method 2

  1. Right-click on the top of your slicer. The following dialog box will appear. Choose the Remove ‘Slicer Name’ option.
    Excel Slicer
  2. Your slicer will be successfully deleted from your Excel worksheet.