Rob Collie, chief technology officer at software company Pivotstream, continues his series explaining how you can do more with Excel through an exciting add on program from Microsoft: PowerPivot.
In the first three parts of this series, I introduced you to PowerPivot, its hybridisation of Excel and ‘industrial strength’ BI, and its transformational ROI benefits. Now it’s time for a tour of the product itself.
Excel 2010 add-in
When you install the PowerPivot add-in for Excel 2010, it manifests as an additional ribbon tab:
This is an important advantage: PowerPivot is not a separate application, but merges right into the Excel environment you already know.
Excel 2010 is required, however (read an explanation of why Excel 2007 isn’t sufficient).
Embracing your existing excel workflow
In my years on the Excel team I became very familiar with the following workflow:
- Load tables of data into Excel.
- Extend and modify those tables via calculated columns.
- Create PivotTables against the modified tables.
- Create reports either by formatting the pivots or referencing them with other formulas.
In my last job at Microsoft, I was tasked with architecting the overall user experience of PowerPivot for Excel. My goal was to make PowerPivot as natural as possible for Excel pros to adopt. With that in mind, I carefully shaped PowerPivot around that four step workflow.
It wasn’t possible to match the existing Excel interface perfectly, because PowerPivot offers a lot of features that Excel alone does not. The central theme was to make the PowerPivot flow ‘rhyme’ with the Excel flow as closely as possible.
Step one: load data
Here’s our first example of ‘rhyming’ rather than perfectly matching. PowerPivot can hold a tremendous amount of data, much more than the one million row capacity of Excel 2010. So when you load data into PowerPivot, you load it into the PowerPivot window rather than into worksheets in Excel.
You start by clicking the ‘PowerPivot Window’ button on the PowerPivot ribbon:
The PowerPivot window appears over the top of Excel:
Two windows, one file
Don’t worry, this doesn’t mean that you have to carry around two files. The two windows are both linked into the same workbook, as illustrated by the identical filename at the top of each window:
Every time you click save in either window, you get a single XLSX file containing the contents of both windows – the Excel window and the PowerPivot window.
Load data from many sources
In the PowerPivot window, you can grab data from many different sources:
The three most common are circled above:
- Paste from any table-shaped source – Excel, Word tables, tables on web pages, etc.
- Import from database – Access, SQL Server, Oracle, DB2, etc.
- Text files – CSV, tab delimited, fixed width, etc.
The others (Report, Azure DataMarket, Data Feeds, Other Sources) are also pretty clever; future articles will explain more about these.
Tables and sheets – equivalent in PowerPivot
If I import a few tables from Access, here’s the PowerPivot window after import:
Note that each of the four tables I imported gets its own sheet tab. In PowerPivot, tables and sheets are equivalent – you can’t have an empty sheet tab, and you can’t put more than one table on each sheet.
Step two: extend tables with calculated columns
Each table in PowerPivot has a blank column on the far right where you can enter formulas:
Let’s say I want to concatenate two columns together – the SalesOrderNumber column and the SalesOrderLineNumber column. I write the following formula:
A lot of rhyming here:
- The formula language is Excel formulas – notice the use of CONCATENATE, and just in general, the same FUNCTION(param1, param2, …) syntax.
- The autocomplete dropdown – this helps with function names as well as references.
- All columns are available as named range-type references – without any additional effort on my part, I can reference any column by its name. Even the [ColumnName] syntax in square brackets matches the reference syntax from Excel 2007 Tables.
Below is the result:
(Note that I renamed the column after entering the formula – it was named CalculatedColumn1 by default.)
Step three – create pivots
Click the PivotTable button in the PowerPivot window. This drops a PivotTable onto a sheet in the Excel window, complete with field list:
A few clicks and I have a pivot that shows sales grouped by territory:
What about VLOOKUP?
That’s right, I didn’t write any VLOOKUP formulas, and yet my pivot shows a field from the territories table and a field from the sales table. Furthermore, my pivot field list is showing me multiple tables all at once.
That isn’t an illusion – PowerPivot virtually eliminates the need to merge tables together using VLOOKUP before you can perform analysis. I’ll explain in the next instalment, but if you want a preview, read the second half of this post.
Links
Excel extras: introducing PowerPivot
Excel extras: the hidden cost of traditional BI projects
Excel extras: using PowerPivot for BI projects
Spreadsheet skills: right to use MOD?
Professional development
CIMA on demand