Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

How to Disable Background Refresh in Excel


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.

How to Disable Background Refresh in Excel

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

How to Disable Background Refresh in Excel

  • 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


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

Apply VBA Code to Disable Background Refresh

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

Apply VBA Code to Disable Background Refresh

  • 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

Apply VBA Code

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

How to Disable Background Refresh in Excel

  • 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.

Apply VBA Code

  • 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

Apply VBA Code

  • 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.

Apply VBA Code

VBA Code Explanation:

Sub Disable_Background_Refresh()
  • Set the macro name as Disable_Background_Refresh
Dim dbr As Long
  • Setting a variable that will store numeric values of a big range.
With ActiveWorkbook
For dbr = 1 To .Connections.Count
If .Connections(dbr).Type = xlConnectionTypeOLEDB Then
.Connections(dbr).OLEDBConnection.BackgroundQuery = False

This code will go through all the connections.

  • Make it True to enable the background refresh and False to disable it.

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

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo