Top 5 Ways PowerPivot Helps Excel Pros

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print



This blog post is brought to you by Rob Collie CTO at PivotStream. Former co-founder of the PowerPivot team and Lead PM for Excel at Microsoft, broad-spectrum geek and cruncher of sports stats.


Hello Excel Pros! I’d like to start by saying this: You are my kind of people.

I’ve met hundreds of you over the years that I worked on Excel (and other teams) at Microsoft, and I meet more of you all the time today. I like to think of myself as one of you, in fact. I know the “secret,” that the world basically runs on the efforts of Excel pros. So when we meet, we often experience instant kinship: the millions of us are like a secret society that operates in plain sight.

I no longer work at Microsoft, but my career is more dedicated to Excel than ever before, thanks to this little thing called PowerPivot. I now make my living applying Excel, PowerPivot, and related tools for a wide variety of clients. You may have heard of PowerPivot by now, but you probably have not heard a concise answer to this question: Why should I take the time to learn something new?

I volunteered to do this guest post as a means of answering precisely that question, because I think it’s long overdue. PowerPivot is like a giant amplifier for your Excel powers. It was built for YOU. And while it’s great that I talk to a lot of SharePoint and SQL pros all the time about it, and they can (and should) play an important cooperative support role, well, it doesn’t change their lives all that much.

It sure can change YOUR life though. It has absolutely changed mine, and I didn’t have to reinvent myself in the process. I hope you are intrigued by that.

So, without further delay, I’m gonna give you my top 5 reasons as crisply as possible, with links to more information as backup. (And if you folks are interested, I will come back and give you reasons 6-10.)

#1: PowerPivot is NOT new to you, it’s just an extension of what you know

As I said above, PowerPivot is an amplifier of the skills you already have. It is NOT a brand new toolset – it just makes Excel more powerful. My last role at Microsoft, in fact, was designing a lot of the PowerPivot experience so that it is familiar to people like us.

So when Mr. Excel, Bill Jelen, said that “PowerPivot is the best thing to happen to Excel in 10 years,” that gave me a huge sense of satisfaction. Note that he said “best thing to happen TO Excel,” not “best alternative to Excel,” nor “best Excel-like substance.” It is not a new language to learn – it’s more like the language you love… just got a few killer new words J

For instance, here is a PivotTable I built WITHOUT PowerPivot:


Traditional Excel PivotTable

…and here is a PivotTable that I built WITH PowerPivot:


PivotTable Created with PowerPivot – Can you spot the differences?

A few things worth noting:

· Both pivots appear in the Excel grid – and in fact, both are real Excel pivots (the PowerPivot version IS drawn/laid out/owned by Excel just like all pivots)

· Both pivots have the same options – right click, Ribbon, whatever, they all offer the same things

· Field Lists are very similar – no, they are not 100% the same, because the PowerPivot field list offers a little bit more. Like a Search box. And dropzones for slicers. I doubt you will feel the need to complain about those deviations, nor will you find their purposes mysterious J

· I like crunching sports stats – it’s a bit of a theme for me actually, and a great data set to show off PowerPivot against, like here for example.

Another example – formulas are of course the core building block of all spreadsheets. Let’s take an IF formula being written in Excel, as compared to Excel with PowerPivot. Can you spot which is which?

clip_image006 clip_image008

Which is Excel By Itself, and Which is Excel with PowerPivot?

That’s right, they are exactly the same. Except that, as above, PowerPivot adds some new wrinkles, including dozens of new functions. Like one of my new favorites, CALCULATE(), which is basically a turbocharged SUMIF.

Remember, it’s Excel, with PowerPivot. It was built by Microsoft, as a cooperative effort between the Excel and SQL teams. And it is already familiar to people like us.

#2: The desktop version is 100% FREE

Yeah, that’s right. It’s free. Every feature of PowerPivot, with respect to the power it adds to Excel on your desktop, is 100% free. (Only the server version of PowerPivot costs money, and you are never forced to adopt that part, although you probably will want to eventually).

You can download it right now, only takes a minute or two. (Be sure to check what version of Excel 2010 you are running first, and grab 32 or 64 bit to match).

All you need is Excel 2010. And even Excel 2010 is available as a trial.

#3: Large data volumes, small files, amazing speed

Right now, in a single Excel workbook on my desktop, I am working with over 97 Million rows of data. 97 Million… and it crunches numbers faster than many spreadsheets far less than 1% that size.

This data set spans ALL sales of a very common household product, from ALL manufacturers, across the ENTIRE US, over ALL retailers, for two years. And there it is, sitting there at my fingertips, right next to my email, my 17 web browser windows, and 5 other open spreadsheets:


97 Million Rows of Data in the PowerPivot Window
(Notice Normal Excel Window Behind It)

(Because this is real data for a real client, I’ve had to “fuzz out” some aspects of it.)

Yes, in order to hold that much data, it must live in its own dedicated PowerPivot window. But that window behaves just like Excel – sheet tabs, autofilter, scroll bars, calc columns. And the contents of that window are tied to and saved in a regular Excel workbook – the relationship of this window to the main Excel window is very similar to the VBA window’s relationship (if you are familiar with macros).

In practice, this means a couple of things. One, the size of a data set is no longer an automatic “disqualifier” of Excel as a tool for analyzing and reporting on it. Many companies’ entire all-time sales histories, for instance, may well fit in a single workbook, and crunch the numbers faster than any database server. You truly can take your Excel abilities up to the industrial scale.

Two, it reduces the time you spend “porting” spreadsheet logic to other data sets, and maintaining sets of workbooks that all do basically the same thing. With better data capacity and performance, it’s suddenly an option to put everything into one “master” file and then filter as needed to focus on specific corners of the business.

One more thing: the compression is AMAZING. I’ve had data sets shrink as much as 50x when I imported them into PowerPivot. That’s extreme, but I commonly see 10x, 15x, and 20x compression. Here is an example.

#4: Multi-table pivots, the dawn of “Analysis Mashups,” and the end of VLOOKUP?

OK, how many times do you get a data source that looks like this – a bunch of different tables of data, a different number of rows in each table, and you need information from each table all pulled together into one analysis?


Sales Table with 60k Rows

clip_image014 clip_image016

Products (600 Rows) and Customers (18k Rows)

And of course, there are six other tables in that data set as well, I’ve just chosen to show three. We all know what this means: VLOOKUP. Lots and lots of VLOOKUP. Before you can do anything, you have to get all the columns you care about all merged into one table.

That’s where Excel ninjas start to feel more like Excel monkeys. It takes forever, it isn’t exactly intelligent work, AND when you’re done, it tends to slow down recalc of your spreadsheet quite a bit. VLOOKUP is one of the slower Excel functions to execute, because it forces Excel to behave more like a database (which it is not) and less like a spreadsheet (which it is).

In practice, all of those obstacles tend to limit the amount of cross-referencing you can do… or the amount you can stand, frankly. One of my personal nightmares used to be someone coming up to me and asking “hey, can we see the Numbers for X broken out by Crazy-Random List Y?”

That same process is now actually FUN with PowerPivot. I go looking for data sets that I can cross-reference with my core data set. That’s because I have not written a VLOOKUP in months. Here’s why…


PowerPivot Lets You Relate Two Tables, No VLOOKUP Required

Yep, you essentially just tell PowerPivot, once, which columns “match” between two tables, and from there on out, you can analyze data across both tables as if they were one table.

For instance, let’s take another look at the PivotTable field list when I have all of those tables above loaded into PowerPivot:


PowerPivot Puts All Tables in One Field List

OK, so all of the tables (sheet tabs) in your PowerPivot window, and all columns in those tables, appear as fields in your field list! That’s not just a visual convenience though – you can use fields from different tables in a single pivot.

For example, let’s check fields from the Products and Sales tables and see what we get:


Field From Sales Table Broken out by Field From Product Table

That’s right, ANY field from the Product table can be used in conjunction with ANY field from the Sales table, because we created a relationship between those two tables in the previous step. But even that’s not terribly necessary. Here’s what happens if I use a field from another table that does NOT have a relationship to Sales:


PowerPivot Detects That You Used Fields from Unrelated Tables

OK, so it warns me, that’s nice. But what happens when I click that “Create” button?

clip_image026 clip_image028

Click Create -> PowerPivot Detects and Creates Relationship, Pivot Refreshes

It’s amazing what a difference those features (relationships, multi-table pivot field list, autodetection) make in practice. I went from dreading multi-table scenarios, to actively seeking them out because the results have such a jaw-dropping effect.

For example, I took that sales data from above, and then found some temperature data on the web, that recorded average temperatures for different areas of the country (and the world), by month:


Temperature Data from the Web

Copy/paste that table into the PowerPivot window, relate it to the Sales table, and guess what? I’m suddenly able to see Sales patterns as broken out by the temperature that was being experienced at the date and time of the sale:


Sales Data Sliced by Temp Range

If you want to know more about that “Temperature Mashup” example, you can read about it and watch a video here. You can even download the entire workbook here and try it out yourself.

#5: Professional-grade formulas in Pivots

Yes, for a long time now, there has been a feature that lets you add formulas (calculated fields) to your pivots. And for a long time now, well, that feature has never been improved. It has remained limited for a long time, nowhere near the richness of the formulas you can write in the regular Excel grid.

Elsewhere at Microsoft, however, there has been a team building and selling a pivot-style calculation engine for about fifteen years. It’s the market leader in fact, within its target industry. And with PowerPivot, that engine has been dropped into Excel, retrofitted so that you can use Excel formula syntax against it.

It’s as if the Excel team spent 15 years improving the Pivot calculated fields feature, and then sprung it on us all at once.

You remember the pivot above? It had a “Qty per Day” field in it. Here it is again as a refresher:

clip_image032 clip_image034

Notice the “Qty per Day” Field? That’s Only Possible with PowerPivot

My sales data did NOT have a field like that, so I had to calculate it myself. And you can’t do that with a calculated column in the source data, you have to do it in the pivot itself. Impossible to do before PowerPivot, but now it is. Here’s the formula:

SUM(Sales[OrderQuantity]) / COUNTROWS(DISTINCT(Sales[OrderDateKey]))

This formula says “sum up the [OrderQuantity] column from within the Sales table, then divide that by the distinct number of dates in the Sales table.” (COUNTROWS and DISTINCT are two new functions added by PowerPivot, and you can do amazing things with them.)

And that formula is always applied, once per cell, just in the context of that pivot cell. So for instance, the 14.9 above is actually “the qty of all Accessories sold on Hot days, divided by the number of Hot days.” That’s because the entire pivot is sliced to Hot, and the 14.9 cell corresponds just to Accessories.

Even better, I can rearrange my PivotTable, and the formula still just works:


Completely Different PivotTable Layout, Formula Still Just Works

That’s just scratching the surface though really. Here are some other quick examples of pivot formulas I’ve written in Excel with PowerPivot:

· Custom Pct of Total – you can do this today in pivots, but with PowerPivot, you have 100% control of what the “total” is – whatever field you want. The field in question doesn’t even have to be in use currently on the pivot!

· Fixed filters – imagine being able to write SUMIF formulas in a pivot and you get my idea. A field that ALWAYS only shows you total sales where Category = “Mountain Bikes” for instance, even if the Category field isn’t part of the pivot.

· Running sums – oh yes, you can do these too.

· Moving averages – average over last 30 days, anyone?

· Formulas that change based on filter – wanna use a different formula for one product category versus another? Wanna use a user’s slicer selection as an input to a formula? I’ve done both.

· Year to date, growth since prior period, growth since same period last year – I’ve done all of those and more. There are about 40 new functions in PowerPivot that deal with time intelligence alone.

· Formulas that do different things at different levels of the pivot – at the lowest level you do X, but on SubTotals and Grand Totals, do something different? One common example of this is to intentionally return BLANK() for totals because they don’t make sense at those levels. Another is that I want my lowest level determined by MAX(), and higher levels determined by SUM().

· Formulas that reference other formulas – I can write a killer formula for, say, “Bike Only Sales” and then later on, I can reference “Bike Only Sales” when I create my “Bike Only Sales Pct Growth” field, without having to copy the original formula.

Preview of Reasons 6-10: Take it to the Web!

OK, now imagine taking all of those tools above, building an incredibly flexible, fast, and useful workbook, and then, with a simple Save As, turning it into a web application that looks like THIS in the browser:


One of our Production PowerPivot Web Apps at Pivotstream
(Again with sensitive information redacted)

You never have to do that with your PowerPivot workbooks, of course, but if you do, it does convey a large number of benefits. Our entire business model at Pivotstream, for instance, simply would not work without the server benefits of PowerPivot.

Anyway, I’m saving that for the next post. But if you want to read more about this in the meantime, feel free to do so here.