• Technology
  • CFO.com | US

Spreadsheets: Mission Impossible Completed with PowerPivot

The makers of PowerPivot should be nominated for a Nobel prize, quips Jelen. Why? For one thing, PowerPivot will wrest the power of business intelligence from the IT department and put it in the hands of accounting and finance.

 

Confessions of a PowerPivot Fan

As you read this paragraph, understand that as MrExcel, I might be slightly biased. I believe that the inventors of PowerPivot should win a Nobel Prize. In fact, I believe that the inventors of PowerPivot should win an Oscar, an Emmy, a Grammy, AND a Tony. I believe that the inventors of PowerPivot should win the Luca Pacioli Lifetime Achievement Award for improving the lives of every accountant on earth.

PowerPivot will wrest the power of business intelligence from the IT department and put it in the hands of accounting and finance. People who use Excel have never been offered such a dramatic improvement in Excel. Ever.

If someone had invented a tool that would solve any one of the Big Six problems, it would be amazing. Given that PowerPivot for Excel solves all six of these problems, it becomes the greatest invention for the accounting profession of all time. To recap, PowerPivot pivot tables offer these Big Six benefits:
1. Create pivot tables from more than a million rows of data. 990 Million rows is the current theoretical maximum on a 64-bit machine running 64-bit Excel.
2. Mash up data from multiple worksheets without the calculation expense of using VLOOKUP.
3. Import data from anywhere; text files, Oracle, Sybase, Teradata, SQL Server, Atom, Informix, IBM DB2, Azure Data Mart, Excel, or anything else that supports ODBC or OLEDB.
4. Create asymmetric pivot tables (2010 actuals vs. 2011 budget).
5. DAX measures run circles around calculated pivot fields. They solved Kelly’s problem in this article and can do far more powerful calculations.
6. New time intelligence functions handle fiscal years, parallel periods and more. My heart races just looking at the list of new date and time functions in Figure 12.

Fig. 12

MrExcel 4-13Fig12

 

A Word About The Pricing Model

There are two PowerPivot tools.
The tool described in this article and in my book is PowerPivot for Excel. This tool is absolutely free for anyone who has Excel 2010. Microsoft is willing to give away all of this power to 750 million Office users for free.

There is also a server version of PowerPivot that runs with SharePoint. The server version adds some bells and whistles for the IT team and provides the ability to automatically refresh the reports every night. The server version is not free.

As long as you are willing to have an administrative assistant open Excel every morning and click Refresh All, you can access all of power of the Big Six features by using the free PowerPivot for Excel.

PowerPivot saved the day for Kelly’s pivot table, but this use of PowerPivot barely scratches the surface of the power available in PowerPivot.

CFO Contributing Editor Bill Jelen fell in love with PowerPivot the day that he saw it handle 100 million rows in Excel. He is the author of 32 books about Excel including the best-selling PowerPivot for the Excel Data Analyst. Bill will be discussing Pivot Tables in a CFO Webcast on May 5, 2011. You always can win a copy of one of Bill’s books if your question is selected as a topic for an upcoming column. Post your question to the community content block at the right.

 

Discuss

Your email address will not be published. Required fields are marked *