Excel Tips: How to Add Refresh Button in a Excel Workbook

Problem: Hoe to Add a Refresh Button Button in an active sheet to automatically refresh Pivot Table (s) without going to Data Tab or Pressing Alt + F5 or Ctrl + Alt + F5. 

Solution: You can add a refresh button directly on your worksheet in Excel 2021. Here's how to do it:

1. Enable the Developer Tab (if it's not already enabled):

   • Go to File > Options.
   • Select Customize Ribbon.
   • In the right pane, check the Developer checkbox.
   • Click OK.

2. Add a Button to the Worksheet:

   • Go to the Developer tab.
   • Click Insert in the Controls group.
   • Under Form Controls, click the Button (Form Control) icon.
   • Click and drag on the worksheet to draw the button.

3. Assign a Macro to the Button:

   • After drawing the button, the Assign Macro dialog will appear.
   • Click New to create a new macro, which will open the VBA editor.

4. Write the VBA Code:

   • In the VBA editor, you will see something like this:

     vba
     Sub Button1_Click()
     '
     ' Button1_Click Macro
     '
     ' 
     End Sub
     

   • Modify it to refresh all data connections and pivot tables:

     vba
     Sub Button1_Click()
         Dim ws As Worksheet
         ' Refresh all pivot tables in all sheets
         For Each ws In ThisWorkbook.Worksheets
             Dim pt As PivotTable
             For Each pt In ws.PivotTables
                 pt.PivotCache.Refresh
             Next pt
         Next ws
         ' Refresh all data connections
         ThisWorkbook.RefreshAll
     End Sub
     

   • Close the VBA editor.

5. Assign the Macro to the Button (if not automatically assigned):

   • Right-click the button.
   • Select Assign Macro.
   • Choose the macro you just created (e.g., Button1_Click).
   • Click OK.

Now, whenever you click the refresh button on your worksheet, it will refresh all pivot tables and data connections in your workbook.