Pivot Table Update

We can update Pivot Table VBA using the following steps, explained with an example.

The following image shows a source dataset and a Pivot Table based on it.



Consider we change the values of two cells in the source dataset, as depicted in the image below.



Here is how to update the new values in the Pivot Table using Excel VBA.

• Step 1: With the active sheet containing the given source dataset and Pivot Table open, press Alt + F11 to access the VBA Editor.



• Step 2: Choose the applicable VBAProject and the Module option under the Insert tab to open a new module.





• Step 3: Enter the VBA code in the module to refresh the required Pivot Table.



• Step 4: Press the play icon to run the code.

Pivot Tables do not update automatically. We must manually update it using the Refresh option from the contextual menu or the Analyze tab.

However, we can set the settings to update Pivot Tables automatically whenever we open the Excel file.

1) Click anywhere in the Pivot Table to enable the Analyze tab.
2) Choose the Options command down arrow → the Options option in the Analyze tab.
3) The PivotTable Options window opens, where we must click the Data tab to open it.
4) Check the checkbox for the Refresh data when opening the file option of the PivotTable Data section in the Data tab.
5) Click OK in the PivotTable Options window.

We can update Pivot Table in Google Sheets using the following methods.

Method 1 – Using The Refresh Icon
1) Open the Google Sheets application.
2) Click the Refresh icon at the top of the window.
The page will reload, updating the unrefreshed data in the Google Sheets, including the Pivot Table.

Method 2 – Using The Editor Option
1) Open the Google Sheets program.
2) Select the sheet where we must refresh the data.
3) The selected Google Sheet will open.
4) Consider entries existing outside the Pivot Table range.
5) Click on a cell for the Editor icon to appear below the table.
6) Click the Edit icon. The Pivot Table Editor window will appear on the right of the screen.
7) Locate the Data Range information in the Pivot Table Editor window.
8) Click the Select Data Range icon on the right end of the data range information.
9) A box appears where we can enter the new data range, which includes the new data.
10) Once we enter the new data range, click OK in the box.