How to Automatically Refresh a Pivot Table in Excel?


One of the more difficult processes in Excel is creating and comprehending pivot tables. When we need to add the new data to the pivot table, we need to add the data to the source data and create a new table. This can waste a lot of time analysing the data in Excel. This tutorial will help you understand how we can automatically refresh a pivot table in Excel. The pivot table is used to understand the tables in a clearer way and help us analyse them more quickly.

Automatically Refresh a Pivot Table in Excel

Here, we will first create a pivot table for the data and then use the VBA code to complete our task. Let us see a simple process to see how we can automatically refresh a pivot table in Excel.

Step 1

Consider the data in an Excel sheet that is similar to the one shown in the image below.

Now to create the pivot table, select the data, then click on "Insert," select "Table," and click on "Pivot Table."

Then enter the destination of the table you want your table to be and click on "OK," select the titles you want the table to be, and our pivot table will be similar to the below image.

Step 2

Now we try to insert the new data into the source data, but it will not update in the pivot table. To do so, right-click on the source data sheet name and select View Code to open the VBA application, then type the programme into the text box as shown in the image below.

Example

Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("pivot").PivotTables("Pivottable1").PivotCache.Refresh End Sub

In the code, pivot is the sheet where the pivot tables are present, and PivotTable1 is the name of the pivot table. Enter the code in the source sheet.

Step 3

Save the sheet as a macro-enabled table and exit the vba application by pressing Alt + Q .From now on, every time we update the source data, the data in the pivot table will be changed. This is how we can automatically refresh a pivot table in Excel.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can automatically refresh a pivot table in Excel.

Updated on: 11-Jan-2023

510 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements