• Article

Add Slicers to PivotCharts (Tutorial)

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 task you will use PowerPivot Slicers to control what data appears in your PivotChart.

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.

To Add Slicers to a PivotChart

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

  2. In the PivotChart Tools area of the Excel ribbon, click Analyze.

  3. Click Insert Slicer.

  4. In the Insert Slicers window, locate the DimDate table and select CalendarYear and CalendarMonth.

  5. Under the Geography table, select RegionCountryName.

  6. Click OK.

To Format Slicers

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

  2. Sometimes Slicers must be resized in order to display their contents correctly.

    1. Select the CalendarYear Slicer and resize it so that only 2007, 2008, and 2009 are displayed.

    2. Right-click RegionCountryName and select Size and Properties.

    3. Highlight Position and Layout.

    4. In the Number of columns dropdown, select 6. Click Close.

  3. By default, Slicer items are displayed in alphabetical and numeric order, with items with no data displayed last. To change this view:

    1. Right-click the CalendarMonth Slicer, and select Slicer Settings.

    2. Uncheck Show items with no data last. Click OK.

  4. Continue to format your Slicers as needed.

To Use Slicers to Analyze your PivotChart Data

  1. Use the CalendarYear Slicer to explore profit by year. The PivotChart clearly shows the profit share increase for COMPUTERS and TV and VIDEO (at the expense of CAMERAS and CAMCORDERS) from 2007 to 2009. Profit share for the other categories shows almost no fluctuation.

  2. To dig even deeper, use the CalendarMonth Slicer. You will discover that CAMERAS and CAMCORDERS had the highest profit share in the latter months of 2007**.**

  3. Explore your data more by using the RegionCountryName Slicer. Clicking through the countries reveals many interesting facts.

Contoso can use this information to make intelligent business decisions.

See Also

Concepts

Filter Data using Slicers