Creating a basic pivot table

Keeping up with changes in the data source

Let’s go back to the family portrait analogy. As years go by, your family will change in appearance and might even grow to include some new members. The family portrait that was taken years ago remains static and no longer represents the family today. So, another portrait needs to be taken.

As time goes by, your data might change and grow with newly added rows and columns. However, the pivot cache that feeds your pivot table report is disconnected from your data source, so it cannot represent any of the changes you make to your data source until you take another snapshot.

The action of updating your pivot cache by taking another snapshot of your data source is called refreshing your data. There are two reasons you might have to refresh your pivot table report:

• Changes have been made to your existing data source.

• Your data source’s range has been expanded with the addition of rows or columns.

The following sections explain how to keep your pivot table synchronized with the changes in your data source.

Dealing with changes made to the existing data source

If a few cells in your pivot table’s source data have changed due to edits or updates, you can refresh your pivot table report with a few clicks. Simply right-click inside your pivot table report and select Refresh. This selection takes another snapshot of your data set, overwriting your previous pivot cache with the latest data.

Dealing with an expanded data source range due to the addition of rows or columns

When changes have been made to your data source that affect its range (for example, if you’ve added rows or columns), you have to update the range being captured by the pivot cache.

To do this, click anywhere inside the pivot table and then select the PivotTable Analyze tab in the ribbon. From here, select Change Data Source. This selection triggers the dialog shown in Figure 2-33.

FIGURE 2.33 The Change PivotTable Data Source dialog enables you to redefine the source data for your pivot table.

All you have to do here is update the range to include new rows and columns. After you have specified the appropriate range, click the OK button.

Note that if you format your pivot table source data as a table by choosing Home | Format As Table; Insert | Table; or pressing Ctrl+T, the Pivot Table Source range will automatically expand as the data grows. You will still have to click Refresh to pick up the new rows.