PowerPivot: A Game Changer for Excel Pivot Table Users

Posted by Guest Blogger on Mar 11, 2014

As a CPA and a power Excel user, my goal in Excel has always been to create useful reports and analysis for audit documentation as well as to meet my client’s need for information and reports. I quickly discovered that the best tool in Excel to meet my goals was to learn and master the capabilities of pivot tables. Learning pivot tables has increased my efficiency in almost every project that I have worked on and has been the tool I most often turn to in order to complete projects in a timely manner.

As great as pivot tables are they do have limitations, for example pivot tables do not work with data sets larger than one million rows. Pivot tables also can only work on one data set at a time. If I want to create a single report from multiple data sets, I must use V-Lookup formulas. If I want to perform calculations in my pivot table, I must use Calculated Items and Calculated Fields, which are cumbersome to work with and are not well supported by the Microsoft team.

Microsoft, with the release of Excel 2013, has introduced a new functionality titled “PowerPivot.” As a fan of pivot tables, my interest was piqued almost instantaneously. In his book on PowerPivot author and expert Rob Collie states, “I think PowerPivot offers the same potential wealth-generation effect to Excel users as the PC offered budding programmers like Gates and Allen.”

In order to understand what PowerPivot is and how it differs from pivot tables you first have to understand the architecture of PowerPivot inside the Excel application. PowerPivot is based on Microsoft SQL Server Analysis Services technology using the VertiPaq database that stores all the PowerPivot tables. The VertiPaq database makes use of compression technology, which allows PowerPivot to hold larger amounts of data. The technology used in PowerPivot for Excel has been around for at least fifteen years and is part of the Microsoft Business Intelligence offering of PowerPivot for SharePoint, which has been used by many larger business organizations for their analytical needs. By now offering this well established and tested technology within the Excel application, Microsoft has given Excel power users access to all its capabilities without any additional cost to them and without having to become IT experts.

As you can now conclude PowerPivot is no ordinary Excel spreadsheet, in fact it is not even a spreadsheet at all. PowerPivot has its own window inside of Excel and acts very differently from spreadsheets:

You cannot enter data directly into the PowerPivot window.

There is no longer a million row limitation for storing data.

PowerPivot also has the additional capability of creating relationships between two different data tables thus enabling the creation of a single report, which contain information from two tables. This capability allows the Excel power user to create data models within Excel thus enabling Excel to become a self-service business intelligence solution.

In order to work with data in PowerPivot it must be imported directly into the PowerPivot window where each data import gets its own tab in PowerPivot. PowerPivot can work with CSV, Excel, Access and other databases, including SQL and Oracle. Data must be loaded into PowerPivot either informally by copy and paste or by a more formal source such as a connection to a database.

PowerPivot is a definite game changer for Excel users. What has been your experience with PowerPivot? In my next blog post, I will discuss the best feature of PowerPivot: DAX.