|
Home > Pivot > Create > Power Pivot PowerPivot for Excel 2010 TestingBack in November 2009, I was invited to test an exciting new product in the Office 2010 Beta -- Microsoft PowerPivot for Excel 2010 (formerly code-named 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. My review notes for that early version of Power Pivot are below, and you can get information on the latest version on the Microsot site. Many features have been added and improved since those early days!
|
The PowerPivot for Excel Add-In
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.
Import Data to PowerPivot
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.
Create a PivotTable and PivotChart
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.
Add Slicers
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.
Final Notes
This was just a quick overview of PowerPivot for Excel, as tested in the virtual lab. 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.
The PowerPivot for Excel Promotion
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).
Related Links
Create Pivot Table in Excel 2013
Last updated: January 26, 2023 3:47 PM