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

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete data in your data set the pivot is instantly refreshed.

There is no need to manually refresh the pivot table or changing cell references if the data source table grows or shrinks. The Excel defined table uses structured cell references that adjust automatically.

How to create an Excel defined table

  1. Select a cell in your data set.
  2. Press with left mouse button on "Insert" tab on the ribbon.
  3. Press with left mouse button on table button.
  4. Press with left mouse button on "My table has headers" if needed.
  5. Press with left mouse button on OK button

Adding new records to the table is easy, simply type on the row right below the last record and the table will expand automatically.

You can also press with right mouse button on on a cell in the table to open a context menu, press with left mouse button on "Insert" and then "Table Rows Above".

Recommended article:

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.

How to use Excel Tables

How to auto-refresh pivot table

VBA offers a solution how to automatically refresh pivot table every time you activate "pivot table" sheet, there are other ways to solve this as well like refreshing pivot table every time a cell in data source table is edited.

Press with right mouse button on on the sheet name where you placed the pivot table.

  1. Press with left mouse button on "View code".
  2. Paste code to module.
  3. Change sheet name and pivot table name in VBA code, you probably have a different sheet name and pivot table name.

VBA code

Private Sub Worksheet_Activate()
    Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

Recommended article:

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Auto refresh a pivot table

Excel 2003 and earlier versions: Create a dynamic named range

This also works in Excel 2007/2010 but it is easier to create a table.

  1. Press with left mouse button on "Name Manager"
  2. Create a new named range, named Rng.
  3. Type in "Refers to:" window:
=Data!$A$1:INDEX(Data!$1:$65535, COUNTA(Data!$A:$A), COUNTA(Data!$1:$1))

Press with left mouse button on Close button!

Recommended article:

Create a dynamic named range

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Create a dynamic named range