How to Update a Pivot Table Range in Excel Automatically

How to Update a Pivot Table Range in Excel Automatically

Table of Contents

Updating a pivot table is a pain, isn’t it? I’m sure that pivot tables are everyday part of you job for reporting and analyzing the data thus, you will understand it.

The point is every time when you add new data to 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 the 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 the source range automatically when you add new data.

1. Apply Table to have a 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 the table to 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.
    add-table-to-update-pivot-table-range
  4. Click OK.
  5. 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
  6. 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.

  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 cells from 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.
    change-source-data-to-update-pivot-table-range
  7. 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 the 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.

    1. Go to → Formulas Tab → Defined Names → Name Manager.
    2. Once you click on name manager you will get a pop-up window.
      use-name-manager-to-update-pivot-table-range
    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. Enter the below formula to “Refer to” input bar.
        =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))
    5. 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 to your source sheet, just refresh your pivot table.

    How does this Formula Works?

    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 mentioning rows and columns, I have specified the height and width of the range by using COUNTA.

    COUNTA will count the cells with values from column A and row 1 and tell offset to expand its height and width accordingly. The only thing you have to take care that there should be no blank cell in between column A and row 1.

    3. Update Pivot Table using a VBA Code

    Most people love to use VBA codes. So here is the code to 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 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.

        Free Excel Templates

        UAE-VAT-Dual-Currency-Invoice

        UAE VAT Dual Currency

        UAE VAT Dual Currency Invoice is use when invoice is …

        Overtime-Percentage-Calculator-Excel-Template

        Overtime Percentage Calculator

        Overtime Percentage Calculator is a ready excel template that calculates …

        home budget worksheet

        Home Budget Worksheet

        Home Budget Worksheet is a very simple solution for creating …

        purchase order

        Purchase Order

        This purchase order template for Excel is a simple and budget …

        We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

        Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

        These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

        Learn Microsoft Excel : Blogs and Articles

        Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.