Working With Slicers in Excel

Working With Slicers in Excel

For dedicated, instructor driven Microsoft Excel courses in Los Angeles call us on 888.815.0604..

In this article, we will learn how to:
  1. Insert and use a slicer
  2. Rename a slicer
  3. Change slicer settings

Inserting and Using a Slicer

Slicers allow you to quickly filter any data that is displayed within a PivotTable. To insert a slicer, first click anywhere inside the PivotTable that you are working with. Next, click PivotTable Tools – Analyze → Insert Slicer:

insert slicer function

The Insert Slicers dialog will now be open. You may use the controls in this dialog to choose what criteria you would like to sort the data by. For this example ensure that a checkmark appears beside Package. Click OK:

slicer dialog displayed

Now the slicer pop-up will be displayed:

slicer pop up displayed

Move this slicer so that is doesn’t obstruct the PivotTable by clicking and dragging it by its header to a new and unobtrusive location:

slicer in unobtrusive location

Next, you need to use the slicer to sort the data in the PivotTable by criteria from the selected category. For this example, click LUX:

pivot table sort

Now only vehicles that were sold with the LUX package will be accounted for in the PivotTable:

pivot table lux displayed

Renaming the Slicer

By default, any slicer that you insert into your worksheet will be named after the field that you chose for it to work with. To change this name, first click on the slicer to select it:

click slicer to amend name

By selecting it, you will make the Slicer Tools – Options tab available on the ribbon. Click to open this tab and you will see that the Slicer group contains a text box:

slicer options

Replace the text in this text box with the new name you want for the slicer. For this example, type “Module 2:”

rename text

The new name will immediately be applied to the currently selected slicer:

new name displayed

Changing Slicer Settings

To access a variety of different settings you can use to customize your slicer, right-click on the slicer and then click Slicer Settings:

access settings

The Slicer Settings dialog will now be shown:

slicer settings shown

Using the controls in this dialog you can rename the slicer, change its caption, change sortation options, as well as choose how to handle items with no data. For this example, hide the header for this slicer by deselecting the “Display header” check box:

sortable options

Click OK to apply your changes:

apply changes

The header for the selected slicer will now be hidden:

header change


Microsoft Excel available in LA

Join our Microsoft Excel training workshop. We run MS classes for all skill levels. Check out our extensive Microsoft Excel student testimonies page.