An exciting new product is part of the Office 2010 Beta - Microsoft PowerPivot for Excel 2010 (formerly Gemini). PowerPivot for Excel is a powerful data analysis add-in that will let you work with millions of records within the familiar Excel environment.
If you haven't downloaded the Office 2010 Beta, you can test PowerPivot in the hands-on Virtual Lab. That's where I tested PowerPivot, and I was impressed by my first look at what PowerPivot for Excel can do.
For the PowerPivot for Excel test, I used Internet Explorer 8 as my browser, because the Virtual Lab wouldn't work with Firefox.
Once the virtual lab was running, it was easy to get started, and work with PowerPivot for Excel. The add-in creates a new tab on the Excel Ribbon, as shown below.
To launch the add-in, simply click the PowerPivot Window command, which opens the PowerPivot client window. From there, you can connect to data from a variety of sources. In my daily work, I'd usually connect to Microsoft Access data, but in this example I used the SQL Server connection, as instructed in the virtual lab instructions.
The next step is to select a table from the data source, and PowerPivot for Excel can automatically select any related tables. A handy feature is that you can filter the selected data before you import it. That would reduce the import time, by limiting the data to what you need for a specific analysis.
In the virtual lab, I connected to a Sales table that had almost 4 million records, and it took just a minute or two for the PowerPivot for Excel add-in to import the data.
In the PowerPivot client window, each table is on a separate worksheet, with its name on the sheet tab. You can change the tab names, to make the names shorter, or easier to understand.
You can also add calculated fields in the tables, as shown below. To create a calculated field, just type in the formula bar, which looks just like Excel's. To make it easy to refer to the imported data, the field names appear automatically when you start typing.
The whole point of importing all the data with the PowerPoint for Excel add-in, is to analyze it in an Excel pivot table.
To create a pivot table and pivot chart from the imported data, you use the PowerPivot Task Pane (called the Gemini Task Pane in the virtual lab).
The instructions implied that the pivot table and pivot chart were connected, but that wasn't the case in the virtual lab - adding fields to the pivot table didn't affect the pivot chart. I'm sure they'll fix that feature before the final release, or the pivot tables won't be very easy to manage.
You can also add horizontal and vertical Slicers to the pivot table and pivot chart, to filter the data that's displayed. Slicers are a new feature in Excel 2010, and I'm not sold on them yet.
The one benefit that I can see is that slicers can be connected to multiple pivot tables. That allows you to filter all the connected pivot tables at the same time. So, if your workbook as several pivot table, you won't need a macro to automatically filter a group of pivot tables.
However, the slicers use quite a bit of space on the worksheet, and make it look very cluttered. You can format them, so they're less intrusive, but I'll have to experiment a bit more, so see how that works best.
This was just a quick overview of PowerPivot for Excel, as tested in the 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.
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 for Excel, see the PowerPivot Team blog.
How did I end up in the PowerPoint for Excel virtual lab in the first place? Well, in November, a surprise package arrived in my mailbox - a set of power tools! It was a promotion for the release of PowerPivot for Excel, and the power tools had clever labels, like this label that was on the flashlight.
The creative promotion did convince me to try PowerPivot for Excel, especially since that was on my To Do list anyway.
The gift didn't influence my testing though -- I'll always tell you exactly what I think (in the politest way that I can).
Last updated: April 20, 2021 7:37 PM