Excel Tip: How to create a pivot table that updates automatically

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

Microsoft Excel has a powerful reporting tool called the Pivot Table. In a few minutes and in a few mouse clicks, you can build a report of your data. This is used by most Excel users but the problem is, the Pivot Table does not refresh to reflect the changes you made in your source data.

How to create a pivot table in excel that updates automatically

Though Excel's Pivot Table is powerful in building good reports regardless of the amount of source data, being unable to automatically reflect changes in the source data is a big limitation for many users. Of course, one will be very pleased if adding or modifying data or information from the source table will be automatically reflected in the Pivot Table.

Creating a Pivot Table that Updates Automatically

The following procedures will create a pivot table that updates automatically when the source data is modified. The refreshing behavior is done using a simple VB code, thus the resulting file should be saved as a Macro-Enabled file. For Excel 2007 and later, the file extension is XLSM.

A. Convert the Source Data into a Table

Step 1:  Select by highlighting the source data of the Pivot Table. In the sample file for this demo, it means selecting the range A1:H63 of the Sales Record sheet.

Step 2: With the data range selected, click on the Insert tab/menu, then click on Table in the Tables group.

Excel => Insert => Table

Step 3: A small "Create Table" box appears. Check the option "My table has headers" and click OK.

Converting selected source data to table format

Step 4: The selected source data will then be formatted as table.

 

B. Create a Pivot Table using the Defined Table

Step 5: With the table data still selected, click on Insert menu then click on Pivot Table in the Tables group.

Excel => Insert => Pivot Table

Step 6: Then the Create Pivot Table dialog box appears, with the table name, which defaults to Table1, preselected. Make your options and then click on OK. The new worksheet will then show up with the Pivot Table Field List shown. Pick the fields in the list to build your pivot table.

Sample fields to make up the pivot table

Step 7:  When the pivot table is done, you shall then notice that any changes you make in the source data are NOT reflected in the pivot table automatically. That is the purpose of this tutorial - to make the pivot table update automatically.

 

C. Convert Into a Dynamic Pivot Table using VB Code

In the previous steps above, you can save the sheet with the Pivot Table as Reports. Then make a copy of this sheet and name it as Reports_Auto. This will be the sheet that we will be working to convert the pivot table into a dynamic one.

Step 8: Supposed you have already created a copy of the Reports sheet and named it as Reports_Auto. Now, right click on Reports_Auto tab, then click on View Code.

Start VB editor using the View Code command

Step 9: Then you will get the VB Editor with the correct sheet already highlighted. Then copy and paste this code into the box:

Private Sub Worksheet_Activate()
    Sheets("Reports_Auto").PivotTables("PivotTable1").RefreshTable
End Sub

Step 10: Here, you need to put the name of the actual sheet containing the pivot table that you want to dynamically update. In this sample, it is called Reports_Auto so that explains the sheet name in the code.

Step 11: Also, another important name in the code is PivotTable1, that is the name of the pivot table that we created in this demo. If you named the sheet and the pivot table differently, then make sure that these names are entered into the code above for this to work.

Step 12: When you save the file at this point, you will get a prompt to save the file as Macro-Enabled file. Click on NO so that the Save As dialog appears.

The prompt to save the file as XLSM

Step 13: In the Save As box, select Excel Macro-Enabled Workbook (.xlsm). Give it a name and then save it.

Congratulations! At this point, the pivot table in the Reports_Auto sheet shall be dynamic. This means that when you change the data in the source table, it will automatically be reflected in the pivot table.

MS Office Software: 
AttachmentSize
File Dynamic_Pivot_Table_Sample.xlsm32.74 KB