How to Automatically Update a Pivot Table Range in Excel

 

Updating a pivot table is a pain…

…isn't it?

If you use pivot tables in your work frequently...

...I’m sure you can understand.

The point is:

Every time when you add new data in the source sheet you need to update the source range for the pivot table...

...before you refresh your pivot table.

Now just imagine...

...if you add data to your source sheet every day you have to update source range every day.

And every time changing pivot table range, is a mess.

Yes that’s right, the more frequently you add data, the more you need to update the source range.

So the point is...

...you need a method to update source range automatically...

...when you add new data.

Top 3 Ways for Updating a Pivot Table Range Automatically

In this post, I’d like to share with you 3 different methods...

...which you can use to update the source range of a pivot table automatically.

  1. Apply Table
  2. OFFSET Function
  3. VBA Code

...so without any further ado, let's get started.

NOTE: pivot tables are one of the INTERMEDIATE EXCEL SKILLS and updating a pivot table range automatically is one of the ADVANCED PIVOT TABLE SKILLS.

1. Apply Table to have a Auto Updating Pivot Table Range

A few days back I asked with John Michaloudis about his million dollar pivot table advice.

He says:

Put your source data in a table.

Believe me, it's a million dollar advice.

By applying a table in source data you don’t need to change the source range of your pivot table again and again.

Whenever you add new data, it will automatically update pivot table range.

Convert Data into a Table Before Creating a Pivot Table

Every time before creating a pivot table make sure to apply table to source data by using following steps.

  • Select any of the cells in your data.
  • Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
  • You will get a pop-up window with your current data range.
add table to update pivot table range
  • Click OK.
  • Now to create a pivot table select any cell of your data. Go to → Design Tab → Tools → Summarize With Pivot Table
new pivot table to update pivot table range
  • Click OK.

Now, whenever you add new data into you datasheet it will automatically update pivot table range and you just have to refresh your pivot table.

Convert Data into a Table After Creating a Pivot Table

If you already have a pivot table in your worksheet..

...you can use following steps to convert your data source into a table.

  • Select any of the cells in your data source.
  • Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
  • You will get a pop-up window with your current data range.
  • Click OK.
  • Now, select any of cells from your pivot table and Go to → Analyze → Data → Change Data Source → Change Data Source (Drop Down Menu).
  • You will get a pop-up window to re-select your data source or you can also enter the name of the table into the range input.
change source data to update pivot table range
  • Click OK.

From now on-wards every time when you add new data into your source sheet it will increase pivot table range to automatically update it.

2. Create a Dynamic Pivot Table Range with OFFSET Function

The other best way to update pivot table range automatically is to use a dynamic range.

Dynamic range can expand automatically whenever you add new data into your source sheet. Following are the steps to create a dynamic range.

  • Go to → Formulas Tab → Defined Names → Name Manager.
  • Once you click on name manager you will get a pop-up window.