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.
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.
- 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 > PivotTable > From Table/Range.
- 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 > Queries & Connections > Connections.
- Right-click on Worksheet Connection and select properties to open the Connection Properties dialog box.
- Go to Usage 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 to open Visual Basic Editor.
- Go to Insert > Module.
- 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
- Press ALT+F8 to open the Macros dialog box. Now, click Run.
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.
- Click the dropdown of Refresh under the Data tab.
- Select Refresh All.
Now, check all the pivot tables in your Excel workbook. You will see the updated result.
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.