Skip to main content

Using Slicers with PowerPivot PivotTables

Rate:  

About This Video:

This video shows you how to add slicers to a PivotTable in a PowerPivot workbook. Slicers can be used to interactively filter and analyze data by controlling what data appears in your PivotChart.

This video is available with closed captions. To view closed captions, click CC on the video control bar.

Transcript

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 Pivot tables.

Slicers can also be used with Pivot charts, and the use of Slicers with Pivot charts is covered in a separate video.

Slicers are one click filtering controls that narrow down the portion of a data set shown in Pivot tables and Pivot charts.

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 Channel Name.

Under the DimProduct Subcategory Table, I select Product Subcategory Name.

Under the DimProduct Category Table, I select Product Category Name.

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 Products of Category Name to Subcategory and Product Category Name to Category.

I’d like to display all options in the Region Country Name 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 Channel Name 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.

 

Presented by: Michele Hart and Ed Price

 

Downloads

Video: WMV(Zip) | MP4 | WMV