In a previous post: How to create a dynamic pivot table and refresh automatically in excel 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 removed from a sheet.

In this example there are two sheets.

  • Data
  • Pivot table

Create a dynamic named range

Excel 2007/2010 users can create a table. It is dynamic by default.

  1. Select "Data" sheet
  2. Select cell range
  3. Click "Insert" tab on the ribbon.
  4. Click "Table" button (Ctrl + T)
  5. Click OK.

Excel 2003

  1. Start Name Manager.
  2. Click "New..."
  3. Type in "referes to:" field.
  4. =Data!$B$2:INDEX(Data!$2:$65536, COUNTA(Data!$B:$B), COUNTA(Data!$2:$2)+1)
  5. Click OK.

 

 

 

Create pivot table

  1. Click "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. Click OK

 

How to auto-refresh pivot table

  1. Click "Developer" tab on the ribbon.
  2. Click "Visual Basic" button
  3. Double click on "Data" sheet in project explorer window.

  4. Paste vba code into worksheet code page to the right of the project explorer window.

VBA

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 executed when any 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.


Pivot table is instantly refreshed whenever a cell is changed.

Download example file

auto refresh pivot table.xls
(Excel 97-2003 Workbook *.xls)