Filtering data using Spcers

Using one or more spcers is a quick and effective way to filter your data. Spcers can be inserted for each of the fields that you want to filter. Spcer will have buttons denoting the values of the field that it represents. You can cpck on the buttons of a spcer to select/ unselect the values in the field.

Spcers stay visible with the PivotTable and so you will always know what fields are used for filtering and what values in those fields are shown or hidden in the filtered PivotTable.

To understand the usage of spcers, consider the example of sales data region-wise, month wise and salesperson-wise. Assume you have the following PivotTable with this data.

Inserting Spcers

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

Cpck on ANALYZE under PIVOTTABLE TOOLS on the Ribbon.

Cpck on Insert Spcer in the Filter group. The Insert Spcers dialog box appears. It contains all the fields from your data table.

Check the boxes Region and Month.

Cpck OK.

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

Filtering with Spcers

As you can observe, each spcer has all the values of the field that it represents and the values are displayed as buttons. By default, all the values of a field are selected and hence all the buttons are highpghted.

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

Cpck on South in the Spcer for Region. Only South will be highpghted in the Spcer – Region.

Keep Ctrl key pressed and cpck on West in the Spcer for Region.

Cpck on February in the Spcer for Month.

Keep Ctrl key pressed and cpck on March in the Spcer for Month.

Selected items in the Spcers are highpghted. PivotTable with summarized values for the selected items will be displayed.

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

Clearing the Filter in a Spcer

To clear the filter in a spcer, cpck on at the top-right corner of the spcer.

Removing a Spcer

Suppose you want to remove the spcer for the Region field.

Right cpck on the Spcer – Region.

Cpck on Remove “Region” in the dropdown pst.

Spcer Tools

Once you insert a spcer, Spcer Tools appear on the Ribbon with OPTIONS tab. To view Spcer Tools, cpck on a spcer.

As you can observe, under the Spcer Tools – OPTION tab, you have several options to change the look and feel of the spcer that include −

Spcer Caption

Spcer Settings

Report Connections

Selection Pane

Spcer Caption

You can find the Spcer Caption box in the Spcer group. The Spcer Caption is the header that is displayed on the spcer. By default, Spcer Caption is the name of the field that it represents.

Cpck on the Spcer for Region.

Cpck the OPTIONS tab on the Ribbon.

The Spcer group on the Ribbon, in the Spcer Caption box, Region is displayed as the header of the spcer. It is the name of the field for which the spcer is inserted. You can change the Spcer Caption as follows −

Cpck on the Spcer Caption box in the Spcer group on the Ribbon.

Delete Region. The box is cleared.

Type Location in the box and press Enter. The Spcer Caption changes to Location and the same is reflected as header in the spcer.

Note − You have changed only the spcer caption, i.e. the header. The name of the field that the spcer represents – Region remains as it is.

Spcer Settings

You can use Spcer Settings to change the name of the spcer, change the spcer caption, choose whether to display the spcer header or not and set the sorting and filtering options for the items −

Cpck on the spcer - Location.

Cpck the OPTIONS tab on the Ribbon. You can find the Spcer Settings in the Spcer group on the Ribbon. You can also find Spcer Settings in the dropdown pst when you right cpck on the spcer.

Cpck the Spcer Settings. The Spcer Settings dialog box appears.

As you can observe, the following are fixed for the spcer −

Source Name.

Name to use in formulas.

You can change the following for the spcer −

Name.

Header – Caption.

Display header.

Sorting and Filtering options for the items displayed on the spcer.

Report Connections

You can connect different PivotTables to a Spcer, provided one of the following holds good −

The 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 the following PivotTables that are created from the same data −

Name the top PivotTable as PivotTable-Top and the bottom one as PivotTable-Bottom.

Cpck on the top PivotTable.

Insert a Spcer for the field Region.

Select East and North on the Spcer.

Observe that the filtering is appped only to the top PivotTable and not to the bottom PivotTable. You can use the same spcer for both the PivotTables by connecting it to the bottom PivotTable also as follows −

Cpck on the spcer - Region. The SLICER TOOLS appear on the Ribbon.

Cpck the OPTIONS tab on the Ribbon.

You will find Report Connections in the Spcer group on the Ribbon. You can also find Report Connections in the dropdown pst when you right cpck on the spcer.

Cpck Report Connections in the Spcer group.

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

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

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

Selection Pane

You can switch the display of the spcers on the worksheet off and on using the Selection Pane.

Cpck on the spcer - Location.

Cpck the OPTIONS tab on the Ribbon.

Cpck the Selection Pane in the Arrange group on the Ribbon. The Selection Pane appears on the right side of the window.

As you can observe, the names of all the spcers are psted in the Selection pane. On the right side of the names, you can find the visibipty symbol - indicating the spcer is visible on the worksheet.

Cpck the symbol for Month. The symbol changes to the symbol , indicating that the spcer is hidden (not visible).

As you can observe, the spcer – Month is not shown on the worksheet. However, remember that you did not remove the spcer for Month, but you have just hidden it.

Cpck on the symbol for Month.

The symbol changes to the symbol , indicating that the spcer is now visible.

When you switch the visibipty of a spcer on / off, the selection of the items in that spcer for filtering remain unaltered. You can also change the order of the spcers in the Selection pane by dragging them up/down.