Share via

Consuming PowerPivot Data in Excel 2010

  • Article

Office Visual How To

Summary:   Learn how to create Microsoft Excel 2010 PivotTables by using data that is provided by Microsoft PowerPivot for Excel 2010.

Applies to: Excel 2010 | Office 2010 | VBA

Published:   February 2011

Provided by:   Steve Hansen, Microsoft Visual Studio MVP and founder of Grid Logic.

Overview

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.

Download PowerPivot for Excel 2010

Code It

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

  1. In Excel, click the PowerPivot tab and then click PivotTable. Alternatively, in the PowerPivot window, click Home, Reports, and then click PivotTable.

    Figure 1. PowerPivot tab


    PowerPivot tab

    Figure 2. PowerPivot ribbon


    PowerPivot ribbon

  2. Specify where you want to create the PivotTable.

    Figure 3: Specifying the PivotTable location


    Specifying the PivotTable location

  3. Select the empty PivotTable and view the PowerPivot Field List on the left side of the window. If the window is not open, click the PowerPivot tab and then click Field List.

    Figure 4. PivotTable and Field List


    PivotTable and Field List

At this point, you can use the PivotTable as you would any other PivotTable in Excel, although PowerPivot PivotTables have a few additional features.

Using Slicers

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

Year Slicer

The second method is to use the PivotTable / PivotChart tabs on the ribbon in Excel.

  1. If you are working with a PivotTable, select any cell in the PivotTable, and then click the Options tab under PivotTable Tools. With a PivotChart, select the chart and then click the Analyze tab under PivotChart Tools.

  2. Click Insert Slicer.

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

  1. Select the PivotTable. If the PowerPivot Field List pane is not visible, select PowerPivot and then click Field List.

  2. Right-click the table name that contains the Avg Salary and Establishment Count measures, and then select Add New Measure.

    Figure 6. Creating a new measure


    Creating a new measure

  3. Name the new measure, Measure Avg Payroll Per Establishment.

  4. Type the formula: = SUMX('Employment Data',[Annual Payroll])*1000/SUMX('Employment Data',[Establishment Count])

  5. Click OK.

Figure 7. Using a new measure

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.

Read It

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

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.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/cb3e747b-cfc5-4214-912c-dc1a41ed3c4b]

Length: 00:06:44

Explore It

 

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.