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

You may often need to disable background refresh in Excel to allow the query to complete its task. And, by disabling background refresh, it will allow the queries to refresh before pivot tables in Excel. To avoid errors while working with external data which is linked to a webpage or any other things, you may have to disable background refresh in Excel.

Download Practice Workbook

You can download the practice workbook from here:

2 Effective Methods to Disable Background Refresh in Excel

Suppose you have a data file that is linked to an external web page. And you have edited some of them but after some time you may see that the data is refreshed and get back to the original data. So, you need to disable background refresh in that Excel file. In this article, I will show you 2 easy methods to disable background refresh in Excel. Here, suppose you have a data file that contains EURO Conversion rates from other currencies and the data is linked to a webpage that shows live updates of currency rates.

1. Disable Background Refresh from Query Properties

To disable background refresh, you can use Query Properties. To do this, follow the steps below.

Steps:

• At first, click any of the cells which contain the linked data.
• Then, go to the  Data tab in the top ribbon.
• Now, press on the Refresh All drop-down menu and select Connection Properties.

• Then, a new window will appear named “Query Properties”. 
• Here, unmark the option named “Enable Background Refresh”

• As a result, you have disabled the background refresh. So, the data will not refresh automatically, Now, to refresh again, you have to press the Refresh All option.

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)

2. Apply VBA Code to Disable Background Refresh

You can also disable background refresh by using the VBA code. here, I am giving the code and procedures so you can copy it and paste it to run and disable the background refresh option. Follow the steps below.

Steps:

• First, go to the Developer tab in the top ribbon.
• Then, select the Visual Basic option

• Now, the Visual Basic window will appear.
• Then, go to Insert > Module to open a new module.

• After opening a new module. Paste the following code below into the window.

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

• Then, press on the Run
• Select “Disable_background_refresh” macro and press

• As a result, you will see that the background refresh has become disabled.
• You can also make a button to disable background refresh using the macro code.
• For this, go to the Insert tab and select the button option.

• Then, select an area in the worksheet where you want to place the button.
• After selecting the area, a window named “Assign Macro” will appear.
• From it, select the macro and press OK

• Then, you will see a button will create. Then rename it as “Disable Background Refresh”.
• So, after pressing on it, the background refresh option will get unmarked.

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)

Things to Remember 

• After disabling the background refresh, the data won’t update automatically anymore.
• And to update them, you have to press on “Refresh All” option.
• Using a button for run running a macro will help you to iterate the work multiple times.

Conclusion

In this article, you have found 2 easy methods to disable background refresh in Excel. You can download the practice workbooks and apply the methods to them for practice. I hope you found this article helpful. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

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 Refresh All | Power Query Excel | Learn Excel