Dynamic Pivot Table Range that Don’t Needs to Update

Apply Table to have an Auto-Updating Pivot Table Range

A few days back I asked John Michaloudis about his million-dollar pivot table advice. He says: Put your source data in a table. Believe me, it’s 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 the pivot table range.

Convert Data into a Table Before Creating a Pivot Table

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

1. Select any of the cells in your data.
2. Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
3. You will get a pop-up window with your current data range.
   
4. Click OK.
5. Now, to create a pivot table, select any cell of your data. Go to → Table Design Tab (Design in older versions) → Tools → Summarize with PivotTable.
   
6. Click OK.

Now, whenever you add new data to your data sheet, it will automatically update the 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 the following steps to convert your data source into a table.

1. Select any of the cells in your data source.
2. Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
3. You will get a pop-up window with your current data range.
4. Click OK.
5. Now, select any of the cells in your pivot table and Go to → Analyze → Data → Change Data Source → Change Data Source (Drop Down Menu).
6. 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.
   
7. Click OK.

From now on, every time you add new data to your source sheet, the pivot table range will grow automatically, and a refresh will show the new data.

Create a Dynamic Pivot Table Range with OFFSET Function

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

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

1. Go to → Formulas Tab → Defined Names → Name Manager.
2. Once you click on Name Manager, you will get a pop-up window.
   
3. In your Name Manager window, click on New to create a named range.
4. In your new name window, enter
   1. A name for your new range. I am using the name “SourceData”.
   2. Specify the scope of the range. You can specify between the current worksheet or workbook.
   3. Add a comment to describe your named range, if you want. Enter the below formula in the “Refers to” input bar.
      

      =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. In the end, click OK.

Now, you have a dynamic range to create a pivot table.

All you have to do is just create a pivot table with your source data and after that change the source with the named range using the same method which I have used in the first method of tables.

Once you add new data to your source sheet, just refresh your pivot table.

How does this Formula Work?

In the above formula, I have used the OFFSET function to create a dynamic range.

I have mentioned cell A1 as the starting point, and then, without moving any rows or columns (0, 0), I have specified the height and width of the range by using COUNTA.

COUNTA will count the cells with values in column A and row 1 and tell OFFSET to expand its height and width accordingly.

The only thing you have to take care of is that there should be no blank cells in column A and row 1 of your data (and nothing else below or to the right of it in that column and row), or the counts will be off.

OFFSET is a volatile function, so it recalculates every time anything changes in the workbook. In a big file, you can use INDEX instead, which gives the same range without being volatile:

=PivotTableData!$A$1:INDEX(PivotTableData!$A:$Z,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

Change $A:$Z to cover all the columns your data can have.

Update the Pivot Table using a VBA Code

Most people love to use VBA codes. So here is the code you can use to update the pivot table range with VBA.

Sub UpdatePivotTableRange()

Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim DownCell As Long

'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

'Enter in Pivot Table Name
PivotName = "PivotTable2"

'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable

'Complete Message
Pivot_Sheet.Activate
MsgBox "Your Pivot Table is now updated."

End Sub

Things you have to change before you use it in your workbook.

1. Line13: Change the name of the source worksheet.
2. Line14: Change the name of the pivot table sheet.
3. Line17: Change the name of the pivot table.

If you still have a problem using this code, please write to me in the comment box. Now, let me show you how this code works so that you can easily modify it as per your need.

Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

In the above part of the code, we have set the variables for the pivot table and source data worksheets. You can change the name of the worksheet from here.

PivotName = "PivotTable2"

In the above part of the code, enter the name of the pivot table on which you want to use this code.

Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

The above part of the code will create a dynamic range by using cell A1 from the data source worksheet.

It will check the last column and last row with data to create a dynamic range. Every time you run this macro it will create a new dynamic range.

'Ensure Pivot Table is Refreshed
 Pivot_Sheet.PivotTables(PivotName).RefreshTable
 
'Complete Message
 Pivot_Sheet.Activate
 MsgBox "Your Pivot Table is now updated."

The above part of the code will refresh the pivot table and show a message to the user that the pivot table is updated now.

Refresh the Pivot Table Automatically

All the methods above update the range, but you still need to refresh the pivot table to see the new data. You have two ways to make that automatic as well:

1. Refresh when you open the file: right-click the pivot table ➜ PivotTable Options ➜ Data tab, and tick “Refresh data when opening the file”.
2. Refresh when you change the data: add the code below to the source sheet’s code module (right-click the sheet tab ➜ View Code). It refreshes all the pivot tables in the workbook whenever a cell on that sheet changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.RefreshAll
End Sub

Keep in mind that RefreshAll also refreshes any other connections and queries in the file, which can be slow in a big workbook. You can also refresh all the pivot tables yourself with Ctrl + Alt + F5.

Frequently Asked Questions