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 my first look at 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 convince me to try PowerPivot? Yes they did. Testing PowerPivot was on my To Do list anyway, and the creative gift inspired me to move it to the top. Will the gift influence my testing? No, it won’t. 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 Internet Explorer 8 was the browser. (It didn’t like Firefox.)

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.

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, as instructed in the module.

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 minute or two 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.

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. The lack of connection made it tricky to follow the instructions, which seemed to jump between the pivot table and pivot chart.

You can also add horizontal and vertical Slicers to the pivot table and pivot chart, to filter the data that’s displayed. The slicers use quite a bit of space on the worksheet, but you can format them, so they’re less intrusive. The virtual lab example of selecting a country by number wasn’t too helpful. Why not show how you can use the country name instead?

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. I liked the way that PowerPivot is integrated into Excel, with a familiar interface, and that makes it easy to get up and running.

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.
• For a new pivot table formatting tip, see John Walkenbach’s Spreadsheet Blog: Unlinking A Pivot Table

___________________