Portable formulas: A PowerPivot benefit you’ll never believe you lived without

Former Excel team member Rob Collie is CTO of Pivotstream and writes the PowerPivotPro blog, describing his experiences adopting Excel PowerPivot.

Hey everybody, last time I posted here I talked about the top 5 benefits that PowerPivot provides to Excel pros. But I left one crucial advantage out, perhaps even the biggest advantage, because I wasn’t ready to explain it clearly. My experiences training Excel pros over the last six months or so, however, have given me better insight into how to explain it. So I’m back to correct that omission.

Traditional spreadsheet formulas are not portable

How much time do you spend in Excel doing basically the same thing over and over again, like calculating “growth versus prior year”?

My bet is you spend a lot of time doing that sort of thing. Maybe even the majority of your time. The reason for that is that Excel formulas are not “portable.”

What do I mean by that? There are two ways in which Excel formulas are not portable:

1. Formulas are “stuck” in their cells. If you write a formula in cell B7 on Sheet1, that formula is not usable in cell C10 on Sheet2 unless you copy/paste it or rewrite it.

2. Formulas do not adjust to changing report shapes. If you write a formula in a report where you are displaying results by Region, and then you need that same formula in a report that displays results by Month, in many cases you can’t copy/paste the formula – it requires “surgery” in order to fit in the second context.

I’ll illustrate with some examples below, but I think you are already at least vaguely aware of those problems. All major spreadsheet products, from VisiCalc to Lotus, Quattro, and Excel, share those limitations. And the dirty little secret is: they don’t actually have to have those limitations.

That’s right, it is 100% possible to have a spreadsheet product that lets you reuse formulas in different places and in different report “shapes.” And a spreadsheet product like that can save you loads of time, while still being simple to use, still feeling like a spreadsheet, and while still using the Excel formula language.

It’s also true that it is not simple to build such a spreadsheet product. It takes a lot of work, which is why you haven’t had one… until now. As I mentioned last time, PowerPivot is 10+ years in the making, built by a second team at Microsoft, and now you finally get to see how much time you can save with “portable formulas.”

Interested? Time for some examples, starting with the nonportable, traditional kind of formulas.

Example: Year Over Year Growth – without PowerPivot

As our example, let’s return to Growth versus Prior Year, also known as YOY (Year Over Year) Growth. If you’re reading this blog, chances are you’ve done a bunch of that in Excel.

Let’s start simple. If you have a table like this:

Simple Excel table

Simple input data

It’s pretty easy to add columns of formulas that calculate YOY growth:

Simple relative reference formulas

Simple relative reference formulas calculate growth vs. prior year

And get a result like this:

Result of calculation

Resulting report

No big deal. Now let’s say your original data is at the monthly level like the following:

Data at monthly level

Complication #1: Month level input data

To get the same results as above, first you need to calculate the total dollars and units for each year before you can calculate the differences. There are a few ways to do this. One is to create another table of results using SUMIF:

Table using SUMIF

SUMIF formulas are 1 way to handle the month problem

Now you can use the same simple formulas from before to handle the year vs. year growth.

You can also calculate percentage of growth by dividing by the prior year’s number:

Calculate percentage of growth

Adding percentage of growth formulas – still easy

You can also use a PivotTable to create the base sums for Dollars and Units, of course, but in the end, it’s the same formulas as above, next to the pivot, for year vs. year growth.

So far, there’s no real need for portable formulas, but the stage is set for the next inevitable complication…

“Those are good results, but now I need to see something different…”

Guess what. Your company didn’t open its doors until July 2001. So the growth numbers for 2002 vs. 2001 are overstating your real growth by comparing 12 months versus 7 months.

To fix that, people now want to see the growth numbers on a “sales per month” basis. And they want the monthly totals included in the final report too, for reference. In other words, they want something like this:

Per month totals and averages table

Now with per-month totals and averages, this version requires formula surgery

First, note that on a per-month basis, dollar growth was completely flat from 2001 to 2002, so we were right to dig deeper.

But now, think about how much work needs to go into a report like this. There’s really no way to reuse the formulas from the first example. Each formula cell circled in green below is a distinctly different formula. Note how the formulas for 2001 and 2002 each have to reference a different number of cells –- 7 months for 2001, 12 for 2002.

Table showing different numbers of months

Different numbers of months mean different formulas for 2001 vs. 2002

But hey, you dig in and you get those formulas written.

Getting the YOY columns filled out is tougher:

Month formulas reference months; years reference years

Month formulas reference months; years reference years

Making sure that month-level formulas reference just month-level inputs, and year-level references year-level, requires a lot of manual adjustment – you are making your formulas “fit” the report shape.

And then, of course, someone asks you to change the report shape. You get more years of data, and they want product type added to the report:

Report with new items added

Hey look! We introduced clothing and accessories in 2003! Get out your formula scalpel!

Now you are faced with major surgery to your formulas. On top of Month vs. Year, now you have to account for making sure the Month level calcs always reference the same product category – Bikes compared to Bikes, not Bikes compared to Clothing. Furthermore, not every product category sold in every month, so you can’t safely fill formulas down, either.

The fundamental idea here is still the same and still pretty simple: calculate growth vs. prior year. But making the spreadsheet do the right thing is anything but simple. It still beats using a calculator, but not by as much as it could.

And once you get this all straightened out, the next need arises, and maybe next you need to be able to filter the report to just show particular sales in a particular country, and not all countries sold the same products in the same years.

Most truly useful formulas require adjustment like this every time you change the shape of your report. Many of you do this all the time.

When you use PowerPivot, this problem goes away forever. In my next post, we will run through the examples again, but with PowerPivot (and therefore with portable formulas).

Rob Collie

Rob Collie is a former member of the Excel team for versions 2003 and 2007, one of the founding engineers of the PowerPivot team at Microsoft, and now CTO at Pivotstream, a cutting-edge adopter of Excel 2010 and PowerPivot. Rob has also been writing the PowerPivotPro blog for the past year and a half, covering his experiences adopting PowerPivot, applying it professionally on an industrial scale, and traveling the country training others on how to adopt it. You can follow him on Twitter as @powerpivotpro.