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.

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.

Dataset to refresh Pivot Table in Excel

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.

Created Pivot Table from raw dataset to refresh Pivot Table in Excel

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

Inserted new data to table to refresh Pivot Table in Excel

Follow these steps to refresh a Pivot Table in Excel:

  1. Select any cell in the pivot table.
  2. Right-click on it to access the Context Menu.
  3. Click on the Refresh option.
    To refresh a pivot table using keyboard shortcut keys use ALT+F5.
    Clicked on Refresh options to refresh Pivot Table in Excel
    You can see the result. After clicking the Refresh options, I updated the pivot table.
    Updated Pivot Table by clicking on Refresh option in Excel

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:

  1. Click a random cell on a pivot table.
  2. Go to the PivotTable Analyze tab.
  3. Click on Options under the PivotTable group to open the PivotTable Options dialog box.
    Access Options in Pivot Table to change settings to refresh Pivot Table in Excel
  4. Navigate to the Data tab.
  5. Check on the “Refresh data when opening the file“.
    Checked on "Refresh data when opening the file" in PivotTable Options in Excel After clicking OK, it will show the following message. It will update the other pivot tables on the worksheets.
    Shown message that it will refresh all pivot tables in the Excel workbook

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.
Updated Pivot Table opening the workbook right-after closing it

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:

  1. Select the data range.
  2. Go to Insert tab > PivotTable dropdown > From Table/Range option.
    Steps to insert Pivot Table to refresh Pivot Table by setting a timer
  3. In PivotTable from table or range, select the location of the pivot table and check “Add this data to the Data Model“.
    Check on "Add this data to the Data Model" to refresh Pivot Table
  4. Go to the worksheet where you have placed the pivot table.
  5. Navigate to Data tab > Queries & Connections tool > Connections command.
  6. Right-click on Worksheet Connection and select properties to open the Connection Properties dialog box.
    Create connections to refresh Pivot Table setting a timer in Excel
  7. Go to Usage tab and check on the “Refresh every __ minutes“. Choose the timing you want to add. Then, click OK.
    Checked on the Refresh and set a time to refresh pivot table in Excel

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

  1. Right-click on the sheet’s name and select View Code command to open Visual Basic Editor window.
    Clicked on View Code to insert a VBA code to refresh Pivot Table
  2. Go to Insert tab > Module command.
    Inserted Module to refresh Pivot Table with VBA code in Excel
  3. 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
    MsgBox "PivotTable refreshed successfully.", vbInformation
    MsgBox "PivotTable not found.", vbExclamation
    End If
    End Sub

    Pasted VBA code to refresh Pivot Table in Excel

  4. Press ALT+F8 to open the Macros dialog box. Now, click Run command.
    Clicked on Run in Macro dialog box to refresh Pivot Table in Excel
    After clicking on Run, it will update the dataset. It will show a message after refreshing the pivot table.
    Message shown after refreshing pivot table with VBA code

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.Updated Pivot Table after refreshing Pivot Table with VBA code

Refresh Multiple Pivot Tables at Once in Excel

  1. Click any cell of a pivot table.
  2. Go to PivotTable Analyze tab.
  3. Click the dropdown of Refresh under the Data tab.
  4. Select Refresh All command.
    Chose Refresh All options to update multiple sheets in Excel

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:

  1. Press F9 on your keyboard.
  2. 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:

  1. 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.
  2. 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)

Leave a Reply

Your email address will not be published. Required fields are marked *