Don’t forget to share this post

Excel is the go-to application for data storage and management. Although it offers many features for advanced analysis and structuring data, you may need to work on larger datasets that need to be stored externally. For example, you own a cloud computing company and use another website for storage.

As with any database, you’d have to update it regularly. To do this manually by moving or copying the information would be time-consuming, and there is a greater risk of human error. Setting up data connections in Excel will allow you to create links between different data sources, including Microsoft Access or SQL databases, and automate data refresh.

In this article, you’ll learn what Excel external data connections are and how to use them in Excel. In a few simple steps, you’ll be able to create your external connections in Excel, change or update the source, and remove this connection when you no longer need it. Alternatively, Layer is an all-around automation tool where you can customize connections between data sources in one single place.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

What are Excel connections?

Connections in Excel can be of two types, internal or external. Whereas the first type links to data in another workbook, the external connection in Excel can link to another file type or database. Once Excel can access the data, you can automate updates or change the data source.

How do you use connections in Excel?

External connections in Excel can ‌link to Microsoft servers, such as SQL and Access Databases, or any other server where you store data. Using the Get External Data and Connections features, you can create and manage connections easily.

Let’s start by creating an external data connection in Excel.

How do I create a data connection in Excel?

In a few simple steps, you can connect an external data source to your Excel workbook.

  1. 1. Open an Excel workbook and go to Data > Get Data.
Excel Connections Get Data from External Sources Get Data
Excel Connections: Get Data from External Sources - Get Data
  1. 2. From the drop-down menu, select the source you would like to connect to. Here, I’ve selected “From Microsoft Access Database”.
Excel Connections Get Data from External Sources From Database
Excel Connections: Get Data from External Sources - From Database

Note that the steps may differ from now on if you have chosen a different data source. For example, if you have data stored in an SQL server, then you need to provide the address for that specific server. To learn more about creating external connections in Excel, take a look at this Microsoft Support page.

  1. 3. From the “Import Data” dialog box, select the Access file to import. Click “Open” to begin the import process.
Excel Connections Get Data from External Sources Select file
Excel Connections: Get Data from External Sources - Select file
  1. 4. Excel launches the “Navigator” pane, so you can select the table to import from the database. Click on the desired data table on the left-side list to preview.
Excel Connections Get Data from External Sources Navigator Pane
Excel Connections: Get Data from External Sources - Navigator Pane
  1. 5. In case you would like to preview more than one table, tick the “Select multiple items” right below the search bar.
Excel Connections Get Data from External Sources Navigator Pane 2
Excel Connections: Get Data from External Sources - Navigator Pane
  1. 6. At this point, you can “Load” to import the data, or you can make any changes if needed. Select “Transform Data” to make changes.
Excel Connections Get Data from External Sources Transform Data
Excel Connections: Get Data from External Sources - Transform Data
  1. 7. Excel automatically launches the Power Query Editor, which can help you automate future loading and transformation of data.
Excel Connections Get Data from External Sources Power Query Editor
Excel Connections: Get Data from External Sources - Power Query Editor
  1. 8. Once you finish making the changes, click “Close & Load” to continue setting up your connection.
Excel Connections Get Data from External Sources Close Load
Excel Connections: Get Data from External Sources - Close & Load
  1. 9. In the “Import Data” box. The first four options will allow you to view the data in your workbook as a “Table”, “PivotTable Report”, “PivotChart”, or “Only Create Connection”. The following two options relate to where the data will be imported, the “Existing worksheet” or a “New worksheet”, set by default.
Excel Connections Get Data from External Sources Import Data
Excel Connections: Get Data from External Sources - Import Data

Note that you can change this further on by going to File > Properties.

  1. 10. If you select “Only Create Connection”, you establish the connection to the Access Database without displaying the data in your workbook. Click “OK” to finish.
Excel Connections Get Data from External Sources Only Create Connection
Excel Connections: Get Data from External Sources - Only Create Connection
  1. 11. You should now be able to see the “Queries and Connections” right-side pane, which provides a breakdown of your newly created external data connection.
Excel Connections Get Data from External Sources Queries and Connections
Excel Connections: Get Data from External Sources - Queries and Connections

Once the connection is established, you can easily change the data source at any time.

How To Use Excel Power Query for Beginners?

With Power Query, you can import data from different sources and prepare the data for analysis. Here's how to use Power Query in Excel.

READ MORE
How To Use Excel Power Query for Beginners Promotion

How to change the data source in Excel?

There are many reasons why you may need to change the data source. The most common is that the original data has been transferred to another database. This is how you can quickly change the data source in Excel.

  1. 1. Open the Excel workbook that contains the external connection. Go to Data > Queries & Connections. Double-click on any of the queries listed to the right in the “Queries & Connections” pane.
Excel Connections Get Data from External Sources Double click on Query
Excel Connections: Get Data from External Sources - Double-click on Query
  1. 2. In the “Power Query Editor”, click on the “Data source settings” button to the right.
Excel Connections Get Data from External Sources Data source settings
Excel Connections: Get Data from External Sources - Data source settings
  1. 3. Click on the data source file path and then press the “Change Source…” button, right below.
Excel Connections Get Data from External Sources Change Source
Excel Connections: Get Data from External Sources - Change Source…
  1. 4. Click “Browse” to launch “File Explorer”.
Excel Connections Get Data from External Sources File Explorer
Excel Connections: Get Data from External Sources - File Explorer
  1. 5. Choose the new data source and click “Open” to finish.
Excel Connections Get Data from External Sources Select new data source
Excel Connections: Get Data from External Sources - Select new data source

Now you have successfully changed your data source. Excel also allows you to remove external data connections just as easily.

How to remove external data connections in Excel?

  1. 1. Go to Data > Queries & Connections. Double-click on any connection to remove it, or press Shift + Down Arrow to select multiple queries.
Excel Connections Get Data from External Sources Doubleclick
Excel Connections: Get Data from External Sources - Double click
  1. 2. Right-click on the selected queries and click on “Delete”.
Excel Connections Get Data from External Sources Delete Query
Excel Connections: Get Data from External Sources - Delete Query
  1. 3. Click on “Delete” to confirm that you want to remove the selected queries.
Excel Connections Get Data from External Sources Confirm Query Deletion
Excel Connections: Get Data from External Sources - Confirm Query Deletion
  1. 4. The “Queries & Connections” pane should now appear empty.
Excel Connections Get Data from External Sources Query Pane empty
Excel Connections: Get Data from External Sources - Query Pane empty
  1. 5. To reverse this process quickly, press Ctrl + Z as many times as needed to reestablish all connections. This might prompt Excel to warn you with the following security warning message “External data connections have been disabled”. Click on “Enable Content” to enable the external connections once again.
Excel Connections Get Data from External Sources Enable Content
Excel Connections: Get Data from External Sources - Enable Content
  1. 6. Click “Yes” to make the file a trusted document.
Excel Connections Get Data from External Sources Trusted Document
Excel Connections: Get Data from External Sources - Trusted Document

Now that your external connection is established, you can choose to refresh it manually or automatically.

Power Automate & Excel: Examples And Use Cases

Power Automate allows you to create workflows, e.g. for data collection. Here's how to combine Power Automate with Excel to automate tedious daily tasks.

READ MORE
Power Automate Excel Examples And Use Cases

How to refresh external data connections in Excel?

There are two ways to refresh and update your external connection in Excel. Let’s see how you can do so manually with one single click.

How to refresh external data connections in Excel manually?

  1. 1. Go to Data > Refresh All.
Excel Connections Get Data from External Sources Refresh All
Excel Connections: Get Data from External Sources - Refresh All

It’s that easy! This will refresh all connected data. To select the data range to refresh, you can do so by right-clicking on the queries, listed in the “Queries & Connections” pane. Now, let’s see how to set up automatic updates at specified time intervals in Excel.

How to refresh external data connections in Excel automatically?

  1. 1. In the “Queries & Connections” pane, select the “Connections” tab. Right-click on the connection and select “Properties”.
Excel Connections Get Data from External Sources Connection Properties
Excel Connections: Get Data from External Sources - Connection Properties
  1. 2. In the “Query Properties” window, the “Enable background refresh” should be enabled by default if the connection type is an “Only Create Connection”. This makes it possible for the system to carry on working without having to wait for the query to refresh.
Excel Connections Get Data from External Sources Enable Background Refresh
Excel Connections: Get Data from External Sources - Enable Background Refresh
  1. 3. Enable the “Refresh every” option to set the frequency of data updates. You can set it as low as 1 minute. As you have also enabled background refresh, it shouldn’t slow down the functioning of Excel. Click “OK” to finish.
Excel Connections Get Data from External Sources Refresh Interval
Excel Connections: Get Data from External Sources - Refresh Interval

Although creating and customizing external connections in Excel is straightforward, it will depend on the data source properties and data type. If you want to simplify the process of managing and controlling all data inputs to your spreadsheet, Layer might be the best alternative solution.

How to get data from external sources into Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

Understanding how to use databases is essential for anyone who uses Excel to store and manage information regularly. Occasionally, the size and nature of this data will require external storage, whether it’s on a Microsoft server, such as Access or SQL Databases, or your company webpage.

After reading this article, the concept of external connections in Excel and how to use them should be clearer. You should now feel ready to create, change, update, and remove external data connections using the “Queries and Connections” feature in Excel. In case you need to transform data before importing, the Power Query Editor helps you quickly transform the data before loading it from an external source.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 1 2022, Updated Nov 23 2022