Filtering data using Slicers - Excel pivot tables

How to filter data using Slicers in Pivot table?

Using one or more slicers is a fast and effective way for filtering data. You can insert slicers for each of the fields which you want to filter. Slicer consists of buttons representing the values of the field. Click on the buttons of a slicer to select/ unselect the values in a field.

Slicers will be visible with the PivotTable and you will know which fields are filtered and what values in those fields are shown or hidden in the filtered PivotTable.

For understanding the usage of slicers, take the example of sales data region-wise, month wise and salesperson-wise. Imagine that you have below PivotTable with this data.

usage_of_slicers.jpg

Inserting Slicers

If you want to filter this PivotTable based on the fields – Region and Month.

  • Click on ANALYZE under PIVOTTABLE TOOLS on the Ribbon.
  • Click on Insert Slicer in the Filter group, insert Slicers dialog box appears. It consists of all the fields from your data table.
  • Check the boxes Region and Month.
  • Click OK.

inserting_slicers.jpg

Slicers for each of the selected fields will appear with all the values selected by default. Slicer Tools will appear on the Ribbon to work on the Slicer settings, look and feel.

slicer_tools.jpg

Filtering with Slicers

As you can see, each slicer has all the values of the field which it represents and values are displayed as buttons. By default, all the values of a field will be selected and therefore all the buttons are highlighted.

If you want to display PivotTable only for the regions South and West and for the Months February and March.

  • Click on South in the Slicer for Region. Only South will be highlighted in the Slicer – Region.
  • Keep Ctrl key pressed and click on West in the Slicer for Region.
  • Click on February in the Slicer for Month.
  • Keep Ctrl key pressed and click on March in the Slicer for Month.

Selected items in the Slicers will be highlighted. PivotTable with summarized values for the selected items will be displayed as shown below.

filtering_with_slicers.jpg

To add/remove values of a field from the filter, keep the Ctrl key pressed and click on those buttons in the slicer of the field.

Clearing Filter in a Slicer

To clear the filter in a slicer, click on clear_filter.jpg at the top-right corner of the slicer.

clearing_filter_in_slicer.jpg

Removing a Slicer

If you want to remove slicer for the Region field.

  • Right click on the Slicer – Region.
  • Click on Remove “Region” in the dropdown list.

removing_slicer.jpg

Slicer Tools

When you insert a slicer, Slicer Tools appears on the Ribbon with OPTIONS tab. To view Slicer Tools, click on a slicer.

tab_options.jpg

As you can see, under the Slicer Tools – OPTION tab, there are many options for changing the look and feel of the slicer that include

  • Slicer Caption
  • Slicer Settings
  • Report Connections
  • Selection Pane

Slicer Caption

You can find the Slicer Caption box in the Slicer group. Slicer Caption is the header which is displayed on the slicer. By default, Slicer Caption will be the name of the field which it represents.

  • Click on the Slicer for Region.
  • Click the OPTIONS tab on the Ribbon.

slicer_caption.jpg

Slicer group on the Ribbon, in the Slicer Caption box, Region will be displayed as the header of the slicer. It is the name of the field for which the slicer is inserted. Slicer Caption can be changed as follows

  • Click on the Slicer Caption box in the Slicer group on the Ribbon.
  • Delete Region, box is cleared.
  • Type Location in the box and press Enter. Slicer Caption changes to Location and the same will be reflected as header in the slicer.

slicer_group.jpg

Remember that you have changed only the slicer caption, i.e. header. Name of the field that the slicer represents – Region remains as it is.

Slicer Settings

Slicer Settings can be used for changing the name of the slicer, changing slicer caption, choosing whether to display the slicer header or not and for setting the sorting and filtering options for the items

  • Click on the slicer - Location.
  • Click the OPTIONS tab on the Ribbon. You will find the Slicer Settings in the Slicer group on the Ribbon. You can also find Slicer Settings in the dropdown list when you right click on the slicer.
  • Click the Slicer Settings. Slicer Settings dialog box appears.

slicer_settings.jpg

As you can see, below names are fixed for the slicer

  • Source Name.
  • Name to use in formulas.

Following can be changed for the slicer

  • Name.
  • Header – Caption.
  • Display header.
  • Sorting and filtering options for the items displayed on the slicer.

Report Connections

Different PivotTables can be connected to a Slicer, if one of the following holds good −

  • PivotTables are created using the same data.
  • One PivotTable has been copied and pasted as an additional PivotTable.
  • Multiple PivotTables are created on separate sheets with Show Report Filter Pages.

Consider below PivotTables which are created from the same data

same_data.jpg

  • Name the top PivotTable as PivotTable-Top and the bottom one as PivotTable-Bottom.
  • Click on the top PivotTable.
  • Insert a Slicer for the field Region.
  • Select East and North on the Slicer.

top_pivottable.jpg

As you can see, filtering is applied only to the top PivotTable and not to the bottom PivotTable. Same slicer can be used for both the PivotTables by connecting it to the bottom PivotTable also as follows −

  • Click on the slicer - Region. The SLICER TOOLS appear on the Ribbon.
  • Click the OPTIONS tab on the Ribbon.

You can find Report Connections in the Slicer group on the Ribbon. You can also find Report Connections in the dropdown list when you right click on the slicer.

Click Report Connections in the Slicer group.

report_connections.jpg

Report Connections dialog box appears. Box PivotTable-Top is checked and other boxes are unchecked. Check the box PivotTable-Bottom also and click OK.

checked.jpg

Bottom PivotTable will be filtered to the selected items – East and North.

selected_items.jpg

This became possible as both the PivotTables are now connected to the slicer. If you make changes in the selections in the slicer, same filtering will appear in both the PivotTables.

Selection Pane

Display of the slicers can be switched on the worksheet off and on using the Selection Pane.

  • Click on the slicer - Location.
  • Click the OPTIONS tab on the Ribbon.
  • Click the Selection Pane in the Arrange group on the Ribbon. Selection Pane appears on the right side of the window.

selection_pane.jpg

As you can see, names of all the slicers are listed in the Selection pane. On the right side of the names, you can find the visibility symbol - eye.jpg which indicates that the slicer is visible on the worksheet.

Clickeye.jpg symbol for Month. Symboleye.jpg changes to the symbol line.jpg, indicating that the slicer is hidden (not visible).

month.jpg

As you can see, slicer – Month is not shown on the worksheet. But, remember that you have not removed the slicer for Month and you have just hidden it.

  • Click on theline.jpg symbol for Month.
  • Symbol line.jpgchanges to the symboleye.jpg, which indicates that the slicer is now visible.

When visibility of a slicer is switched on / off, selection of the items in that slicer for filtering will remain unaltered. Order of the slicers can also be changed in the Selection pane by dragging them up/down.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel pivot tables Topics