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, Sheetgo is an all-in-one automation platform where you can customize connections between data sources in one single place.

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.

1. 2. From the drop-down menu, select the source you would like to connect to. Here, I’ve selected “From Microsoft Access 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.

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.

1. 5. In case you would like to preview more than one table, tick the “Select multiple items” right below the search bar.

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.

1. 7. Excel automatically launches the Power Query Editor, which can help you automate future loading and transformation of data.

1. 8. Once you finish making the changes, click “Close & Load” to continue setting up your connection.

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.

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.

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.

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

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.

1. 2. In the “Power Query Editor”, click on the “Data source settings” button to the right.

1. 3. Click on the data source file path and then press the “Change Source…” button, right below.

1. 4. Click “Browse” to launch “File Explorer”.

1. 5. Choose the new data source and click “Open” to finish.

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.

1. 2. Right-click on the selected queries and click on “Delete”.

1. 3. Click on “Delete” to confirm that you want to remove the selected queries.

1. 4. The “Queries & Connections” pane should now appear 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.

1. 6. Click “Yes” to make the file a trusted document.

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

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.

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

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.

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.

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, Sheetgo might be the best alternative solution. You can quickly connect to BigQuery and your spreadsheets - Excel, Google Sheets, CSV/TSV - and automate data transfers, as well as monitor and manage your workflows from the web app.

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. 

Finally, you also know about Sheetgo: an alternative that allows you to quickly connect your files and your data in BigQuery, manipulate and transfer the data, and automate entire processes through custom workflows.

Sign up for Sheetgo for free and easily connect your data across multiple formats and locations.