Drill Into Data With PowerPivot

Have you tried Microsoft PowerPivot for Excel 2010 (formerly Gemini)? It’s a powerful data analysis add-in for Excel, and is part of the Office 2010 Beta. If you haven’t downloaded the Beta, you can test PowerPivot in the hands-on Virtual Lab.

That’s where I tested PowerPivot last weekend, and hit a few snags, but was impressed by what PowerPivot can do.

Testing PowerPivot

On Friday, a surprise package arrived in my mailbox – a set of power tools! It was a promotion for last week’s release of PowerPivot, and the power tools had clever labels, like this one on the flashlight.


Did the power tools influence my decision to try PowerPivot? Of course! Testing PowerPivot was on my To Do list, and the power tools inspired me to move it to the top. Will the gift influence my testing? Nope. I’ll still tell you exactly what I think.

The PowerPivot Add-In

I had trouble with the virtual lab on my desktop computer, and couldn’t get the ActiveX control installed. My laptop is newer, and everything went smoothly there. Both machines are Windows XP, and I used Internet Explorer 8 as the browser.

Once the virtual lab was running, it was easy to get started, and work with PowerPivot in Excel. The PowerPivot add-in creates a new tab on the Excel Ribbon.

PowerPivot Ribbon

Click PowerPivot Window, to launch the add-in, and open the PowerPivot client window. From there, you can connect to data from a variety of sources. I’d normally connect to Access data, but in this example I used the SQL Server connection.

PowerPivot Data

Next, select a table from the data source, and PowerPivot can automatically select related tables. You can also filter the selected data, before importing it. In the virtual lab, I connected to a Sales table that had almost 4 million records, and it took just a couple of minutes to import.

The Imported Data

In the PowerPivot client window, each table is on a separate tab. You can change the tab names, and add calculated fields in the tables. The formula bar looks just like Excel’s, and the field names appear automatically when you start typing.

PowerPivot Formula Bar

Create a PivotTable and PivotChart

You can create a pivot table and pivot chart from the data, using the PowerPivot Task Pane (called the Gemini Task Pane in the virtual lab). The pivot table and pivot chart weren’t connected though – adding fields to one, didn’t affect the other. I’m not sure if that was a bug in the virtual lab, or a Beta feature that will be fixed later.

PowerPivot PivotTable

You can also add horizontal and vertical Slicers to the pivot table and pivot chart, to filter the data that’s displayed.

PowerPivot Slicers

Try PowerPivot Yourself

This was just a quick overview of the PowerPivot test in the PowerPivot virtual lab. If you don’t have the Office 2010 Beta installed, I’d recommend this as a great way to see what PowerPivot can do.

It took me about an hour to go through the 3 modules, while making notes and taking screenshots. There’s a button to download a PDF file with the instructions, but that didn’t work, so I copied the instructions and pasted them into Word.

It was easier to read the instructions in Word, where I could increase the Zoom level. Also, the instructions disappeared at one point, and I would have had to start over, if I hadn’t made a copy.

The virtual machine hung on me a couple of times, and I don’t see a way to start anywhere except the beginning. Restarting was annoying, but it was pretty quick to go through the steps the second time.


For more information on PowerPivot, see the PowerPivot Team blog.

For more information on Excel Pivot Tables and Excel Pivot Charts, see the Pivot Table FAQs on my Contextures website.