How to Use Slicer in Excel (Examples & Customizations)

Download Practice Workbook

How to Use Slicer.xlsx

What Is Excel Slicer?

Slicer is an interactive way to Filter Excel Tables as well as Pivot Tables. It’s a smart way to Filter out data quickly. Along with filtering, the slicer shows the present criteria of filtering in an Excel Table or a Pivot Table.

Types of Excel Slicer

There are two types of Excel slicer:

Pivot Table Slicer
Table Slicer

3 Ways to Add Slicers in Excel

Method 1 – Use Slicer from Excel Pivot Table Fields

You have to first create a pivot table and then create slicers from the PivotTable Fields window.

Steps:

Create a Pivot Table. To create a Pivot table, select any cell from the dataset.
Go to the Insert tab in the top ribbon.
Click on the PivotTable menu and select the “From Table/Range” option.

In the pop-window, the range of the dataset will be selected automatically in the Table/Range option.
Select New Worksheet.
Click OK.

A new worksheet will be created where the pivot table is stored.

Click on the pivot table and you will see a PivotTable Fields window on the right side of the worksheet.
Mark the items and drag them to rows and columns to create a pivot table.

In the Pivot Table window, right-click on an item and you find an option named “Add as Slicer”.
Click on it to create a slicer for that item.

In the Excel worksheet, you will see a slicer. Drag and move it to a suitable position.

The slicer is created and you can filter the dataset by account types using the slicer.

Method 2 – Insert Slicer from Pivot Table Analyze Tab

Follow the steps of method 1 to create a pivot table.

Steps:

Select any cell of the pivot table.
You will see that a new tab is created in the top ribbon named “PivotTable Analyze”.
In the PivotTable Analyze tab, you will find the “Insert Slicer” option in the Go to this option.

In the Insert Slicer window, you will see the name of the column headers name as a list.
Mark the options as you want to create slicers.
Click OK.

You will see that slicers are created for the selected column headers.
Click on the top right corner of the slicer to select it. Drag or move the slicer to a suitable position.

Method 3 – Insert Slicer to Excel Table from Design Tab

Steps:

To convert the dataset into an Excel table, select any cell of the dataset.
Go to the Home tab and click on the “Format as Table” option.
A list of styles will appear. Select the desired table style.

In the Create Table window, you will see that the range of the dataset is already selected in the box.
Mark the box, “My table has headers”.
Click OK.

The dataset will be converted into a table. You will be able to create a filter arrow in each column header in this table.

Click on any cell of the excel table.
A new tab named “Table Design” will be on the top ribbon.
Click on the “Insert Slicer” option under this tab.

In the Insert Slicers window, mark the items for which you want to create slicers.
Click OK.

Slicers will be created for the selected items.

Use of Excel Slicers

You can use slicers for various purposes in Excel. The main purpose of the slicer is to filter an Excel table and Pivot Table easily. Follow the steps below to use Slicers for any tables or Pivot tables.

After creating slicers, you can choose any of the items from the slicer to filter the table.
Click on the item to show the dataset only for that item. For example, we have created a slicer of Branch. So, when we select the item “Central” in the slicer, the dataset is automatically filtered for the central branch.
You can select multiple items in the slicers. For this, you have to hold the Ctrl key on the keyboard and select the items one by one from the slicer.

How to Format to Create Fancy Slicers in Excel

Steps:

Select the slicer.
You will see a new tab named Slicer on the top ribbon.
Under the Slicer tab, you will find many styles which you can apply to the slicer.

You can customize the slicer as required.

You can also change other formatting options. When you select the slicer, you will see a new window on the right side of the worksheet named “Format Slicer”.

From the Format Slicer window, you can change the Position, height, width and other features of the slicer.
You can convert the slicer into two or more columns. You can change the column number from the “Number of Columns” under the Layout