How to reference another workbook without opening: 5 DANGERS!

In this post, we look at using Excel to reference another workbook without opening it. While this may seem like a great idea, we need to be very careful. When using this technique, a lot can go wrong without us realizing it.

Let’s explore this area. Please read to the end and ensure you know all the dangers.

Table of Contents

• Alternative to reference another workbook without opening
• How to reference another workbook
• Copy and paste links
• Cell linking
• Writing a formula
• Cell linking quirks
• Absolute and relative referencing
• Workbooks saved in OneDrive or SharePoint
• Dynamic arrays
• Password protected files
• Named ranges
• Updating links
• Automatic updates
• Opening the file
• Edit Links window
• Single cell update
• Recalculation
• Calculations with linked cells
• The dangers of linked workbooks?
• The linked data changes location
• Sub-links are not updated
• Data from last saved version
• Excel caches the external data
• When is it OK to link files?
• Conclusion

All examples in the post, we are using two workbooks, Closed.xlsx and Open.xlsx.

Alternative to reference another workbook without opening

Before we launch into the main topic of how to reference another workbook without opening it, I would like to tell you about an alternative method.

Later in this post, there is a list of potential pitfalls and dangers from referencing to another workbook. In my opinion, a better option is to use Power Query.

Power Query is a tool that can extract data from an Excel workbook rather than linking to individual cells.

In most circumstances, Power Query achieves a similar result to cell linking but avoids many of the pitfalls. To learn more about Power Query, click here: Introduction to Power Query.

However, if you want to keep referencing another workbook in the old way, then let’s press on.

How to reference another workbook

There are many ways to set up links to other workbooks. In this section, we will explore three methods and the differences between using Excel Desktop and Excel Online.

Copy and paste links

The first method of creating links to external workbooks involves copying and pasting.

1. Open both the Open.xlsx and Closed.xlsx workbooks.
2. In Closed.xlsx, copy the required cells with Home > Copy (or Ctrl +C)
3. In Open.xlsx, select the required cell. Then, paste the links to the cells by clicking Home > Paste (drop-down) > Paste Link (or Alt, H, V, N)
4. The formula bar displays the cell reference:
   =[Closed.xlsx]Sheet1!B4

When we close the Closed.xlsx workbook, the formula changes to a full file reference:
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!B4

This first method is easy but requires both workbooks to be open to create the links initially.

Cell linking

Cell linking to another file is just like linking to a cell in the same workbook.

1. Open both the Open.xlsx and Closed.xlsx workbooks.
2. In the Open.xlsx workbook, select the required cell and type the equals symbol ( = )
3. Click a cell in the Closed.xlsx workbook. The formula bar will look like this:
   =[Closed.xlsx]Sheet1!$B$4
4. Press return to accept the formula.

On closing the Closed.xlsx workbook, the formula becomes a full file reference:
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!$B$4

In Excel Online, each browser window is a separate instance of the Excel application. As a result, it is not possible to cell link between workbooks using this approach.

Like the first method, this also requires both workbooks to be open initially.

Writing a formula

With this third method, we write a formula directly into the Open.xlsx workbook and do not need the Closed.xlsx workbook open.

1. In the Open.xlsx workbook, select the required cell.
2. Enter a formula that has the following syntax:
   =’filePath[fileName]sheetName’!cellRef
3. Using our example files, the formula would be:
   =’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!B4

If you’re unsure of the worksheet name, enter anything as the sheet name.

• If there is one sheet in the workbook, Excel will use that.
• If there are multiple sheets, Excel displays a list to select from.

In the screenshot below, Unknown has been entered as a worksheet name; Excel has provided a list of the valid sheet names to select from.

Cell linking quirks

There are a few interesting quirks to make you aware of:

Absolute and relative referencing

The standard referencing techniques apply.

Therefore, if we are working with the following formula:
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!B4

Copying the formula one cell down and one cell right changes the reference to:
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!C5

However, if the original formula has absolute referencing, it does not matter where we copy the formula; it will always reference the same cell.
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!$B$4

Workbooks saved in OneDrive or SharePoint

If the Closed.xlsx workbook is saved on OneDrive or SharePoint, the full file path will reference the online location:
=’https://exceloffthegrid-my.sharepoint.com/personal/mark/Documents/Documents/[Closed.xlsx]Sheet1′!B4

Even if the file exists locally, it always reverts to the online file path.

Dynamic arrays

Linked cell references use the dynamic array engine. Therefore, if we provide a range of cells, the values will spill into the subsequent rows and columns.
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!B4:E11

Password protected files

If linking to a password-protected file, Excel requires a password to update the links.

Named ranges

It is possible to link to named ranges rather than cell ranges.

The following links to a named range called myNameRange:
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!myNamedRange

Updating links

Having created a link, the next question is how to update it. As with most things in Excel, there are multiple ways.

Automatic updates

If both the Open.xlsx and Closed.xlsx workbooks are open simultaneously, the values update automatically when recalculation occurs.

Opening the file

The following message will be displayed when opening an Excel file containing links to other workbooks.

Clicking Update will update all the links in the workbook, while Don’t Update will not retrieve the linked values.

Edit Links window

To update any existing links:

• From the Ribbon, click Data > Edit Links.
• The Edit Links dialog box opens. This window can also be found in File > Info > Edit Links to Files.
• Select each link and click Update Values.

We also use the Edit Links dialog box to change any existing links.

Single cell update

A single cell can be updated by entering and leaving edit mode. Some methods to achieve this are:

• Double-click a cell to enter edit mode, then click away into another cell to leave.
• Press F2 to enter edit mode, and press enter to leave.
• Click on a cell, click inside the formula bar to enter edit mode, and click away to leave.

Recalculation

The link to a closed workbook is updated when the cell containing the link requires recalculation. The calculation chain in Excel is a tricky concept. Generally, a cell only recalculates if it includes a reference to a cell or preceding cell which has changed.

The following link will never update through a standard recalculation as it has no preceding cells in the same workbook.
=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!A1

However, the following link will recalculate each time Cell B1 (or a preceding cell to B1) changes.
=IF(B1=1,=’C:\Users\marks\Documents[Closed.xlsx]Sheet1′!A1,”Do nothing”)

Calculations with linked cells

In the previous section, we saw an IF function with a linked cell included within the true argument. But we can push this further and use a linked file within other formulas and functions.

The following shows the XLOOKUP function with the lookup_array and return_array arguments referencing cells in another workbook.

=XLOOKUP(D3,
‘C:\Users\marks\Documents\[Closed.xlsx]Sheet1’!$B$4:$B$11,
‘C:\Users\marks\Documents\[Closed.xlsx]Sheet1’!$E$4:$E$11)

Try it for yourself; XLOOKUP behaves the same as normal; it does not care if the cells are in a closed or open workbook.

Many other functions that handle arrays work with external workbook references too. However, functions that require a range, such as SUMIFS, do not work with external cell references.

The dangers of linked workbooks?

As mentioned in the introduction, there are some significant problems with referencing another workbook.

I’ve heard many people say: “Look, I’ve linked to this workbook so that I don’t have to update a cell. Isn’t this cool!”. But, unfortunately, the excitement soon dies when they realize the risks.

These are the traps that many Excel users fall into.

The linked data changes location

If a cell is referenced within its own workbook, Excel tracks what happens to that cell. For example, if Cell A1 is dragged to Cell B1, any cells referencing A1 will automatically update to B1. Perfect!

But this is not what happens when we reference another Excel workbook.

If a workbook is closed, it has no idea when a cell in another workbook moves. For example:

• When Open.xlsx is closed
• In Closed.xlsx, Cell A1 is dragged to Cell B1
• Open.xlsx continues to link to Cell A1 and will not change. It has no idea that A1 is now B1.
• Open.xlsx is now linking to the wrong cell – oops!

The risk can be mitigated slightly by using Named Ranges. However, if it is a colleague’s file, what is to stop them from deleting the Named Ranges, or renaming worksheets, just because they want to (work colleagues are nice like that!)

Therefore, linking to any file you do not tightly control is very dangerous.

Sub-links are not updated

When some Excel users discover the ability to link to other workbooks, they go a bit loopy; linking everything in sight. It is not long before they have a spider’s web of links across multiple workbooks. However, they don’t realize that when a link updates, it does not update any sub-links contained in the linked file.

Let’s look at an example. Let’s suggest that

• Open.xlsx contains a link to Closed.xlsx.
• Closed.xlsx contains a link to AnotherClosed.xlsx.
• When opening Open.xlsx on its own, the Update links message box appears.
• Clicking Update will bring the values from Closed.xlsx into Open.xlsx.
• Nothing happens to the links to AnotherClosed.xlsx. This is because these links are not updated. Therefore, the final values in Open.xlsx may not be correct.
• For all links to update correctly, all the files must be open simultaneously.

Data from last saved version

The values are only retrieved from the last saved version of a workbook. 

Let’s look at an example. Let’s suggest that:

• Your colleague opens Closed.xlsx and changes some values but leaves the workbook open.
• You are working in Open.xlsx and have updated the links
• If Closed.xlsx is locally saved, the available linked values will remain as old ones until Closed.xlsx is saved.
• If Closed.xlsx is saved on OneDrive or SharePoint, the new values are available as soon as the file is synced back to the server.

This is not a problem by itself. However, there is no warning message to indicate Closed.xlsx is currently open, so it may not be obvious if the file is up-to-date or not.

Excel caches the external data

An Excel workbook is a zipped file constructed in a specific way to be read by the Excel application. When a link is created, Open.xlsx keeps a copy of the data from Closed.xlsx.

We will look into the Excel file format later in the post, but for now, let’s prove that the Open.xlsx caches the data:

• Create an XLOOKUP (or VLOOKUP if you prefer) between Open.xlsx and Closed.xlsx workbooks.
• Save both files and close the Closed.xlsx workbook
• Rename Closed.xlsx to Closed_Backup.xlsx

The formulas in Open.xlsx do not change; the link is no longer valid as the Closed.xlsx file does not exist.

Question: What happens if we change the lookup value?

Answer: It should cause an error… right?!! Nope! A value is returned

Hmm…. that should not work, but it does. This means you can keep retrieving values from files that no longer exist!!! It’s because Excel keeps a cached copy of all linked data.

While working with Excel, you might see this message:

“We can’t update some of the links in your workbook right now.
You can continue without updating their values, or edit the links you think are wrong.”

If you click Continue, calculations return values from the cached source file.

If Excel caches external data in this way, it means:

1. You may be using old data without ever knowing it – that’s a big risk!!
2. Any users receiving the file could access the cache and see confidential data you didn’t know was there – that might be a bigger risk!!

Where is the cached data?

So, where is this cache of data which I keep referring to?

1. Create links between Closed.xlsx and Open.xlsx
2. Save and close Open.xlsx
3. Rename Open.xlsx to Open.zip
4. Open the zip file and find the \xl\externalLinks\externalLink1.xml file.
5. The file will look something like this:
   

In the screenshot above, the worksheet values have been highlighted. All the external data from the original Closed.xlsx workbook is included. Therefore, we could easily send information accidentally and bloat the file sizes.

To stop Excel caching data in this way, click File > Options to open the Excel Options dialog box. Then, uncheck save external links values in the Advanced section.

When is it OK to link files?

As you’ve seen, linking files is not as simple as you might think. Excel doesn’t always behave the way we might want. So, when is it OK to link files? I use the following rules:

• I must ‘control’ all the files involved
• There must be zero sub-links
• Named ranges must be used, not standard cell references
• If sharing the file, it must not contain sensitive data

These rules avoid most of the potential issues.

As noted at the start; now that we have Power Query as a tool, it is worth asking if that is a better solution for your particular scenario. Go here to find out more about Power Query: Introduction to Power Query.

Conclusion

Linking files is not for the faint-hearted. What seems like a simple solution, opens up a whole world of complexity. While it is easy, you need to ensure the data is correct and you are not sharing information unexpectedly.

---

What next?

Discover how you can automate your work with our Excel courses and tools.