Rob Collie, chief technology officer at software company Pivotstream, continues his series on Excel’s exciting add on program, PowerPivot.
In the last instalment, I gave you a brief tour of what PowerPivot looks like once you have downloaded it on top of Excel 2010. With that out of the way, I started to hint very briefly at some of its features that will revolutionise the way you use Excel. Now we can really dive in to the good stuff.
Create once, use forever
Let’s say you have a 60,000 row sales table that looks like this:
And a separate, 600 row products table that looks like this:
And you want to produce a report that looks like this:
In normal Excel, you’d have to write two VLOOKUP formulas in the sales table: one to fetch the ProductLine column from the products table, and one to fetch SizeRange from the products table.
That becomes tedious very quickly, especially if there are many columns in products that you want to use. There are other drawbacks as well that I’ll cover below.
In PowerPivot, however, you only need to connect the sales and products tables one time. Ever.
Just like with VLOOKUP, you need to have a column in each table that matches. In this case, that is ProductKey in both tables (although the column names themselves do not have to match, nor do the matching columns have to be at the left side of their respective tables):
Then you right click that column in either table, and select Create Relationship:
The next dialog box is very straightforward. It simply asks you to specify the matching columns between the tables:
Click Create, and now, in a pivot, any column in products can be used to group or filter data from sales. Not just the two fields from the report above (ProductLine and SizeRange), but any of them.
Just check the checkboxes you want from the field list. Columns like Color for instance:
Automatic relationship detection
PowerPivot even includes an automatic relationship detection feature. If you add fields from unrelated tables to the same pivot, you will be prompted by the field list:
And if you click Create, PowerPivot will scan those tables looking for a relationship to create:
That auto-detect feature works a lot better than you’d expect. That said though, I recommend just creating the relationships yourself using the manual ‘Create Relationship’ dialog I showed first. Even the manual version is incredibly fast and simple, and gives you total control without the 1% chance that the automatic feature does something incorrect.
Benefits of relationships over VLOOKUP
In addition to being less tedious than VLOOKUP, relationships offer the following benefits:
1. Relationships do not slow down your spreadsheet – Even a 10,000 row data set can run pretty slowly when you have a lot of VLOOKUPS. (Which often drives Excel pros into Access, as described in this post contrasting PowerPivot with Access.) Relationships however don’t slow things down at all, and function quite well even with hundreds of millions of rows.
2. Relationships auto-adjust to changes in table size – as your sales and product tables pick up new rows over time, relationships automatically adjust to account for the new rows. With VLOOKUP, you often have to fill down your formulas manually.
3. Relationships show you the ‘holes’ in your data – with VLOOKUP, if for some reason you have no sales on a specific date, there’s no way to make your pivot show you a zero for that date. That’s a pretty simple example of a larger problem – VLOOKUP masks gaps in your data, and relationships do not.
How about SUMIF formula in a Pivot?
As useful as relationships are, their power doesn’t compare to the next benefit I am going to show you. PowerPivot comes with a formula engine that lets you add calculations to pivots.
The engine isn’t limited to the formulas offered by calculated fields in Excel, either. They’re much better than that, and worth many articles in their own right.
How about a formula that only shows you sales for weekends, no matter how the pivot is filtered? Something like ‘sales just on weekends?’
See that? The pivot contains one column displaying total sales (across all days) and another column simultaneously acting as if the pivot were filtered to weekends only! This wasn’t possible before PowerPivot.
I will save most of the details for the next article, but for now, here is a glimpse of the formula for weekend sales:
=CALCULATE(SUM(Sales[ExtendedAmount]), Calendar[IsWeekend]=1)
The two items in bold are just references to columns in the PowerPivot tables, so the formula is actually pretty simple.
And the CALCULATE function, while new, ‘rhymes’ a lot with SUMIF – it’s a supercharged version of SUMIF.
Read this post if you would like to know more between now and my next instalment for Insight.
Links
Professional development
CIMA on demand
Spreadsheet skills