Chapter 1 – Why You Will Love PowerPivot

July 7, 2010 at 7:24 PM 3 comments

There are five delicious reasons why you will want to try out PowerPivot…

PowerPivot is a free download that you can easily add to Excel 2010. It is the best product to come out of Microsoft in twenty years.

There are five amazing things that PowerPivot enables. Anyone of these five features would easily justify the purchase price:

  • Nearly a billion rows in Excel. If you have a 64-bit version of Office 2010, you are limited by available memory on the machine (presumably much larger than the old 3GB limit in 32-bit Windows) and by the 2GB File Size limit. However, since PowerPivot can achieve 15:1 data compression, you can open 30GB data sets in PowerPivot. For a theoretical file with 7 columns, I calculate about 995 million rows as a theoretical limit.
  • Join data from Sheet1 and Sheet2 in a single pivot table without doing any VLOOKUPs! Yes, if you are reading this blog, you can probably do VLOOKUPs with your eyes closed. But…VLOOKUPs are slow, and if you are doing five columns of VLOOKUPs on 5 million rows, the recalc times would be astronomical.
  • Pull data from anywhere. From Excel. From SQL Server. From Access. From ATOM. From Oracle. From any ODBC client. Put data from Oracle on Sheet1 and data from an RSS feed on Sheet2. Copy and paste data from Excel into Sheet3. Mash up those data sets with a few clicks.
  • Asymmetric reporting using Named Sets. Say that you currently have columns for 2009 Budget, 2009 Actual, 2010 Budget, 2010 Actual. You would like to report 2009 Actual and 2010 Budget. Pivot tables can not do that. In Excel 2010, the Excel team added Named Sets that allow OLAP pivot tables to create such a report. This sucks for you if your data is regular old Excel data. One benefit of PowerPivot is that your regular old Excel data turns into an OLAP cube by running it through PowerPivot. Thus, Named Sets become viable for you.
  • New calculated fields using the new DAX language. DAX and Excel share 80 functions in common, so if you are familiar with LEFT, RIGHT, MID, INT, ROUND, then you will be comfortable with DAX. However, DAX adds 60 new functions that allow for miraculous calculations. The Time Intelligence functions allow you to compare one day’s sales to all MTD sales, sales from the prior month, MTD sales from the prior year, and so on.

The obvious question: why would PowerPivot be free? Microsoft is making the client version of PowerPivot available for free. There is a server version that you can buy. See? By tempting the 500 million people using Excel, they hope to sell some server versions. I don’t think this is a smart strategy, because the client version of PowerPivot is so powerful, 99% of the Excel people won’t ever need the server version.

In this introductory video, you can see 7 million rows in Excel.


Entry filed under: Chapter. Tags: , , , , .

PowerPivot for the Excel Data Analyst Chapter 2 – Getting Your Data Into PowerPivot

3 Comments Add your own

  • 1. Alex Kerin  |  July 7, 2010 at 11:32 PM

    Great video – finally a succinct ‘what-is’ for powerpivot that seems to have been missing from the MS marketing team.

    Looking forward to the rest.

  • […] will ensure that Excel remains a useful, and perhaps even more serious, dashboard tool. I found this video he made to be a useful introduction to […]

  • 3. greg kramer  |  July 29, 2010 at 2:46 AM

    hate to pick a fight with the excel god but the jury is still out regarding excel to sharepoint…i’m an excel jock…setting up sharepoint isn’t that hard…just follow vidas’s’re good to go…this is how spreadsheets should be…create it->share with your enterprise!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed

PowerPivot for the Excel Data Analyst

PowerPivot Book Cover

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 10 other followers

Recent MrExcel Tweets

%d bloggers like this: