Consuming PowerPivot Data in Excel 2010
Summary: Learn how to create Microsoft Excel 2010 PivotTables by using data that is provided by Microsoft PowerPivot for Excel 2010.
Applies to: Microsoft Excel 2010 | Microsoft PowerPivot for Excel 2010
Published: February 2011
Provided by: Steve Hansen, Microsoft Visual Studio MVP and founder of Grid Logic.
After you import data into PowerPivot, you can visualize that data by using Excel PivotTables and PivotCharts. PowerPivot, much like standard Excel PivotTables, gives you a way to display your data and a navigational interface that you can use to focus on the data that is relevant to your analysis. The only real difference when you use PowerPivot is that your data is stored in PowerPivot, and that difference gives you capabilities that are beyond standard Excel PivotTables.
This Visual How-To illustrates some of the subtle differences between PowerPivot PivotTables and standard Excel PivotTables.
For the most part, using PowerPivot PivotTables is identical to using standard Excel PivotTables. However, there are some important differences. For example, the only way to create a PowerPivot PivotTable is by using PowerPivot. You cannot access data that is stored in PowerPivot by using standard Excel PivotTables.
Displaying PowerPivot Data in Excel
PowerPivot gives you two ways to display data in Excel: a PivotTable or a PivotChart. The PivotTable button on the Insert tab on the ribbon is not associated in any way with PowerPivot; you cannot use that button to access PowerPivot data.
To create a PivotTable based on PowerPivot data
At this point, you can use the PivotTable as you would any other PivotTable in Excel, although PowerPivot PivotTables have a few additional features.
Slicers are a convenient user interface component that you can use to quickly filter a PivotTable or PivotChart. You can add slicers to a PivotTable or PivotChart by using one of two methods.
The first method is to drag and drop the desired field into one of the Slicer areas on the PowerPivot Field List task pane. The ability to specify slicers within the PivotTable layout is unique to PowerPivot PivotTables. Figure 5 shows an example of a Year slicer that was created by dragging the Year field into the Slicers Horizontal area in the PowerPivot Field List task pane.
Figure 5. Year Slicer
The second method is to use the PivotTable / PivotChart tabs on the ribbon in Excel.
If you select one item or multiple items in a slicer, the PivotTable that is associated with the slicer immediately calculates the relevant results. To select multiple items in a slicer, hold down the Shift key or the Ctrl key. Use the Shift key to select a contiguous range of items; use the Ctrl key to select multiple non-contiguous items.
Working With Measures
The term measure refers to the data elements that you can place into the Values area of a PivotTable. You can think of a measure as a type of field containing numerical data that can be aggregated by using count, sum, or average functions as you navigate or manipulate a PivotTable.
PowerPivot enables you to create new measures by using a syntax called Data Analysis Expressions (DAX). As an example, consider a dataset that has two existing measures: Avg Salary and Establishment Count. You could create a new measure called Avg Payroll Per Establishment by performing the following steps.
To create a measure
Figure 7. Using a new measure
After you create the measure, you can use it as you would any other measure. Note the small icon after Avg Payroll Per Establishment in figure 7; the icon indicates that the field is a custom measure.
After you create your PivotTable or PivotChart, the first step is to lay out your view. To lay out the view of a PivotTable, you use the PowerPivot Field List task pane. This task pane is very similar to the PivotTable Field List task pane that you use to manipulate standard Excel PivotTables. The PowerPivot task pane, however, includes more functionality.
Figure 8. PowerPivot task pane
There are two significant visual differences. First, the fields are listed differently. Second, the PowerPivot task pane includes slicer areas so that you can create slicers automatically as part of the layout process instead of creating them after the fact as you do with a standard PivotTable.
Another significant difference, which is not immediately apparent, is that the PowerPivot task pane has more options in the context menus that appear when you right-click the elements in the task pane.
It is helpful to be aware of these differences to prevent confusion. If you hide the PowerPivot task pane and then attempt to display it by using the Field List button on the PivotTable Tools group on the Options tab, you will display the PivotTable task pane instead of the PowerPivot task pane. To show the PowerPivot task pane, use the Field List toggle button on the PowerPivot tab on the ribbon.
Watch the video
About the Author
Steve Hansen is the founder of Grid Logic, a Minnesota-based consulting firm specializing in business intelligence and information worker solutions. A developer, frequent author and speaker at technical conferences, Steve is a Microsoft MVP for his work with Visual Studio. One part code jockey, one part finance geek; Steve also has an MBA from the University of Minnesota with a concentration in finance.
Steve would like to thank Grid Logic's Sean Van Dyk for his assistance with this article.