Author: Oscar Cronquist Article last updated on January 22, 2019

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is edited/added or deleted on another worksheet.

The issue here is that a pivot table doesn't know if

  • source data has changed
  • source cell reference has changed

That is why you have to manually change the source cell reference and refresh pivot table, this is very easy to forget. However, this article solves your problems using an excel defined table and three lines of event code.

In this example there are two worksheets, "Data" and "Pivot table", to make it simple. The following image shows you some of the data on worksheet "Data"

Watch a video

Create a dynamic source range

Excel is not smart enough to know when you have added values to your data, the source cell reference won't adjust automatically. However Excel defined tables (Excel 2007 and later versions) has that feature and luckily it is easy to implement.

You will also need event code to refresh your pivot table but first let's build an Excel defined table.

Excel 2007 and later versions

  1. Go to your "Data" worksheet
  2. Select a random cell in your data
  3. Go to tab "Insert" on the ribbon
  4. Press with left mouse button on "Table" button (Ctrl + T)
  5. Press with left mouse button on OK button

Here is the excel defined table:

Recommended article:

Recommended articles

How to use Excel Tables
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Excel 2003 and earlier versions

The named range formula below is dynamic meaning it automatically expands both horizontally and vertically while adding new entries or columns.

  1. Start Name Manager.
  2. Press with left mouse button on "New..."
  3. Type in "referer to:" field.
    =Data!$B$2:INDEX(Data!$2:$65536, COUNTA(Data!$B:$B), COUNTA(Data!$2:$2)+1)
  4. Press with left mouse button on OK.

Recommended articles

How to use the INDEX function
Gets a value in a specific cell range based on a row and column number.

Create pivot table

If you already have a pivot table, skip these steps.

  1. Press with left mouse button on "Pivot table"
  2. Excel 2007/2010: Type table name in "Table/Range" field
    Excel 2003 and earlier versions: Type Rng in "Table/Range" field
  3. Press with left mouse button on OK

Recommended article:

Recommended articles

How to use Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

How to auto-refresh pivot table

The following event-code must be placed in the same sheet as your data is. Whenever you edit/add/delete data, event code is rund.

The downside is that it refreshes the pivot table every time you add data to a single cell, this may slow down your workbook on an older computer.

Read this post if you'd rather refresh your pivot table when you activate pivot table worksheet.

Here is instructions on how to insert the event code to your "pivot table data source" worksheet:

  1. Press with left mouse button on "Developer" tab on the ribbon.
  2. Press with left mouse button on "Visual Basic" button
  3. Double press with left mouse button on "Data" sheet in project explorer window.
  4. Insert event code into worksheet code window to the right of the project explorer window.

VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

The code is copied into the code window for sheet "Data". The Worksheet_Change sub is rund when a cell is changed in sheet (Data).

This line of code refreshes the pivot table on sheet Pivot table whenever a cell is changed on sheet Data.

Press with mouse on any cell in your pivot table and go to tab "Options" to see the name of your pivot table. Make sure to use that name in code above.

Your pivot table is now instantly refreshed whenever a cell value on worksheet "Data" has changed, and the pivot table source data range is adjusted if values have been added/deleted to your excel defined table.

Get Excel *.xlsm file

auto refresh pivot table.xlsm

Get the Excel file