Excel’s workbook links feature lets users link and update data tables from different sheets. It is an advanced tool for data organization and analysis. To access the tool, you need to go to the main menu and click on the “Data” tab.
Such linking or data connectivity between different worksheets is called External Linking. Whenever you open the Excel file, those links need to fetch the latest data from the source file.
The Workbook links tool is your ultimate tool to refresh those links and fetch the latest data. It offers a seamless integration between different files.
This is a quick guide about workbook links in Excel. We will discuss various things,
- Linking data tables across different worksheets
- how to open external links in Excel
- how to update workbook links in Excel
Let’s begin and make sure you read till the end.
What are Workbook Links?
Workbook links in Excel help users establish and manage connectivity between different worksheets. Simply put, it lets you reference data stored in another workbook.
It is a tool to ensure proper synchronization of data between multiple workbooks stored locally or on the cloud.
For example, suppose I linked data from an Excel named “Workbook”. Here’s how the link should look,
=[Workbook2.xlsx]Sheet!A1Note that it links to cell A1 of Sheet 1 from Workbook2.
Benefits Of Workbook Links in Excel
Linking workbooks in Excel can be advantageous while working with large datasets or collaborating with teams. Below are a few benefits of linking workbooks:
- Data Synchronization: Whenever a workbook is linked in Excel, the data gets updated automatically when changes are made to the source workbook.
- Reduced File Size: Linking data instead of copying it into a large spreadsheet can decrease the file size, thereby reducing crashes.
- Error Reduction: Workbook links minimize manual data entry errors by automatically updating linked data based on changes in the source workbook.
- Easy Collaboration: By linking workbooks, teams can work on different parts of a project simultaneously and instantly update the changes in the main file’s source.
How to Create & Manage Workbook Links In Excel
There are two ways to create a workbook or external links in Excel, depending on whether you want to link a single cell or a range of cells.
Let’s go through the steps for each of them in detail.
Steps to Link a Single Cell in Another Workbook
This method helps in linking a single cell from a different spreadsheet.
Here’s how you can do it:
- Open the destination and source workbooks
(We need to link a cell from the source workbook to the destination workbook) - In the destination workbook, select the cell where you wish to display the content from the source workbook
- Type “=”
- Now, switch to the source workbook
- Select the cell you want to link to
- Press the “Enter” button on your keyboard
Our final formula would be as follows,
='[Workbook2.xlsx]Sheet1'!$D$2Where,
- “Workbook2.xlsx” is the Source workbook that is being linked
- “Sheet1” is the sheet name of the linked workbook
- “$D$2” is the cell being referred
You don’t need to worry about the formula because Excel will automatically generate the formula that links the cell in your destination workbook to the selected cell in the source workbook.
Steps to Link a Cell Range from Another Workbook
This method of linking a cell range from another workbook is an excellent way of creating dynamic and interconnected spreadsheets. It allows you to reference and update data from multiple files without manually copying and pasting each time.
It allows you to create more efficient and consistent reports.
Below are the steps to link a cell range:
- Open both the source and destination workbooks
- Select a cell from the destination workbook
- Type “=”
- Navigate to the source workbook
- Select the range of cells you wish to link
- Press the “Enter” key
The final formula would be,
='[Workbook2.xlsx]Sheet1'!$D$2:$D$8Where,
- “Workbook2.xlsx” is the Source workbook
- “Sheet1” is the sheet available in the source workbook
- “$D$2:$D$8” is the range of cells being referred to or linked
This method is very similar to the previous method of linking a single cell, with the only difference being the selection of multiple cells.
How to Update Workbook Links
By default, Excel prompts users to update the workbook links whenever the file is opened. To refresh the data, click the “Update” button, as shown in the following image.
On the other hand, if you wish to update the links manually, you can do that as well by following the steps below:
- Go to the “Data” tab from the main menu
- Click on “Workbook Links” in the “Queries & Connections” group
- A new pane will appear on the right side of the screen
- Click on the “Refresh all” button as shown in the following image
Your workbook links will be updated immediately. This feature is helpful if you have made changes to the source workbook and want the destination workbook to reflect the changes immediately.
How to Open External Links in a Workbook
Note that you can find, remove, or change the external links within a few minutes. Thanks to the Find and Replace tool, it is really easy.
Let us learn to find and update external links in Excel.
To find the cells with external links, you can use Excel’s Find and Replace tool, as discussed below:
- Open the desired Excel
- Press “Ctrl + F” on your keyboard to open the “Find and Replace” dialog box
- Click on the “Options” button
- In the “Find what” box, enter “.xl”
(With this entry, you can search the links, including keywords .xls, .xlsx, .xlsm, etc.) - In the “Within” box, select either “Workbook” to search across the workbook or “Sheet” to look within the current worksheet
- In the “Look in” box, choose “Formulas”
- Click on the “Find All” button
That’s it! You will have a list of cells, including external links. Refer to the following image.
Since we have only one workbook linked, only one link is displayed.
To go to the cell that contains the external link, simply click on the cell address from the “Cell” column, as shown in the image below.
If you wish to see all the external references, place the cursor anywhere within the results and press Ctrl+A. This will show you all the links present in the workbook.
How to Break the External Links in a Workbook
Removing the external links from a workbook is as easy as linking it.
You need to search the links using the Find and Replace tool and then delete them manually, one by one.
Yes, of course, it sounds time-consuming. But as of 2024, there is no way to delete all the external links at once. Since we have only one workbook linked, only one link is displayed.
- Open the desired workbook
- Go to the ”Data” tab from the main menu
- In the “Queries & Connections” group, click on “Workbook Links”
- A new pane will be opened on the right side of the screen
(Here, you will see all the linked workbooks. Since we have only one workbook linked, it is displayed here) - Click on the three dots beside the name of the workbook, as shown below
- Choose the “Break Links” option
- Excel will show a prompt asking for confirmation to break the link
- Click on “Break Links”
Note that you cannot undo this action once you break the link. Your formula will be replaced with the current value.
How to Change the External Links in a Workbook
Changing external links in a workbook involves updating the source file name or cell range to reflect the correct data.
It is helpful when the original file is renamed, moved, or replaced. Let’s learn the steps to be followed to change the external links in a workbook:
- Open the Excel file containing the external links
- Go to the “Data” tab in the main menu
- Click on “Workbook Links” in the “Queries & Connections” group
- This will open a pane on the right side of the screen, showing all the linked workbooks
- Click the three dots beside the linked workbook’s name
- Select the “Change Source” option
- Choose the file you want to link
- Click on “Select”
Excel will now update the external link to reflect the new source. Note that the formulas will automatically pull data from the updated file, but you need to update the cell range manually to reflect the data accurately.
The method helps you to update external links efficiently without losing any data or formatting.
How to Find Links in Named Ranges
Named ranges are used to simplify complex and lengthy formulas by assigning a name to a cell or range of cells used within it.
Sometimes, named ranges may also contain links to other workbooks, which can be hard to track.
Let’s see how to find the links in named ranges,
- Open the desired Excel file
- Go to the “Formulas” tab
- Click on “Name Manager” in the “Defined Names” group
- This will open a dialog box showing all the named ranges in your workbook
- Check the “Refers to” column for any external links
Note that the references to other workbooks are enclosed in “[ ]”. You can select the named range with the external link and click on the following options.
- “Edit” to modify the reference
- “Delete” to remove the reference
How to Identify External Links in Excel Objects
External links in Excel can sometimes be hidden within objects like charts, buttons, or shapes, making them difficult to find.
Here are the steps to quickly identify such links:
- Open the desired Excel sheet
- Go to the “Home” tab
- In the “Editing” group, select “Find & Select” dropdown
- Click on “Go to Special”
- A new dialog box will appear as shown below. You need to select the “Objects” option
- Click the “OK” button once done
Now, you will see that Excel selects all the objects in the spreadsheet. You can press the Tab key to navigate through all the objects.
You will see the reference workbook mentioned in the formula bar.
How to Find Links to Other Files in Excel Charts
To find links to other files from Excel charts, you can follow the steps discussed below:
- Click on the chart for which you want to identify the links
- In the formula bar, you will observe the external link of the Excel file, as shown below
If you are referring to a column graph or bar graph that has multiple data series, you can quickly move between them in the following way:
- Select the target chart
- Go to the “Format” tab
- Select the desired data series from the dropdown as shown below
Common Issues with Workbook Links and How to Fix Them
Though workbook links are powerful, they can sometimes cause issues.
Below are some common problems you might encounter while accessing the Workbook Links feature in Excel.
- If you move or rename a source workbook, the link will break and lead to errors. In this case, make sure to update the source location by clicking on “Data> Workbook Links> Change Source”
- The linked data is not updating, even though the source workbook has been changed. In this case, make sure to manually update the links by clicking on “Data > Workbook Links > Refresh All”
Conclusion
Workbook Links is the ultimate tool for people who are dealing with data across multiple workbooks.
It helps them streamline the synchronization process and reduce errors.
We have learned everything from linking a single cell or cell range, updating links, deleting external links, and troubleshooting common issues.
But if you need help somewhere or have any particular error while using the Workbook Links feature, please let us know in the comments section. Our team will assist you as soon as possible.
Also, make sure you explore our blog section for more tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.