How to Disable Background Refresh in Excel (2 Handy Methods)

Download Practice Workbook

The sample dataset contains EURO Conversion rates from other currencies and the data is linked to a webpage that shows live updates of currency rates.

Method 1 – Disable Background Refresh from Query Properties

Steps:

• Click any of the cells which contain the linked data.
• Go to the Data tab in the top ribbon.
• Click on the Refresh All drop-down menu and select Connection Properties.

• A new window will appear named “Query Properties”.
• Uncheck the option named “Enable Background Refresh”.

• You have disabled the background refresh. The data will not refresh automatically. To refresh again, click Refresh All.

Read More: Pivot Table Not Refreshing (5 Issues & Solutions)

Similar Readings

• How to Refresh All Pivot Tables with VBA (4 Ways)
• Auto Refresh Pivot Table in Excel (2 Methods)
• How to Refresh All Pivot Tables in Excel (3 Ways)
• How to Auto Refresh Excel File Without Opening (2 Easy Ways)

Method 2 – Apply VBA Code to Disable Background Refresh

Steps:

• Go to the Developer tab in the top ribbon.
• Select Visual Basic.

• The Visual Basic window will open.
• Go to Insert > Module to open a new module.

• Enter the following code.

Sub Disable_Background_Refresh()
Dim dbr As Long
    With ActiveWorkbook
        For dbr = 1 To .Connections.Count
          If .Connections(dbr).Type = xlConnectionTypeOLEDB Then
            .Connections(dbr).OLEDBConnection.BackgroundQuery = False
          End If
        Next dbr
    End With
End Sub

• Click on the Run button.
• Select “Disable_background_refresh” macro and click on Run.

• The background refresh has become disabled.
• You can also make a button to disable background refresh using the macro code.
• Go to the Insert tab and select the button option.

• Select an area in the worksheet where you want to place the button.
• A window named “Assign Macro” will open.
• Select the macro and click OK.

• A button will be created. Rename it as “Disable Background Refresh”.
• The background refresh option will be unchecked.

Sub Disable_Background_Refresh()

Dim dbr As Long

With ActiveWorkbook
For dbr = 1 To .Connections.Count
If .Connections(dbr).Type = xlConnectionTypeOLEDB Then
.Connections(dbr).OLEDBConnection.BackgroundQuery = False

Read More: VBA to Refresh Pivot Table in Excel (5 Examples)

Related Articles

• How to Refresh Excel Sheet Automatically (3 Suitable Methods)
• [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
• How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)
• [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
• How to Refresh Chart in Excel (2 Effective Ways)
• How to Automatically Update a Pivot Table When Source Data Changes

<< Go Back to Power Query Excel | Learn Excel