Find and Fix Pivot Table Source Data

After you create a pivot table, you might add or change records in the source data. When you refresh the pivot table later, sometimes the new data doesn’t show up. Here’s how to find and fix the pivot table source data, so the correct data appears.

Video: Find and Fix Pivot Table Source Data

In this video, you’ll see how to find and pivot table source data. There are written steps below the video.

Find the Source Data

If a pivot table isn’t refreshing correctly, follow these steps, to find its source data:

Select any cell in the pivot table.
On the Ribbon, under the PivotTable Tools tab, click the Analyze
tab
In the Data group, click the top section of the Change Data Source
command.

NOTE: If you’ve bought my Pivot Power Premium add-in, click Pivot Table Info, then click Go to Source Data.

Source Table/Range

The Change PivotTable Data Source dialog box opens, and you can see
the the source table or range in the Table/Range box.

In this example, the Table/Range is a reference to a sheet and a static range of cells:

Orders!$A$1:$H$9

Behind the dialog box, you can see the source range on the worksheet,
surrounded by a moving border.

In the screen shot above,

the source data range ends at row 9, and
a new record has been added in row 10.

The new record won’t be included in the pivot table, unless the Data Source Table/Range is adjusted.

Adjust the Source Data Range

Because this pivot table’s source data is a static reference to a specific
sheet and range, it doesn’t adjust automatically if new data is
added.

In the screen shot above, the Table/Range refers to Orders!$A$1:$H$9,
and the latest data is in row 10.

To manually adjust the static source range:

In the Change PivotTable Data Source window, change the ending
row number, in the range reference:
- to Orders!$A$1:$H$10
Click OK, to close the window.
Then, refresh the pivot table, and the new data will appear

Dynamic Source — Excel Table

Instead of using a static range as the pivot table’s data source,
a better solution is to create a dynamic range, based on a named Excel
table. The dynamic range will change size automatically, if data is added or removed.

Watch this video to see how to set up a named Excel table, and then change the pivot table source, to refer to that named table. There are written steps on my Contextures website.

_______________________