Changing the File Path to a Data Connection in Excel

This document shows how to change the path to a linked data source in an Excel workbook. This can be useful if the file location of a linked data source changes.

Step 1:

When the location of a linked Excel file has changed and Excel can no longer find the file it will display this message:

Screenshot of an Excel window displaying a message: “The connection to the data source cannot be made. The file may be corrupt or unavailable.” Steps for changing the file path to a data connection are listed below the message.

You can redirect Excel to the new location of the file by clicking the Edit Links button in this window or by clicking on the Data tab at the top of the main Excel window and then clinking on Edit Links.

Screenshot of an Excel window with instructions on changing the file path for a data connection. Text reads: “You can redirect Excel to the new location of the file by clicking the Edit Links button in this window or by clicking on the Data tab at the top of the main Excel window and then clinking on Edit Links.”

Step 2A:

In the Edit Properties window click Change Source.

Screenshot of a window with options including “Change Source.” Text reads: “Step 2A: In the Edit Properties window click Change Source.”

This will open the file browser so you can find the file in its new location.

Screenshot of a window titled "Change Data Source". It shows a file path field and a "Browse" button. Text reads: "This will open the file browser so you can find the file in its new location."

Once you have found the desired file, select it and click Ok

Screenshot of a window with options to select a file. Text reads: "Once you have found the desired file, select it and click Ok."

Step 2B:

Sometimes when changing the source of a linked file you will encounter this error:

Screenshot of an error message reading: “Cannot change the source of a data connection. The file path contains invalid characters.”

To fix this you must unprotect any sheets that use data from the source you are trying to change.

To do this, right click on the sheet at the bottom of the main Excel window.

Excel screenshot showing right-click menu on a sheet tab at the bottom of the window. Text reads: "To fix this you must unprotect any sheets that use data from the source you are trying to change. To do this, right click on the sheet at the bottom of the main Excel window."

Then select Unprotect Sheet.

Screenshot of Excel. Menu shows: "Data > Get & Transform Data > From File > From Workbook". Below that, "Select Unprotect Sheet".

You will then be prompted to enter the password used to protect that sheet.

Screenshot of a Microsoft Excel window. A dialog box titled "Change Data Source" is open, prompting for a new file path. Fields include "File name:" with a path partially visible, and buttons labeled "Browse...", "Cancel", and "OK".

After you have done this you will be able to follow the steps in 2A of this document to update the location of your linked data sources.

Step 3:

You can easily add links from one Excel file to another.

In the destination file select a cell and enter an equal sign (=).

Screenshot of Excel showing formula bar with “=“ and text: “Return only the alt text description, no other content.” Instructions below explain linking Excel files and updating data connections.

In the source file you wish to get the data from select the cell you wish to pull the data from and press the Enter key.

Screenshot of an Excel spreadsheet with a cell selected. Text reads: "In the source file you wish to get the data from select the cell you wish to pull the data from and press the Enter key."

Now the destination workbook will populate that cell from the source file.

You can also link more than one cell at a time. In your source file hold the Shift key and select all of the cells you wish to link to in your destination file.

Screenshot of Excel showing linked cells. Text reads: "Now the destination workbook will populate that cell from the source file. You can also link more than one cell at a time. In your source file hold the Shift key and select all of the cells you wish to link to in your destination file."

In your destination file right click and select Past Link.

Screenshot of a computer window with instructions: “In your destination file right click and select Past Link.”