Excel Data Connections (Create, Refresh, Manage & Remove)

Here’s an overview of one of the ways you can make data connections in Excel.

Download Practice Workbook

We recommend downloading the Excel file and practicing along while reading the article.

Source Workbook:

Sample Dataset.xlsx

Workbook with Connected Sheets:

Created Data connections.xlsx

Create Data Connections in Excel

Method 1 – Using Existing Connections to Connect Two Excel Workbooks

From the Data tab, under Get & Transform Data group, select Existing Connections.
You will get a dialog box named Existing Connections. In that dialog box, click on Browse for More.

You will get another dialog box named Select Data Source. Select your target file which will be connected to the existing workbook and press Open.

If there are column headers in your dataset, check the option First row of data contains column headers and press OK.

From the Import Data dialog box, set the location in Existing worksheet option and press OK. If you want to create only a connection, then select Only Create Connection in the section of Select how you want to view this data in your workbook.

You will get the external source sheet in your workbook.

Method 2 – Use of Power Query to Connect Data in Excel

From the Data tab, select Get Data, choose From File, and pick From Excel Workbook (or select the type of file which you want).

In the Import Data dialog box, select the file and press Import.

You will get a window named Navigator. Choose the particular sheet here and click on Transform Data.

You will get the Power Query Editor. From the Transform tab, select Use First Row as Headers and pick Use First Row as Headers.

From the Import Data dialog box, select New worksheet and press OK.

You will get the connected data in a new sheet.
At the rightmost corner of the worksheet, you will see a window named Queries & Connections. From Connections, you can see the imported workbook name, and also you can refresh the worksheet.

How to Refresh Excel Data Connection

From the Data tab, under Queries & Connections group, select Refresh All and Refresh.

This way, if there is any change in the original sheet, then the connected sheet will be updated, too.

How to Manage Data Connection in Excel

From the Data tab, under Queries & Connections group, select Queries & Connections.
At the right corner of the sheet, you will get the Queries & Connections window.
From the Queries & Connections window, select Connections, right-click on the external sheet name.
From the Context Menu, choose Properties. You will see the Connection Properties dialog box.
In the Connection Properties dialog box, there will be a Definition segment and a Used in segment. From these segments, you can check the location of the original sheet, the type of connected sheet, where the sheet is used, and so on.

Refresh Data Automatically at Regular Intervals in Excel

From the Data tab, under Queries & Connections group, select Queries & Connections.
At the right corner of the sheet, you will get the Queries & Connections window.
From the Queries & Connections window, select Connections and right-click on the external sheet name.
From the Context Menu, choose Properties. You will see the Connection Properties dialog box.
In the Connection Properties dialog box, go to the Usage segment and check Refresh every.
Set the time interval and press OK.

How to Remove Connected Worksheet from Excel

Go to the Data tab.
Under the Queries & Connections group, select Queries & Connections.
At the right corner of the sheet, you will get the Queries & Connections window.
From the Queries & Connections window, select Connections and right-click on the external sheet name.
From the Context Menu, choose Delete.
You will get a warning from Microsoft Excel. Press OK to confirm.

This will remove your data from the current workbook.

How to Enable External Data Connections in Excel

Open your workbook and go to the File tab.
Click on Options and you will get the dialog box named Excel Options.
Go to the Trust Center menu and select Trust Center Settings.
You will get the following attached dialog box (Trust Center). Go to External Content.
Under Security settings for Data Connections segment, choose Enable all Data Connections or Prompt user about Data Connections and press OK.
Press OK.

Note: Microsoft Excel doesn’t recommend Enable all Data Connections because your workbook may get harmed by unwanted viruses from different data connections.

Frequently Asked Questions

1. How do I find data connections in Excel?

From the Data tab >> under Queries & Connections group >> select Queries & Connections >> at the right corner of sheet, you will get the Queries & Connections window >> you can check the data from the Connections segment.

2. How do I remove access restrictions in Excel?

From the top ribbon >> go to File >> Info >> Protect Workbook >> Encrypt with Password >> a dialog box will appear >> keep the Password box empty >> press OK >> a warning box will appear >> press Yes >> press CTRL+S to resave the file.

3. How do I edit existing connections in Excel?

Go to Data tab >> under Queries & Connections group >> select Queries & Connections >> at the right corner of the sheet, you will get the Queries & Connections window >> go to Connections segment >> right click on the sheet name >> choose Properties >> change what you want.