A pivot table doesn’t automatically respond to a change in its source data the way a worksheet formula does. Suppose you use the SUM function in a formula to get the total of the values in A1:A5. Then you change the value in, say, A3. Unless you’ve gone to the trouble of turning off automatic recalculation, the formula with the SUM function immediately recalculates and takes account of the changed value. Pivot tables don’t work like that. When their data source changes, they don’t automatically recalculate themselves. You have to arrange for that to happen. How?
There are three basic ways [methods] to refresh pivot tables in excel sheet, as I am going to provides in this post. Enjoy!
Way-1. Manual Refreshes
Updating a pivot table to reflect new data is called refreshing the pivot table. To do this manually, right-click any cell in the pivot table and choose “Refresh“ from the contextual menu [Note: in versions earlier than Excel 2007, the menu item is “Refresh Data“].
Way-2. Semi-Automatic Refreshes
You can, if you want, use the pivot table’s options to call for the pivot table to refresh itself when the workbook is opened. This approach has value when the pivot table’s data source is not a range in a worksheet, but a connection to an external database. In that case, whenever you open the workbook, the pivot table refreshes itself from the latest information in the database.
Some accounting software [programs] do not generally support that sort of live connection. So, if you want to take this approach, you would have to update the workbook with new data from your accounting software report, then save and close the workbook, and then cause the pivot table or tables to refresh themselves by reopening the workbook.
Way-3. Automatic Refreshes
This is probably the most convenient method in the long run, but it requires some up-front work. The idea is to cause VBA code to run automatically whenever you activate a worksheet that contains one or more pivot tables. [Note: VBA is Microsoft’s Visual Basic for Applications scripting language, which Excel uses]. In turn, the VBA code causes the pivot tables to refresh. This approach is superior to manual or semi-automatic refreshes because you don’t have to remember to do it every time you export a report from your accounting software.
Here are the steps to arrange automatic refreshes:
Step-1. Right-click the worksheet tab of a worksheet that contains one or more pivot tables.
Step-2. Choose View Code from the contextual menu.
Step-3. The Visual Basic Editor window opens, as shown below:
Step-4. There are two dropdowns at the top of the Code window. The one on the left, by default, displays General. Choose Worksheet from that dropdown.
Step-5. The dropdown on the right changes to display SelectionChange and two VBA statements appear in the Code window. You can select and delete them if you want. They do no harm if you decide to keep them.
Step-6. Choose Activate from the dropdown on the right. The following statements appear in the code window, with a blank row between them:
Private Sub Worksheet_Activate()
Note: You can switch back to the worksheet view by clicking the Excel icon at the left of the upper toolbar.
Step-7. Enter the following code between the Private Sub and the End Sub statements:
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
Step-8. Switch back to the Excel workbook window and save the workbook.
Now whenever you activate the worksheet, any pivot table in that worksheet will be refreshed with the most current data in its data source.