{"id":2130,"date":"2022-12-23T15:26:16","date_gmt":"2022-12-23T15:26:16","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=2130"},"modified":"2024-02-28T20:14:15","modified_gmt":"2024-02-28T20:14:15","slug":"refresh-pivot-table-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/refresh-pivot-table-in-excel\/","title":{"rendered":"How to Refresh Pivot Table in Excel?"},"content":{"rendered":"

Refresh Pivot Table in Excel to ensure your data analysis remains accurate and up-to-date. By regularly updating your pivot tables, you can maintain the integrity of your reports, make informed decisions based on the latest information, and adapt swiftly to new data trends. This crucial step in data management allows for dynamic reporting and timely insights, making your Excel workflows more efficient and reliable. Embrace the power of refreshing pivot tables to transform your data analysis into a robust, responsive process that consistently supports strategic business decisions.<\/p>\n

Manually refresh Pivot Table<\/strong><\/h2>\n

When the existing data source changes and you want to refresh the pivot table to reflect those changes, this approach is the best choice.<\/p>\n

The steps to reload a pivot table are as follows<\/strong><\/h2>\n

Step 1: <\/strong>Click the right mouse button on any Pivot Table cell and then choose \u201cRefresh\u201d.<\/p>\n

\"Refresh<\/p>\n

The Pivot Table will quickly refresh as a result.<\/p>\n

You may also use the keyboard shortcut ALT + F5 to choose any cell in the pivot table.<\/p>\n

Quick Tip:<\/strong> It is recommended to utilize an Excel Table to generate the Pivot Table after converting the data source into an Excel Table. In this case, as an Excel Table automatically takes into account newly added rows and columns, you may also utilize the refresh technique to update the Pivot Table also when additional data (rows\/columns) are added to the raw data.<\/p>\n

Refresh Pivot Table on Protected Sheet<\/strong><\/h2>\n

You cannot refresh the pivot tables on a worksheet that has been protected. Right-clicking on the pivot table causes the Refresh command to become faint.<\/p>\n

\"Refresh<\/p>\n

Here’s how to avoid the issue<\/strong><\/h2>\n

Manually:<\/strong> If you are aware of the password, you can manually unlock the worksheet, update the pivot table, and then re-protect it.<\/p>\n

Macros:<\/strong> If you’re using a macro to update the pivot table, add code to unlock the worksheet, update the pivot table, and then lock it back. An example is shown in this macro.<\/p>\n

Sub UnprotectRefresh()<\/p>\n

On Error Resume Next<\/p>\n

With Activesheet<\/p>\n

.Unprotect Password:=”mypassword”<\/p>\n

.PivotTables(1).RefreshTable<\/p>\n

.Protect Password:=”mypassword”, _<\/p>\n

AllowUsingPivotTables:=True<\/p>\n

End With<\/p>\n

End Sub<\/p>\n

Refresh\/Update Pivot Table by Changing the Data Source<\/strong><\/h2>\n

You must update the pivot table’s source data if the number of rows and columns in your data set changes.<\/p>\n

The procedure of refreshing Pivot Table by changing the data source:<\/strong><\/p>\n

Step 1:<\/strong> In the Pivot Table, choose any cell.<\/p>\n

\"Refresh<\/p>\n

Step 2:<\/strong> Click \u201cChange Data Source\u201d under the \u201cData\u201d group on the \u201cPivotTable Analyze\u201d tab.<\/p>\n

\"Refresh<\/p>\n

Step 3:<\/strong> Update the range to include new data in the \u201cChange PivotTable Data Source\u201d dialog box. After that, click OK.<\/p>\n

\"Refresh<\/p>\n

You don’t need to use the change data source option if you convert the data source to an Excel table and use that table to build the pivot table. The Pivot Table will automatically account for the new rows and columns if you simply refresh it.<\/p>\n

Auto refresh Pivot Table Using a VBA Macro<\/strong><\/h2>\n

Even though it just takes two clicks to refresh a Pivot table, you still need to do it each time there is a change.<\/p>\n

You can use a straightforward one-line VBA macro code to improve efficiency and have the Pivot Table automatically refresh anytime the data source changes.<\/p>\n

The VBA code is as follows:<\/p>\n

Private Sub Worksheet_Change(ByVal Target As Range)<\/p>\n

Worksheets(“Sheet1”).PivotTables(“PivotTable1”).PivotCache.Refresh<\/p>\n

End Sub<\/p>\n

Deciphering the Code:<\/strong> This change event is triggered each time the sheet containing the source data is changed. The code updates the Pivot Cache of the Pivot Table with the name PivotTable1 whenever there is a change.<\/p>\n

To make this code work for your workbook, you must make the following changes:<\/p>\n

“Sheet1” should be replaced with the name of the sheet containing the pivot table in this section of the code.<\/p>\n

Replace “PivotTable1” with the name of your pivot table. Click anywhere in the Pivot Table and select the Analyze Tab to get the name. The name would be readable under the “PivotTable Name” header in the left portion of the ribbon.<\/p>\n

\"Refresh<\/p>\n

Where to enter this VBA code<\/strong><\/h2>\n

Step 1:<\/strong> Hit \u201cAlt and F11\u201d on your keyboard. The VB Editor window will open.<\/p>\n

Project Explorer would be on the left in the VB Editor (that has the names of all the worksheets). Press \u201cControl + R\u201d to make it visible if it isn’t already.<\/p>\n

\"Refresh<\/p>\n

Step 2:<\/strong> Double-click on the name of the sheet containing the pivot table in the Project Explorer.<\/p>\n

Copy and paste the provided code into the code window on the right, then change the pivot table’s name and sheet name as necessary.<\/p>\n

\"Refresh<\/p>\n

Snap the VB Editor shut. Now the Pivot Table would automatically refresh whenever you made any changes to the data source.<\/p>\n

Note:<\/strong> Because the workbook contains a macro, save it with the.xls or.xlsm extension.<\/p>\n

Application of Refresh Pivot Table in Excel<\/strong><\/h2>\n