MVPs for Office and SharePoint 2010: Using PowerPivot with Excel 2010 to Mash Up Data

  • Article

Editor's Note: The following is a guest post by MVP Bill Jelen as part of the MVP Award Program Blog's "MVPs for Office and SharePoint 2010" series. Bill Jelen is an Excel MVP and the host of MrExcel.com. He is the author of PowerPivot for the Data Analyst: Excel 2010 from QUE.  

PowerPivot is a new business intelligence tool introduced for Excel 2010. PowerPivot enables even casual users of Excel to produce sophisticated business intelligence reports from multiple data sets without having to master the VLOOKUP function.

PowerPivot can use data from just about anywhere. Sheet2 might be some data imported from SQL Server or Oracle. Sheet2 could be a small lookup table that you paste from an Excel workbook. Sheet3 might be from a text file. Amazingly, data that is imported into Excel using PowerPivot is not limited to 1,048,576 rows per sheet. I’ve personally imported five million rows into a PowerPivot worksheet and I’ve seen 100 million rows on a PowerPivot worksheet. 

The real day-to-day power in PowerPivot is the ability to create pivot tables using fields from any or all of the PowerPivot worksheets. When you choose to create a pivot table, you will see a new PowerPivot Field List instead of the traditional PivotTable Field List. This field list offers fields from all of the worksheets.

Start to build your pivot table using fields from Sheet1. As soon as you select a field from Sheet2, PowerPivot suggests that a relationship will be needed. Click the Join button and PowerPivot will attempt to figure out the relationship between Sheet1 and Sheet2. In this example, the StoreID field on Sheet1 corresponds to the Store field on Sheet2. Because the complete universe of store numbers on sheet1 is found in the Store field on Sheet2, PowerPivot correctly guesses that StoreID and Store are the key fields. After a few seconds of processing, PowerPivot reports that a relationship has been created.

 

In other cases, PowerPivot can not detect the relationship. When this happens, it will require three mouse clicks for you to define the relationship. Follow these steps:

1. Switch to the PowerPivot window by using the PowerPivot Window icon in the PowerPivot tab of the ribbon.

2. Click on the key field from one of the worksheets. 

3. Go to the Design tab of the PowerPivot ribbon. Choose Create Relationship.

4. In the Create Relationship dialog, specify the Related Lookup Table and the Related Lookup Column. Click Create.

5. Return to Excel using the XL icon in the top left corner.

6. Click the Refresh button in the top of the PowerPivot Field List. The pivot table will calculate.

In this figure, data from all three worksheets is mashed up into a single pivot table:

Excel gurus will point out that this logic could have been accomplished in any version of Excel using VLOOKUP. This is true. PowerPivot enables people who are not comfortable with VLOOKUP to join tables. Even if you are comfortable with VLOOKUP, it doesn’t make sense to add millions of cells of VLOOKUP to the workbook when PowerPivot can perform all of the necessary join logic in memory.

PowerPivot includes the ability to use 140 different functions on the data in the PowerPivot grid. Many of these functions are the same as those in Excel, but PowerPivot adds two types of functions of interest:

Time Intelligence Functions handle calculations such as MTD, YTD, Fiscal Year, and so on. You can calculate all records that are from the parallel period one year ago, or find sales from the same weekday that occurred 52 weeks ago (this latter calculation is critical when looking at retail daily sales).

Filtering functions are used when calculating a new field in a pivot table. By default, the calculation for a cell will respect all of the filters applied to that cell in the pivot table. New PowerPivot functions such as ALL or ALLEXCEPT will allow you to override a particular filter. This is very useful when calculating the denominator of many popular calculations. 

The combination of virtually unlimited records, joining tables without VLOOKUP, and the powerful new calculation options make PowerPivot the best new feature to arrive in Excel in the last decade.