Share via

Add Slicers to PivotTables (Tutorial)

  • Article

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

Slicers are one-click filtering controls that narrow the portion of a data set shown in PivotTables and PivotCharts. Slicers can be used in both Microsoft Excel workbooks and PowerPivot workbooks, to interactively filter and analyze data.

In this lesson you will use PowerPivot Slicers to control what data appears in your PivotTable.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

Add Slicers to a PivotTable

To Add Slicers to the Profit by Category PivotTable

  1. Click anywhere inside the Profit by Category PivotTable to display the PowerPivot Field List.

  2. In the PivotTable Tools area of the Excel ribbon, click Options.

  3. Click Insert Slicer.

  4. In the Insert Slicers window, locate the Geography table and select ContinentName.

  5. Under the DimChannel table, select ChannelName.

  6. Under the DimProductSubcategory table, select ProductSubcategoryName.

  7. Click OK.

Format Slicers

To Format Slicers

  1. Arrange the slicers so that they can all be seen. To move the Slicers, click the gray border and drag.

    Move the PivotChart down and to the side to make room for the slicers.

  2. The title of the ProductSubcategoryName Slicer is truncated. To format this Slicer, right-click the Slicer and select Slicer Settings.

    1. In the Caption box, type Subcategory.

    2. Verify that Display header is selected.

    3. Click OK.

  3. Sometimes Slicers must be resized in order to display their contents correctly. Resize the Subcategory Slicer by adding columns.

    1. Right-click the Product Subcategory Slicer and select Size and Properties.

    2. Highlight Position and Layout.

    3. In the Number of columns dropdown, select 2. Click Close.

    4. Drag the corners until all content is visible.

  4. Continue to format your Slicers as needed.

Use Slicers to Analyze your PivotTable Data

To Use Slicers to Analyze your PivotTable Data

  1. At Contoso we want to evaluate our sales profit trends by channel. Based on what we discover, we may have to redistribute marketing budgets and/or close channels.

    1. In the ChannelName Slicer, select Catalog. Catalog should now be the only shaded item in the Slicer list.

    2. By looking at the PivotTable you see that catalog sale profits are declining.

    3. One at a time, click Online, Reseller, and Store to reveal those profit trends. You see that online profits are increasing, store profits are decreasing and reseller profits are decreasing slightly.

    4. Clear the filters you have set by clicking the icon in the upper-right corner of the Slicer.

  2. Dig a bit further by slicing your profit data by subcategory and continent. Here are several interesting things that you might discover:

    1. Profits have more than doubled for cellphone accessories, televisions, and recording pens with the biggest percentage increase coming from online sales. While most sales were made in stores, store profit percentage increase was the lowest. Given that most sales come from stores, what can be done to maximize profits from this channel?

    2. Cellphone accessories profits took a significant jump in 2009 for the Reseller and Store channels. Prior to that, profit increase was fairly flat in those 2 channels. To what can this jump be attributed?

    3. Overall profits from the sales of televisions more than doubled from 2007-2009. However, most of that increase came in 2008 with very little profit increase seen in 2009. Why were profits flat in 2009 and how can they be increased?

    4. Sales of Desktops dropped significantly. With total profits of almost 260 million, only 21 million came from catalog sales. Catalog sales saw profits drop from almost 10 million (2007) to 4 million (2009). Perhaps Contoso should close this channel?

    5. Profits in Asia are increasing whereas profits in and North America are decreasing.

Contoso can use this information, and much more, to make intelligent business decisions.

Next Step

To continue this tutorial, go to the next topic: Add Slicers to PivotCharts (Tutorial).

See Also

Concepts

Filter Data Using Slicers