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.
If you want to filter this PivotTable based on the fields – Region and Month.
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.
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.
Selected items in the Slicers will be highlighted. PivotTable with summarized values for the selected items will be displayed as shown below.
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.
To clear the filter in a slicer, click on at the top-right corner of the slicer.
If you want to remove slicer for the Region field.
When you insert a slicer, Slicer Tools appears on the Ribbon with OPTIONS tab. To view Slicer Tools, click on a slicer.
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
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.
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
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 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
As you can see, below names are fixed for the slicer
Following can be changed for the slicer
Different PivotTables can be connected to a Slicer, if one of the following holds good −
Consider below PivotTables which are created from the same data
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 −
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 dialog box appears. Box PivotTable-Top is checked and other boxes are unchecked. Check the box PivotTable-Bottom also and click OK.
Bottom PivotTable will be filtered to the selected items – East and North.
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.
Display of the slicers can be switched on the worksheet off and on using the Selection Pane.
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 - which indicates that the slicer is visible on the worksheet.
Click symbol for Month. Symbol changes to the symbol , indicating that the slicer is hidden (not visible).
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.
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.
Excel pivot tables Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel Data Analysis Tutorial||Excel Power View Tutorial|
Excel pivot tables Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.