In Excel, we use a pivot table to summarize the data rapidly. Pivot tables are the most excellent feature in Excel. However, it does not refresh automatically while modifying or updating data in the worksheet. In this article, we will learn to refresh the pivot table in Excel.
Introduction of the Dataset & Pivot Table
The following dataset is about Cars. There are four columns in the dataset. Column B contains the model name of the cars, Column C contains the Brand, Column D contains the color of the car model, and Column E contains the prices of the listed cars. Three bands of cars are listed in the following dataset: Hyundai, Suzuki, and Nissan.
We create a pivot table to summarize the dataset. The pivot table’s Row Labels have the Count of Car Model, Sum of Price, Total Count of Car Model, and Total Sum of Price, and its Column Labels have the Colors and the Grand Total. So, now we can easily see the total number of cars and the total price of all the cars in a compact way.
How to Refresh Pivot Table in Excel: 4 Easy Ways
A pivot table is a trademark in Excel that lets the data be reconstructed. However in Excel, the pivot tables could not be updated automatically if we modify the data source.
1. Refresh Pivot with Right Click of the Mouse
Suppose, we don’t want to see the Altima car model which is in the Nissan brand. So, let’s delete row 6. To do this, right-click on the row number and then just click on Delete.
It will delete the row we don’t want to keep on our dataset. Now we can see that the Nissan brand has only one car on the list.
But if we take a look at our created pivot table, the modified data is not updated yet. To refresh the table we just need to follow the steps below.
STEPS:
- Firstly, click anywhere in the pivot table.
- Secondly, right-click on the table and select Refresh.
- Finally, this will refresh the pivot table as shown in the picture. As a result, we can see that the Nissan brand now has only one car on the list.
2. Pivot Options to Refresh Automatically While Opening the File
Let’s assume that we want to add the Nissan Altima car again. To do that, right-click on the row where we want to view the inserted data. Then, click on Insert.
This will insert a row, now put the data on the row.
To refresh the updated data to the pivot table, go around with the steps down.
STEPS:
- First, select anywhere in the pivot table.
- In the second place, go to the PivotTable Analyze tab from the ribbon.
- After that, from the Options drop-down menu, select Options.
- Instead of doing this, just right-click on the table and select PivotTable Options.
- The PivotTable Options dialog box will appear.
- Consequently, go to the Data menu.
- Next, checkmark the Refresh data when opening the file.
- Then, click on the OK button.
- As a result, now we can view the card which is red now shown in the pivot table. Mainly the pivot table is updated now.
3. Refresh Pivot Data from PivotTable Analyze Tab
To refresh the pivot table as shown in the previous method, we can also use the PivotTable Analyze tab. For this, follow the below steps.
STEPS:
- In the beginning, go to the PivotTable Analyze tab on the ribbon.
- Now, click on the Refresh drop-down menu.
- And, select Refresh.
- In the end, we can see the result.
If you have multiple pivot tables in your worksheet, you can refresh all the pivot tables together by clicking on the Refresh All option.
4. VBA Code to Refresh Pivot table in Excel
We can use a simple VBA code to refresh our pivot table. For this, suppose we delete the Nissan Altima brand again, likewise the earlier methods. To do this, do the same as shown below.
STEPS:
- First, right-click on the sheet where the pivot table is located.
- Second, go to the View Code.
- After that, copy and paste the VBA code below.
- If you have multiple pivot tables in your worksheet.
VBA Code:
Private Sub Sheets()
Application.EnableEvents = False
Me.PivotTables(1).RefreshTable
Application.EnableEvents = True
End Sub- In the end, to Run the code, press the F5 key or click on the Run Sub button.
- This will refresh the pivot table.
Things to Remember
- We can easily refresh all the pivot tables with a keyboard shortcut. To do this, just click anywhere on the pivot table then press the Alt + F5 key. It will refresh all the pivot tables on the spreadsheet.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods are guidelines to refresh the pivot tables in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
