Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
If you use pivot tables in your work frequently, I am sure you face the problem to update it’s 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.
The point is, we need a method to update source range automatically when we add new data so that we can save our precious time.
So today, in this post, I’d like to share with you 3 different methods which we can use to update the source range of a pivot table automatically.
So let's get started.
A few days back I had a word with John Michaloudis who has created the most comprehensive pivot table course on the internet about his million dollar advice. And he said:
Put your data in a table.
Believe me, that's a million dollar advice.
By applying a table in data source you don’t have to change the source of your pivot table again and again. Whenever you add new data, it will automatically update pivot table range.
If you already have a pivot table in your worksheet you can use following steps to convert your data source into a table.
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.
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.
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 & 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.
Most of the people love to use VBA for pivot tables. 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.
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 above code, I have specified the variables for pivot table & source data worksheet. You can make a change here to use this code in your workbook.
'Enter in Pivot Table Name PivotName = "PivotTable2"
Enter the name of the pivot table on which you want to use this code.
'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))
This code will create a dynamic range by using cell A1 from the data source worksheet. It will check the last column & 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."
This code will refresh the pivot table & show a message to the user that pivot table in updated now.
Using a dynamic method to update pivot table range can save your lots 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 just a table? Please tell me which method is your favorite to update pivot table range. Go and hit comment box.