In this video, I’ll show you how to use PowerPivot slicers.PowerPivot slicers help me with my analysis by controlling what data appears in my PivotTables.
Slicers can also be used with PivotCharts, and the use of Slicers with PivotCharts is covered in a separate video.
Slicers are one-click filtering controls that narrow down 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.
Let’s add slicers to the Profit by Category PivotTable we created in a previous video. Click anywhere inside the PivotTable to display the PowerPivot task pane.
In the PivotTable tools area of the Excel ribbon, click Options and then Insert Slicer. In the window, I find the DimChannel table and select ChannelName.
Under the DimProductSubcategory table, I select ProductSubcategoryName. Under the DimProductCategory Table, I select ProductCategoryName.
The slicers I selected up here and I move and resize them so I can see them all at once.
Some of the slicer names are too long to display, so I right click and select Slicer Settings and rename ProductSubcategoryName to Subcategory
and ProductCategoryName to Category. I’d like to display all options in the RegionCountryName slicer, so I right click and select Size and Properties,
highlight Position and Layout, and increase the number of columns. I continue to fine tune the display and formatting of my slicers until I’m happy with the result.
Now that my slicers are arranged and formatted, I’ll use them to analyze the PivotTable data. The first thing I want to look at is Sales Profit Trends by Channel.
In the ChannelName slicer, select Catalog. Catalog should now be the only shaded item in the slicer list.
By looking at the PivotTable, I see that catalog sales profits are declining. One at a time, I click Online, Reseller, and Store to reveal those profit trends.
I see that online profits are increasing. Store profits are decreasing and reseller is remaining fairly stable.
Based on what I discovered, Contoso may need to redistribute Marketing budgets and/or close one of the channels.
Clear the filters by clicking the icon in the top right corner of the slicer. I dig a little further by slicing profit data by Subcategory and Country.
I discover several interesting things. Overall, profits from the sales of televisions more than doubled from 2007 to 2009.
However, most of that increase came in 2008 with very little profit increase seen in 2009.
Why were profits flat in 2009? How can they be increased?
Contoso can use this information and much more to make intelligent business decisions. I’ve added slicers to my PivotTables and used those slicers to analyze my data. Thank you for taking the time to view this video. I hope you found it helpful.
