How to Automatically Update a Pivot Table Range in Excel

If you use pivot tables in your work frequently, I am sure you face the problem to update its source data range manually.

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, if you do that more than one time in a day, it’s a mess.

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

So the point is, you need a method to update source range automatically when we add new data so that we can save our precious time.

Today, in this post, I’d like to share with you three different methods which we can use to update the source range of a pivot table automatically.​

So let's get started.

Quick Navigation
1. Apply Table
2. Dynamic Range
3. VBA Code

1. Apply Table

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.

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

B. 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. Dynamic Range

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.
use name manager to update pivot table range
  • In your name manager window click on new to create a named range.
  • In your new name window, enter
    • A name for your new range. I am using a name “SourceData”.
    • Specify the scope of the range. You can specify between current worksheet or workbook.
    • Add a comment to describe your named range.
      Enter below formula to “Refer to” input bar.
    • =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))
    • In the end, click OK.
create a new name to update pivot table range

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 in your source sheet, just refresh your pivot table.

How does this Formula Works?

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

I have mentioned cell A1 as the starting point and then without mentioning rows and columns, I have specified the height and width of the range by using COUNTA Function.

COUNTA function will count the cells with values from column A and row 1 and tell offset to expand it’s height and width accordingly.

The only thing you have to take care that there should be no blank cell in between your column A and row 1.

3. VBA Code

Most of the people love to use VBA codes. So here is the code to use to update 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 lastRow 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 source worksheet.
  2. Line14: Change the name of pivot table sheet.
  3. Line17: Change the name of the pivot table.

If you still have a problem to use this code, please write 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 specified the variables for the pivot table and source data worksheet. You can change the name of the worksheet from here.

PivotName = "PivotTable2"

In 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))

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

Above part of the code will refresh the pivot table and show a message to the user that pivot table in updated now.

Sample File

Download this sample file from here to learn more.

Conclusion

Using a dynamic method to update pivot table range can save you a lot of time. You don’t have to change source data again and again.

Just choose one of the above-mentioned methods, put your data on your data sheet and refresh your pivot table.

If you ask me I always use tables.

Now, tell me one thing.

Which method do you really like? VBA or table?

Share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tips with your friends.

Share0
Tweet0

Must Read Excel Tutorials

  1. How To Link Slicer to Multiple Pivot Tables in Excel
  2. How to Apply Conditional Formatting to a Pivot Table
  3. How to Group Dates in a Pivot Table in Excel
  4. How to Create a Pivot Table from Multiple Worksheets
  5. Top 31 Pivot Table Tips & Tricks for Advanced Excel Users

Content Protection by DMCA.com
2018-08-31T11:14:47+00:00
  • Christopher Massey says:
    at

    I’m getting a Runtime Error 1004 When I get to the Set DataRange Line under ‘Define Start Point & Dynamic Range of Data….Any suggestions?

    Reply
  • Plastic Cup says:
    at

    Don’t use OFFSET! It is volatile, use INDEX instead:
    =$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
    $A$2 is the upper, left corner of your range.
    You can play around with the INDEX range and the two COUNTA ranges to adjust to your needs.

    Reply
  • Jonathan Koe says:
    at

    Thanks for sharing this. However, I get a syntax error message when trying to run and it points to this particular line.

    NewRange = Data_Sheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1)

    Any idea why/how to fix?

    Reply
  • Anindya says:
    at

    The Table approach works perfectly when we do this manually.. but say we have a template excel file and we have one data sheet and one pivot sheet.. and each time we delete all the rows (except the header one) from the template excel file and insert rows to this data sheet programmatically (with apache POI).. The table approach is NOT working when the number of rows for current iteration is greater than the number of rows in the template file..

    Reply
    • riaz says:
      at

      I have the same issue. Did anyone reply directly to you?

      Reply
  • Munoz Cassie says:
    at

    When I run the code I get a Run-time error ‘1004’: Method “PivotTables” of object’_Worksheet’ failed. Upon debugging it is pointing the code in the Change Pivot Table Data Source Range Address. Any suggestions?

    Reply
    • Puneet Gogia says:
      at

      Refer this:

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

      Reply
  • Wilmar Meyer says:
    at

    Thank you for the above code. It works 100%. But how can I change the above code to only use the actual UsedRange? As I have “” and hidden formulas lower down that I don’t want part of the UsedRange. I don’t want to use xlDown but rather the actual UsedRange….Any Suggestions please? 🙂

    Reply
    • Puneet Gogia says:
      at

      best thing is to create a table (well, a table is a solution in itself) then use that table name in the source name.

      Reply
    • ExcelChamps © 2018
    Toggle Sliding Bar Area
    >