How To Use Slicers In Excel

When it comes to data analysis, Excel is one of the most popular software programs available. And for good reason – it’s relatively easy to use and can be extremely helpful in a variety of different settings. However, if you really want to get the most out of your Excel usage, it’s important to learn about all of the features that the program has to offer. One of the most helpful features that you may not be aware of is called a slicer.

What Is A Slicer?

So, what exactly is a slicer? In short, slicers are interactive filters that allow you to quickly and easily filter data in Excel. They’re incredibly easy to use. Simply click on the slicer element that you want to filter by and your data will be filtered accordingly.

Best of all, slicers can be used on any type of data, whether it’s numerical or categorical. You can create slicers from tables or Pivot Tables in Excel.

Benefits Of Using A Slicer

Several benefits come from using slicers in Excel.

First, they can save you a lot of time when filtering data. Rather than having to manually filter your data each time you want to look at a certain subset, you can simply use a slicer and have the work done for you.

Another benefit of using slicers is that they make it easy to share your data with others. Rather than sending someone an entire spreadsheet full of data, you can simply send them a file with the slicers already set up. This way, they can easily filter the data themselves without having to wade through everything.

Creating A Slicer from a Table

To create a slicer in Excel, you first need to have some data that you want to filter. Refer to the sample data in the How To Use Slicers In Excel.xlsx file. From there, you’ll be able to choose which element you want to use as your slicer.

Steps To Create A Slicer

Open the How To Use Slicers In Excel.xlsx file, Table. Click any cell within the Excel table.

On the ribbon, click the Insert tab, Filters group, and click the Slicer button.

The Insert Slicers dialog opens. From here, you’ll be able to choose which element you want to use as your slicer. Select the Year, Product, and Sales elements, then click OK.

If the slicers are overlapping each other, use click and drag to separate them on the same row.

You can now use the slicers to filter the table data; Excel filters the data in place.

Selecting Individual Items In A Slice

In our sample data, we have the sales figures from a grocery store. We can use slicers to filter out individual items within each slicer.

1. We want to see the apples sold in 2018:
   1. In the Year slicer, click 2018.
   2. In the Product slicer, click Apples.
   3. Excel filters the table to the relevant cells.
   4. Whenever you select a field within a slicer, the Clear Filter icon becomes active.

2. Click the Clear Filter button in the Year and Product
   1. Excel displays all records in the table.
   2. Click the Sales figure 261. Excel displays the record for that sales figure only.
   3. Including the data field (sales in this example) in slicers is not very useful. Clicking on data fields will only return one record. A better practice is to create a slicer of all fields except the data field.
   4. On the Sales slicer click Clear Filter

Editing Slicers

Once you’ve created your slicer, it’s important to remember that you can always go back and edit it if necessary. For example, if you want to change the fields you originally selected. This will allow you to make changes to the way that your slicer filters data.

1. Let’s remove the Sales slicer:
   1. Click the Sales slicer, the border gets darker around it.
   2. Press the Delete key, Excel removes the slicer. Note you can also right-click on a slicer to access the Remove “Sales” option.
   3. Now we can create a slicer out of another field.
   4. Click any cell in the table.
   5. On the ribbon, click Insert, Slicer.
   6. The Insert Slicers dialog appears, click Category, then OK.
   7. Move the Category slicer in line with the others.
2. We want to see the fruit sold in 2018:
   1. In the Year slicer, click 2018.
   2. In the Category slicer, click Fruit.
   3. Excel filters the table to the relevant cells.
   4. Notice that Excel displays all the products that fall under the category of fruit in the product slicers.
   5. Click the Clear Filter button on the Year and Category slicers.

Using Multi-Select In A Slice

You can select more than one item within a slicer element.

Let’s filter the table to all the veggies sales in 2018 and 2021.

In the Year slicer, click the Multi-Select button; the button is yellow when active.

Click years 2018 and 2021. Note selected items appear as white within the elements. You can use the Multi-Select option on more than one slicer at the same time.

Click Veggies in the Category element.

Click the Multi-select option to deactivate it; the button is now white.

Clearing Slicer Filters

You can clear filters in a couple of ways:

1. On each slicer click the Clear Filter button, this is useful if only one filter is active. ALT+C is the keyboard shortcut for this feature.
2. Click the Clear button on the Data tab. To use this option, your cursor must first be in the table if you wish to clear filters on multiple slicers.
   1. Click any cell in the table.
   2. On the ribbon go to the Data tab, Sort & Filter group, click Clear.

Refreshing The Data Source

Slicers expand as you add data; the data refreshes and updates automatically.

Let’s add some data for 2022.

1. Click in B28, type 2022, press tab. Notice the year appears in the Year slicer.
2. In C28 press f, Fruit will appear.
3. In D28 type Jackfruit.
4. In E28 enter 424.
5. In the Product element, click Jackfruit.
6. The new entries appear in the slicers automatically.

Using The Slicer Ribbon

The Slicer tab appears whenever your cursor is on a slicer. The options here allow you to change settings, formats, arrangement, and sizing.

Some useful features on this ribbon are:

• Under Slicer settings
  • You can choose to hide records that have no data. This is useful if you have a table with blank cells.
• Buttons group
  • Buttons defaut to 1 column, but for longer lists, it may be useful to display several columns so users don’t have to scroll down to see additional items.

And two columns slicer:

Take the time to review the options on the Slicer ribbon to see what other features may be useful to you.

Creating Slicers on a PivotTable

You can also create slicers on PivotTables; there are two ways to do this.

Adding Slicers to a PivotTable – Ribbon

Go to the Pivot tab of the worksheet. Click anywhere on the PivotTable. On the ribbon, click PivotTable Analyze.

In the Filter group, click Insert Slicer. Select Product.

Adding Slicers to a PivotTable – PivotTable Fields

Click in the PivotTable, on the right side the PivotTable Fields pane appears.

Right-click on the Category element and click Add as slicer. The new slicer appears.

Note, if there is more than one PivotTable in a worksheet, you can choose which one of them to include in your slicers by selecting Report Connections on the Slicers tab.

Overall, using slicers in Excel can be a huge help when it comes to data analysis. They’re easy to use and can save you a lot of time and effort in the long run.