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 a specific 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 a small amount 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"

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. Click "Table" button (Ctrl + T)
  5. Click OK button

Here is the excel defined table:

Recommended article:

Excel tables

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

Comments(0) Filed in category: Built-in features, Excel, Excel table

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. Click "New..."
  3. Type in "referer to:" field.
    =Data!$B$2:INDEX(Data!$2:$65536, COUNTA(Data!$B:$B), COUNTA(Data!$2:$2)+1)
  4. Click OK.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

Create pivot table

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

  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

Recommended article:

Excel – Pivot tables

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.

Comments(3) Filed in category: Built-in features, Excel, Pivot table

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

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. Click "Developer" tab on the ribbon.
  2. Click "Visual Basic" button
  3. Double click 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 executed 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.

Click 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.

Recommended article:

How to create a dynamic pivot table and refresh automatically in excel

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]

Comments(17) Filed in category: Excel, Pivot table

Change pivot table data source using a drop down list

In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]

Comments(18) Filed in category: Drop down lists, Excel, Pivot table

Analyze trends using pivot tables

Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]

Comments(2) Filed in category: Charts, Excel, Pivot table

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.

Download example file

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