You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Former Excel team member Rob Collie is CTO of Pivotstream and writes the PowerPivotPro blog, describing his experiences adopting Excel PowerPivot.
Hello again, I’m back for part 2. If you missed part 1, I highly recommend reading it first, because this won’t make much sense on its own.
OK, now we’re going to replay part 1, but this time we are going to use PowerPivot.
Let’s start with a basic PivotTable that looks like one of our first steps in part 1. You check four check boxes, and here’s what you’ve got:
No formulas needed so far… just 4 clicks and the Month/Year/Category problem from before is covered
I did NOT need to write a formula in order to get “Sum of Sales” in the pivot – I just checked the Sales check box, and it automatically handles some of the problems from above.
But I CAN write a formula for Sales if I want. It’s a pretty simple formula:
For space considerations, I’m not going into the details of that formula, but it should be pretty easy to get the idea. It yields the same results as above, but it ALSO adds a new check box to the field list:
If you write a formula for sales, it becomes its own check box
For extra clarity, I like to express my measure formulas as “[MeasureName] = <formula>.” In this case, that is:
[Sales] = SUM(Sales[SalesAmount])
That behavior – the formula getting saved as its own new field (checkbox) provides a lot of benefit. The simplest demonstration of said benefit is this: You can now use your [Sales] formula in other pivots, even on different worksheets!
For example, here’s another pivot showing [Sales] broken out by DayOfWeek, and [Sales] did NOT need to be rewritten, all I needed to do was click the check box:
Reusing a PowerPivot formula in other pivots: You just click the check box
For a formula as simple as [Sales], this may not be a huge deal. But as your formulas get more complex, it is a MONSTER time-saver.
Formulas being saved as new fields will come in handy in our next step, too, which is calculating “Dollars per Month.” I can use the [Sales] formula I defined above as an input to my new formula!
[$ per Month] = [Sales] / COUNTROWS(DISTINCT(Sales[MonthNum]))
Which, given the definition of [Sales] above, is *precisely* the same thing as rewriting the [Sales] part as a SUM:
[$ per Month] = SUM(Sales[SalesAmount]) / COUNTROWS(DISTINCT(Sales[MonthNum]))
But using [Sales] instead of rewriting the SUM saves me time. That savings becomes more pronounced when your original formula is more complex, of course. And as an extra benefit, if you need to change the way [Sales] is calculated later, you can do that in one place, and all of your other formulas that depend on it will also pick up the change!
(As incredibly useful as they are, I don’t have room here to describe the new functions COUNTROWS and DISTINCT, but it should be easy to get a feel – we are taking the distinct count of months and using then dividing our sales amount by it. If you want to know more, I highly recommend you check out this example, it includes short video tutorials.)
The formula just works. It does NOT need to be modified to when I add Month to the pivot – it automatically adjusts itself! More on this later.
$ per month formula – it just works
Let’s round out the example with a formula for “$ per Month Prior Year”.
[Prior Yr $ per Month] = [$ per Month](DATEADD(Calendar[Date],-1,Year))
And then use that to define $ per Month Pct Growth vs. Last Year:
[$ per Month Pct Growth vs. Last Year] = ([$ per Month]-[Prior Yr $ per Month])/[Prior Yr $ per Month]
Yielding our final result:
Same result as traditional formulas but in a fraction of the time
If, later on, I needed to change the formula for [Sales] to be something different, like to subtract out [Credits] or something like that, that fix will automatically flow through to [$ per Month], [Prior Yr $ per Month], and [$ per Month Pct Growth vs. Last Year]. All of my reports will just update automatically, no surgery required.
Benefits 1 and 2 would be enough to make this a valuable addition to Excel. But benefit 3 is probably the most magical of them all.
I can change the layout of the report above by removing Category, and then I even filter out some years, my formulas still work:
Dramatic rearrangement and filtering of the pivot, and formulas still just work
Even more dramatic: Even though my “$ per Month” formula includes a calculation of the number of months, the formula works even when Months are NOT displayed on the pivot:
Even though the formulas calculate number of months, they still work without months displayed!
Last, and this is purely a bonus at this point: If you adopt PowerPivot for SharePoint, you can reuse these measures across workbooks:
Define measures in a “core” workbook, use them in pivots in “thin” workbooks
Seriously. The measures I defined above like [$ per Month] can be reused in pivots in completely different workbooks. And again, I don’t need to rewrite them to do that, I just “connect” my new workbook (I call that workbook a ‘thin”) to the original workbook (which I call a “core”), and then just check a check box in the field list. Also, if I later change the formula in my original workbook, new results will show up next time I refresh the pivot in the other workbook.
I could write an entire post on this, so if you want to know more, please check out this blog post.
As I said in part 1, I believe that a very large percentage of the time spent in traditional Excel is consumed by compensating for the lack of formula portability. Everything just flows in PowerPivot though, so you can focus your time on designing smarter formulas, and then using those formulas basically everywhere.
Not only does it save tremendous amounts of time, but it opens up new lines of thinking that you never would have thought to pursue before. The rewriting and reshaping of formulas would just have been too painful without PowerPivot. What used to be your entire afternoon is now as simple as a few clicks.
The resulting freedom has literally become my entire career. At Pivotstream, our small team of analysts performs analysis and reporting work for dozens of clients, and the only tool we use is Excel with PowerPivot. That sort of scale simply was not possible before. If you’d like to get started, I’ve collected some links below.
Getting started With PowerPivot
The Great PowerPivot FAQ is a collaboration by myself, members of Microsoft, as well as other leading experts in the Excel community.
Case studies and in-depth examples
Observations from hands-on PowerPivot training
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 also follow him on Twitter as @powerpivotpro.