4 Methods to Refresh Pivot Table in Excel

In Excel, there may arise instances where you need to refresh the pivot table. If you have added new data to the dataset, you would want to include them in the existing Pivot Table. Manually adding them can be time-consuming and laborious. Creating a new pivot table for each new set of data is not a practical solution. Hence, the best approach is to refresh the pivot table in Excel. In this article, I will show you 4 different ways to refresh a Pivot Table in Excel.

Table of Contents

Refresh Pivot Table Manually in Excel

Here I have datasets with some product names, the unit prices, and the names of their salesmen. Now, I will insert two new rows here.

Let’s convert the range into a table. To convert into a table, press CTRL+T and select the range, then click OK. Now, I will insert the pivot table using this data table.

Here I have created a sales report category-wise and also filtered the result for a salesman to optimize performance.

I am gonna insert new information into the dataset. Now, we will see how the refresh works on a Pivot Table.

Follow these steps to refresh a Pivot Table in Excel:

Select any cell in the pivot table.
Right-click on it to access the Context Menu.
Click on the Refresh option.
To refresh a pivot table using keyboard shortcut keys use ALT+F5.

You can see the result. After clicking the Refresh options, I updated the pivot table.

Refresh Pivot Tables Each Time You Open the Excel

You can set the settings in Excel to refresh the pivot table each time you close and open your Excel. Here’s how:

Click a random cell on a pivot table.
Go to the PivotTable Analyze tab.

Click on Options under the PivotTable group to open the PivotTable Options dialog box.
Navigate to the Data tab.
Check on the “Refresh data when opening the file“.
After clicking OK, it will show the following message. It will update the other pivot tables on the worksheets.

Now, close the file. Then, open it again to check whether you have got the result updated or not. The following screenshot is the updated result after opening the Excel again.

Refresh Pivot Table in Excel by Setting a Timer

You can set a timer to refresh a pivot table in Excel. To refresh a Pivot Table in Excel setting a timer, you can do the following:

Select the data range.

Go to Insert tab > PivotTable dropdown > From Table/Range option.
In PivotTable from table or range, select the location of the pivot table and check “Add this data to the Data Model“.
Go to the worksheet where you have placed the pivot table.

Navigate to Data tab > Queries & Connections tool > Connections command.
Right-click on Worksheet Connection and select properties to open the Connection Properties dialog box.
Go to Usage tab and check on the “Refresh every __ minutes“. Choose the timing you want to add. Then, click OK.

This will refresh your pivot table every 5 minutes. So, whenever you have a change in the dataset, the pivot table will be updated automatically every 5 minutes.

Refresh Pivot Table in Excel with VBA

Right-click on the sheet’s name and select View Code command to open Visual Basic Editor window.
Go to Insert tab > Module command.

Copy the VBA code and paste it. Then, go back to the worksheet.

Sub RefreshPivotTable()
Dim pt As PivotTable
On Error Resume Next
Set pt = ThisWorkbook.Sheets("PivotTable_4").PivotTables("PivotTable1") 'Change the sheet name and PivotTable name as needed
On Error GoTo 0
If Not pt Is Nothing Then
pt.RefreshTable
MsgBox "PivotTable refreshed successfully.", vbInformation
Else
MsgBox "PivotTable not found.", vbExclamation
End If
End Sub

Pasted VBA code to refresh Pivot Table in Excel

Press ALT+F8 to open the Macros dialog box. Now, click Run command.

After clicking on Run, it will update the dataset. It will show a message after refreshing the pivot table.

The VBA code has successfully refreshed the pivot table. You can see the update in the sum of quantity and sum of sales column with an extra row of sports data.

Refresh Multiple Pivot Tables at Once in Excel

Click any cell of a pivot table.
Go to PivotTable Analyze tab.

Click the dropdown of Refresh under the Data tab.
Select Refresh All command.

Now, check all the pivot tables in your Excel workbook. You will see the updated result.

Conclusion

In this article, I have gathered 4 ways to refresh the pivot table in Excel. Here I have also added the ways to change multiple sheets in Excel as well. While working on large spreadsheets in Excel, you will need to add data to the dataset. In that case, you have to update your pivot table as well. However, this would be hectic to perform the tasks manually. So, you can follow this entire article to refresh pivot tables in Excel. Hopefully, if you follow the steps, you won’t face any issues.

Frequently Asked Questions

Why is Excel sheet not updating?

If your Excel sheet is not updated, formulas set to manual calculation can be a reason. When the calculation mode is set to Manual, Excel won’t automatically update formulas when data changes occur. To resolve this, change the calculation mode to Automatic in the Formulas tab under Calculation Options. This ensures that Excel recalculates formulas automatically, keeping your data up to date.

How do I refresh formulas in Excel?

To refresh all formulas in Excel:

Press F9 on your keyboard.
Excel will recalculate and update all formulas in your workbook.

This quick keyboard shortcut recalculates all formulas, ensuring your Excel data is up-to-date and accurate.

How do I retain formatting in PivotTable refresh?

To retain formatting in a pivot table after a refresh:

Apply Formatting Directly: Apply your formatting (e.g., cell colors, fonts) directly to the cells in the pivot table. This formatting will persist even after a refresh.
Turn Off AutoFormat: Disable the Preserve cell formatting on update option in the PivotTable Options. This will prevent Excel from automatically applying default formatting during a refresh.

By using these methods, you can ensure that the formatting you apply to your pivot table remains intact when you refresh the data.

5/5 - (1 vote)