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

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn about how Excel deals with data connections. Here, we will see how to create data connections in Excel. Then, how to refresh them, and manage them. Can we automatically refresh the data? Here, you will get this answer too. Also, you will get to know how to remove data connections.

With the data connection feature of Excel, you can make a connection to external data sources such as databases, websites, text files, XML files, and more. Then you can create interactive reports and dashboards with those imported data.

To make the dashboards, you can use Excel’s powerful features like pivot tables, charts, conditional formatting, and slicers. Actually, these visualizations help you to analyze and present your data in a meaningful and insightful way.

You can create dynamic reports with a data connection that will automatically update when the data changes. Moreover, if you have multiple data connections, you can merge data from different sources into a single dataset using Power Query.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.

Source Workbook:

Sample Dataset.xlsx

Workbook with Connected Sheets:

Created Data connections.xlsx

Table of Contents Expand

Create Data Connections in Excel

1. Using Existing Connections to Connect Two Excel Workbooks

From the Data tab, under Get & Transform Data group >> select Existing Connections.
So, 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, then must check the option First row of data contains column headers >> press OK.

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

As a result, you will get the external source sheet in your workbook.

2. Use of Power Query to Connect Data in Excel

From the Data tab >> select Get Data >> From File >> From Excel Workbook (select the type of file which you want).

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

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

At this time, you will get the Power Query Editor. From the Transform tab >> select Use First Row as Headers >> Use First Row as Headers.

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

Lastly, 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 >> Refresh.

In 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 Context Menu >> choose Properties. So, 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 >> right-click on the external sheet name.
From Context Menu, choose Properties. So, you will see the Connection Properties dialog box.
In the Connection Properties dialog box, go to the Usage segment >> check Refresh every >> set the time interval >> press OK.

How to Remove Connected Worksheet from Excel

First, 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 >> right-click on the external sheet name.
From Context Menu Bar, choose Delete.
As a result, you will get a warning from Microsoft Excel. Press OK to that.

This will remove your data from the current workbook.

How to Enable External Data Connections in Excel

Open your workbook and go to File tab.
Click on Options and you will get the dialog box named Excel Options.
From Excel Options, go to the Trust Center menu and select Trust Center Settings.
As a result, 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 to Excel Options dialog box.

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.

Conclusion

Here, we have tried to show how Excel creates data connections. Which allows us to import data from external sources, create dynamic reports, and make dashboards. By creating connections and refreshing data, we can analyze and present information in a meaningful way. Through this, we can enhance our ability to make informed decisions based on up-to-date data.

Excel Data Connections: Knowledge Hub

<< Go Back to Importing Data in Excel | Learn Excel