Excel Cookbook

Chapter 16. Power Pivot and the Data Model

By default, you can base a PivotTable on only a single table, which is problematic if your data is in multiple related tables. Furthermore, Excel’s row limits can make handling large datasets tricky.

If you’re using Excel for Windows, a solution to these limitations is to use the data model: a collection of related tables saved with your workbook. Using the data model has the following advantages:

• You can create a PivotTable that uses data from multiple related tables.

• There’s no row limit; you can import millions of rows from multiple data sources.

• Using the data model is faster for large datasets because of its efficient compression algorithms.

The recipes in this chapter show how to work with Excel’s data model using Power Pivot: an add-in that interfaces with the data model. Areas covered include adding data to the data model; defining relationships between tables; creating calculated columns, measures, and key performance indicators (KPIs); and using PivotTables and Cube formulas to retrieve and analyze data-model data.

16.1 Installing Power Pivot

Problem

You’re using Excel for Windows and want to install the Power Pivot add-in so you can interact with Excel’s data model.

Solution

If you’re using Excel for Windows, you can use the Power Pivot add-in to interact with the workbook’s data model. You enable Power Pivot as follows:

1. Choose File ⇒ Options.

2. Select Add-ins.

3. In the Manage box at the bottom of the screen, ...