How To Use Slicers To Edit PivotTable Data in Microsoft Excel Tutorial

Slicers are visual controls that let you quickly filter data in a PivotTable in an interactive, intuitive way. If you insert a slicer, you can use buttons to quickly segment and filter the data to display just what you need. In addition, when you apply more than one filter to your PivotTable, you no longer have to open a list to see which filters are applied to the data. Instead, it is shown there on the screen in the slicer. You can make slicers match your workbook formatting and easily reuse them in other PivotTables, PivotCharts, and cube functions.

In earlier versions of Microsoft Excel, you can use report filters to filter data in a PivotTable report (PivotTable report: An interactive, cross-tabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), but it is not easy to see the current filtering state when you filter on multiple items. In Microsoft Excel 2010, you have the option to use slicers to filter the data. Slicers provide buttons that you can click to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable report.

What are slicers?

Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter the data in a PivotTable report, without the need to open drop-down lists to find the items that you want to filter.

When you use a regular PivotTable report filter to filter on multiple items, the filter indicates only that multiple items are filtered, and you have to open a drop-down list to find the filtering details. However, a slicer clearly labels the filter that is applied and provides details so that you can easily understand the data that is displayed in the filtered PivotTable report.

Slicers are typically associated with the PivotTable in which they are created.

A slicer typically displays the following elements:

1) A slicer header indicates the category of the items in the slicer.

2) A filtering button that is not selected indicates that the item is not included in the filter.

3) A filtering button that is selected indicates that the item is included in the filter.

4) A Clear Filter button removes the filter by selecting all items in the slicer.

5) A scroll bar enables scrolling when there are more items than are currently visible in the slicer.

6) Border moving and resizing controls allow you to change the size and location of the slicer.

Using slicers 

There are several ways to create slicers to filter your PivotTable data. In an existing PivotTable, you can:

• Create a slicer that is associated with the PivotTable.
• Create a copy of a slicer that is associated with the PivotTable.
• Use an existing slicer that is associated with another PivotTable.

In addition to or instead of creating slicers in an existing PivotTable, you can also create a stand-alone slicer that can be referenced by Online Analytical Processing (OLAP) Cube functions or that you can associate with any PivotTable at a later time.

Because each slicer that you create is designed to filter on a specific PivotTable field, it is likely that you will create more than one slicer to filter a PivotTable report.

After you create a slicer, it appears on the worksheet alongside the PivotTable, in a layered display if you have more than one slicer. You can move a slicer to another location on the worksheet, and resize it as needed.

To filter the PivotTable data, you simply click one or more of the buttons in the slicer.

Create a slicer 

1. Click anywhere in the PivotTable report for which you want to create a slicer. This displays the PivotTable Tools, adding an Options and a Design tab.
2. On the Options tab, in the Sort & Filter group, click Insert Slicer.

3. In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.

4. Click OK. A slicer is displayed for every field that you selected.

5. In each slicer, click the items on which you want to filter. To select more than one item, hold down CTRL, and then click the items on which you want to filter.

Format a slicer

1. Click the slicer that you want to format. This displays the Slicer Tools, adding an Options tab.

2. On the Options tab, in the Slicer Styles group, click the style that you want. To see all available styles, click the More button .

Share a slicer by connecting to another PivotTable 

You can share a slicer with another PivotTable by connecting it to that PivotTable. You can also insert a slicer from another PivotTable by connecting to that PivotTable.

Make a slicer available for use in another PivotTable

1. Click the slicer that you want to share in another PivotTable. This displays the Slicer Tools, adding anOptions tab.

2. On the Options tab, in the Slicer group, click PivotTable Connections.

3. In the PivotTable Connections dialog box, select the check box of the PivotTables in which you want the slicer to be available.  

Use a slicer from another PivotTable 

1. Create a connection to the PivotTable that contains the slicer that you want to share by doing the following:

• On the Data tab, in the Get External Data group, click Existing Connections

• In the Existing Connections dialog box, in the Show box, make sure that All Connections is selected.

Note: If you do not see the connection that you want, you can create a connection. ClickBrowse for More, and then in the Select Data Source dialog box, click New Source to start the Data Connection Wizard so that you can select the data source that you want to connect to.

• Select the connection that you want, and then click Open.

• In the Import Data dialog box, under Select how you want to view this data in your workbook, clickPivotTable Report.

2. Click anywhere in the PivotTable report for which you want to insert a slicer from another PivotTable. This displays the PivotTable Tools, adding an Options and a Design tab.

3. On the Options tab, in the Sort & Filter group, click the Insert Slicer arrow, and then click Slicer Connections.

4. In the Slicer Connections dialog box, select the check box of the slicers that you want to use.

5. Click OK.

6. In each slicer, click the items on which you want to filter. To select more than one item, hold down CTRL, and then click the items that you want to filter.

• All PivotTables that share the slicer will instantly display the same filtering state. 

Disconnect or delete a slicer

If you no longer need a slicer, you can disconnect it from the PivotTable report, or you can delete it. 

Disconnect a slicer

1. Click anywhere in the PivotTable report for which you want to disconnect a slicer. This displays thePivotTable Tools, adding an Options and a Design tab.

2. On the Options tab, in the Sort & Filter group, click the Insert Slicer arrow, and then click Slicer Connections.

3. In the Slicer Connections dialog box, clear the check box of any PivotTable fields for which you want to disconnect a slicer.

Delete a slicer 

Do one of the following:

• Click the slicer, and then press DELETE.

• Right-click the slicer, and then click Remove <Name of slicer>.