Slicer is a built-in feature in Microsoft Excel that allows filtering data in tables in your spreadsheet. This feature is especially helpful when dealing with large data sets.

In this guide, we explain how to insert and delete a slicer, use multiple slicers, and change slicer settings in Microsoft Excel.

What Is a Slicer in Excel?

Slicers in Excel are a way to filter data in tables, pivot tables, and pivot charts. Slicers are a great way to quickly filter information in large data sets or create an interactive Excel dashboard.

Sure, you can use the Filter option to filter data in Excel. However, using slicers offers a more intuitive view because the slicers also indicate the current filtering state. This makes it easy to understand the information displayed in your sheet after applying filters.

How to Insert a Slicer in Microsoft Excel

Follow these steps to insert a slicer in Excel:

  • Click anywhere on the table containing the data you want to filter and go to the Table tab.
  • Click on Insert Slicer.
  • You'll see a pop-up with a list of fields in the table. Check the box next to the fields based on how you want to "slice" or filter your data and click OK.

    • For example, this is what our sheet looks like once we add slicers for the Month and Totals fields.

    Slicers have buttons, which act as the criteria for filtering your table. In our example, the months are the buttons for the Month slicer.

    How to Use a Slicer in Excel

    Here's how you can start using the slicers once you've added them to your worksheet.

    How to Use a Single Slicer

    Click on a button on the slicer to filter the data. To select multiple buttons, select one button and then click the Multi-Select button in the top-right corner of the slicer.

    If you click on Multi-select without any buttons already selected, it hides the fields you select.

    Alternatively, you can hold-press Ctrl (or Cmd on Mac) and click on the buttons you want to select.

    In our example, we've selected three months (January, February, and August). As you see below, the table then displays the total sales for all products only for these three months.

    How to Use Multiple Slicers

    Adding more than one slicer works the same way as adding more filters on a shopping site. It filters all data that doesn't meet the criteria you've used in the slicers.

    Once you select criteria from one slicer, you might see some buttons in other slicers turn gray. This is because selecting the gray buttons won't produce any results.

    For example, when we select January on our spreadsheet, only one option remains on the Total slicer. This is because none of the other months have the same total.

    On the other hand, you might see multiple buttons on the second slicer after selecting one button on the first slicer.

    For example, when we select 1450 in the Total slicer, the buttons for January, May, and December are available to select. This is because those three months have the same totals.

    You won't be able to search for specific buttons in the slicers. You can scroll down or resize the slicers in case of a long list.

    How to Clear Filters Applied by Slicers

    To clear the filters applied by the slicers, click on the cross in the top-right corner of the slicer from which you want to clear the filters.

    Clearing filters doesn't delete the slicers. You can reapply the filters with a single click as long as the slicers are on the worksheet.

    How to Delete a Slicer

    You can delete slicers using two methods. You can select the slicer and press Delete on your keyboard. Alternatively, you can right-click on the slicer and click Remove "NAME OF THE SLICER".

    How to Change Excel's Slicer Settings

    Slicers are dynamic and flexible. You can change their size, headers, data arrangement, position, and more.

    To access slicer settings, right-click on any slicer and go to Slicer Settings. Alternatively, you can click anywhere on the slicer, go to the Slicer tab, and select Slicer Settings.

    Here are the settings you can change for slicers:

  • Name: You can change the name of the slicer by editing this field.
  • Header: Check or uncheck the box against the Display header to show or hide the header on the slicer. Change the display caption (header) by editing the box next to Caption.
  • Item Sorting and Filtering: Select Ascending or Descending to set the order of items under your slicer.
  • Use Custom Lists when sorting: Selecting this option retains the order of the list in the worksheet. If left unchecked, the list in the slicer appears alphabetically.
  • Hide items with no data: Check this option to only view buttons for fields that have no data.
  • Visually indicate items with no data: Check this option if you want to see buttons for fields with no data.
  • Show items with no data last: Check this option to see fields with no data at the bottom of the slicer.
  • Bring Forwards and Send Backwards: If you're working with multiple slicers, and they're stacked, you can change their position with the Bring Forwards and Send Backwards options under the Slicer tab in the ribbon.

    • To move the slicer around, click on one of the edges of the slicer and slide it to where you want to place it. Click and hold any of the edges or corners of the slicer and drag to resize the slicer.

    Also, you can change the slicer style in case you have too many slicers on your sheet and want to color-code them. The styles appear in the ribbon at the top after you select a slicer.

    Filtering Data in Excel Is Made Easy With Slicers

    Slicers make it easy to understand large data sets by filtering them, simplifying data analysis. It does so without changing the original data, so you don't have to worry about losing any data during analysis.

    Slicers are just one of the many Microsoft Excel functions that make life easier. If you use Microsoft Excel regularly, it's worth learning Excel tips to save time.

    ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGisrJ1dqLmqr8SrZK2nXZu2rcDEq2StmZKhsm61zWacsZuVoXw%3D