Add Slicers to PivotTables (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 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
Click anywhere inside the Profit by Category PivotTable to display the PowerPivot Field List.
In the PivotTable Tools area of the Excel ribbon, click Options.
Click Insert Slicer.
In the Insert Slicers window, locate the Geography table and select RegionCountryName.
Under the DimChannel table, select ChannelName.
Under the ProductCategory table, select ProductCategoryName.
Under the DimProductSubcategory table, select ProductSubcategoryName.
Click OK.
Format Slicers
To Format Slicers
Arrange the slicers so that they can all be seen. To move the Slicers, click the gray border and drag.
The title of the ProductSubcategoryName Slicer is truncated. To format this Slicer, right-click the Slicer and select Slicer Settings.
In the Caption box, type Subcategory.
Verify that Display header is selected.
Click OK.
Sometimes Slicers must be resized in order to display their contents correctly. Resize the RegionCountryName Slicer by adding columns.
Right-click the RegionCountryName Slicer and select Size and Properties.
Highlight Position and Layout.
In the Number of columns dropdown, select 4. Click Close.
Drag the corners until all content is visible.
Continue to format your Slicers as needed.
Use Slicers to Analyze your PivotTable Data
To Use Slicers to Analyze your PivotTable Data
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.
In the ChannelName Slicer, select Catalog. Catalog should now be the only shaded item in the Slicer list.
By looking at the PivotTable you see that catalog sale profits are declining.
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.
Clear the filters you have set by clicking the icon in the upper-right corner of the Slicer.
Dig a bit further by slicing your profit data by subcategory and country. Here are several interesting things that you might discover:
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?
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?
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?
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?
Profits in Asian countries are increasing whereas profits in European countries, and North America are decreasing.
Contoso can use this information, and much more, to make intelligent business decisions.