Update an Excel Pivot Table – Even When the Source Data Changes (+ video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

Did the data in your pivot table change??? Here’s how to update pivot table data (even when its source data changes!).

How to Refresh a Pivot Table in Excel

WATCH: How to Update Pivot Table When Source Data Changes in Excel – Tutorial

If the data linked to your pivot table has changed, you may want to also update your pivot table. This is also referred to as refreshing your pivot table.

To refresh a pivot table means to update your pivot table to reflect what current data is linked to it.

There are a few ways you can refresh a pivot table. With your cursor inside the pivot table you can:

1. Right-click and select “Refresh” from the options
   • 
2. From your Excel Ribbon click the button labeled “Refresh” from the Pivot Table Analyze tab (look in the Data section) to update all pivot tables in the current workbook
3. To update only the pivot table in which your cursor is in, click to open the Refresh options (from the Excel Ribbon), then click the one that says “Refresh”
4. Use keyboard shortcut Alt+F5
   • TIP: in some keyboards, you may have to press the “fn” key along with your F5 key

But what if you need to link your pivot table to different data?

How to Change the Pivot Table Data Source

There are times that you may need make a change to the data source that is linked to your pivot table.

The data that your pivot table is linked to is called its Data Source. Sometimes referred to only as the Pivot Table Data Source.

It could be that you need to expand the data source to include more rows or columns.

Or you may need to link your pivot table to a completely different data source.

Either way, to change the pivot table data source, you will go back to the Analyze tab in your Excel Ribbon, but this time click on Change Data Source. If the drop-down opens, select Change Data Source.

When you do this, you’ll see the current data source selected (it should be framed by small dashes).

Its cell address will also be displayed in the dialog box inside the “Table/Range” field.

To update the data range, you can either manually update the cell address or delete it and, using your cursor, select your new data range. Click OK when done.

BONUS: Help Avoid Missing New Rows or Columns in Your Data Source

In certain situations, one way that you can help make sure you don’t miss any rows or columns of data if your data source is changed, is to format your data source as an Excel Table.

What’s so special about this???

When a Table is created in an Excel sheet, the data in it is grouped and assigned a name (Table1, Table2, etc).

You can see this both in the Name Box and by opening the Name Manager.

One of the features of a Table is that Excel recognizes that you want all of the data inside the Table grouped together.

This is important, because now, as you add any new rows or columns of data to your table, your pivot table will automatically pick them up.

To convert your data into a table, make sure your active cell is inside the data range you want to convert. Then, you can either:

• Use the Ctrl+T keyboard shortcut OR
• In your Excel Ribbon, go the Home tab and, in the Styles section, select Format as Table. From the dropdown, select your preferred table style and click OK.

To help make sure that your pivot table updates correctly, use the Table name in the “Table/Range” field.

TIP: If pasting new data to your Table, make sure you are not pasting the new data over the entire table. This will cause its name to change, breaking the link to your pivot table. Instead, paste new data directly into the data part (not the headers). If you have a new header, paste the headers first, then the data.

---

FYI, this tutorial was created using the desktop version of Excel in Microsoft 365.

---