Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition

Chapter 22

Working with PowerPivot

With great power comes great responsibility.

—Spider-Man’s Uncle Ben, as written by Stan Lee, author, 2000

PivotTables have been one of the best tools for the average businessperson to analyze data. However, a new and more powerful tool is available. If you have Excel 2010, you can download from Microsoft the free PowerPivot add-in. This tool is the PivotTable plus much more.

Although PivotTables and PowerPivots are similar, you need to understand major differences in their power. PowerPivot differs from PivotTables in ways that you may at first find frustrating.

You should continue using PivotTables in Excel 2010. For most single-dataset problems, they are still the tool of choice. PowerPivot expands your world of analytics because it accesses and joins very large databases and calculates much faster. This gives you a larger and more integrated view of your organization—how different parts of your organization work together. If you have used PivotTables in the past, you will definitely want to learn when to use PivotTables and when to use PowerPivots.

Basic PowerPivot Concepts

Like PivotTables, PowerPivot connects to a data source and calculates a cross-tabulation that displays on a worksheet. Unlike PivotTables, PowerPivot can analyze very large databases containing millions of rows of data. It can automatically detect when database tables have common fields and join the tables. And considering that PowerPivot can do this with databases having ...