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. Click "Insert" tab on the ribbon.
  3. Click table button.
  4. Click "My table has headers" if needed.
  5. Click 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 right-click on a cell in the table to open a context menu, click on "Insert" and then "Table Rows Above".

Recommended article:

Become more productive – Learn Excel Defined Tables

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

Become more productive – Learn Excel Defined 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.

Right click on the sheet name where you placed the pivot table.

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

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!

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

Click 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