Keeping Up with Changes in Your Data Source

Let's go back to the family portrait analogy. As years go by, your family will change in appearance and may 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 will have to be taken.

As time goes by, your data may 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 date 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 why you may 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.

These two scenarios are handled in different ways.

Changes Have Been Made to Your Existing Data Source

When changes have been made to your data source that do not affect the range of your data source, your pivot table report can be refreshed with a few clicks.

Choose a cell in your pivot table report. From the pivot table toolbar, choose the Refresh Data icon. This is the icon with the red exclamation point, as shown in Figure 2.27.

Figure 2.27. If a few cells in your source data have changed (but the range of the data has not changed), choosing the red exclamation point will refresh the data in the pivot table.

NOTE

There are two other ways to refresh data. You can refresh data by selecting Refresh Data from the Data menu. You can also right-click on your pivot table report and select Refresh Data.

Your Data Source's Range Has Been Expanded with the Addition of Rows or Columns

When changes have been made to your data source that affect the range of your data source (that is, you've added rows or columns), you will have to update the range being captured by the pivot cache.

To do this, right-click on your pivot table report and select PivotTable Wizard. You will start in step 3 of the PivotTable Wizard, so choose the Back button to return to step 2 of the wizard.

In step 2, choose the Browse button, as shown in Figure 2.28, to respecify the new location of your data.

Figure 2.28. If you've added new rows, new columns, or have changed the field headings in your data, you need to respecify the data source using step 2 of the wizard.

Update your range to include new rows and columns and then choose Finish. Your pivot table report will now include your new data.

TIP

Selecting just the columns in your data source as the range (as in $A:$K) will capture all 65,536 rows of the current spreadsheet in your pivot cache. Although this does take up a bit more memory, you will be able to add new rows to your data source and refresh your pivot table report without readjusting your range.