Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Getting values from a closed Excel workbook

Remove passwords from an Excel Workbook

One of the things which scares me in Excel is seeing this message:

Linked File warning message

So many things can go wrong with linked files, yet unsuspecting users tend to use them with pride.  “Look, I’ve linked this so that I don’t have to update a cell, isn’t this cool!”. If they knew the risks they might not be so quick.

Let’s explore this area and hopefully by the end you will see why it’s so risky.  Or if not, at the very least, you’ll be aware of the possible issues.

How to set-up a link to an external file

Setting a link to a cell in another file is easy, it’s just like linking to any other cell in a workbook.  In a cell type the equals symbol ( = ), click on a cell in another workbook, then press return.

The formula will look something like this.


When you close the linked file the address will change to include the full file path of the linked file.


Provided you can remember the syntax, it is possible to create a link without even opening a file.  That means Excel files can be read without opening them, pretty cool eh?  Though, Excel will ask for the password when updating the link to a protected file.

Updating links

Having created a link, the next question is how to update it.  There are multiple ways.

Automatic updates

If both the linking and linked files are open at the same time, the values will update automatically when a recalculation occurs.

Opening the file

When opening an Excel file which contains links to other files the following message will be displayed.

Linked File warning message

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

Edit Links window

From the Data Ribbon click Edit Links button.

Data Edit Links

The Edit Links window will open.  This window can also be found in File -> Info -> Edit Links to Files.

Edit Links Window

Select each link and click Update Values.

Single cell update

A single cell can be updated by entering and leaving edit mode:

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


The link to a closed workbook will be updated when the cell containing the link requires recalculation.  The calculation chain in Excel is a tricky concept.  Generally, a cell is only recalculated if it contains 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.


The following link will recalculate each time Cell B1 (or a preceding cell to B1) changes.

=IF(B1=1,'C:\Users\marks\Documents\Testing\[LinkedFile.xlsx]Sheet1'!$A$1,"Do nothing")

Calculations with linked cells

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

The following shows the VLOOKUP function with the table_array argument referencing cells in a linked workbook.


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

Other functions work in the same way, the SUM function for example.


Named ranges are also available.  The function below includes a named range called myLinkedData.


The problem with linked files?

As mentioned in the introduction, there are some significant problems with linked files:

The linked data can change 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!

If the linking file is closed Excel has no idea when a cell has moved.  For example, if Cell A1 is dragged to Cell B1, the file which links to Cell A1 will continue to link to Cell A1 (which is now the wrong cell – ooops!).

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

Therefore, linking to any file which is not tightly controlled by you is dangerous.

Sub-links are not updated

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


  • Workbook A contains a link to Workbook B.
  • Workbook B contains a link to Workbook C.
  • When opening Workbook A on it’s own the Update links message box will appear.
  • Clicking Update will bring the values from Workbook B into Workbook A.
  • Nothing happens to the links from Workbook B to Workbook C.  These links are not updated, therefore the final values in Workbook A may not be correct.
  • For the links to update correctly, all the files must be open at the same time.

Data from last saved version is retrieved

Data can only be retrieved from the last saved version of a file.  If your colleague opens a file and changes some values, the link will not retrieve the updated value until the file is saved.

This is not a problem by itself, as that is how I would expect it to work.  However, there is no warning message to indicate the file 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 file is constructed in a specific way to be read by the Excel application.  When a link is created, the linking file keeps a copy of the data from the linked file.

We will look into the Excel file format later in the post, but for now, let’s prove that the linking file does cache the data:

  • Create a VLOOKUP between two workbooks.
  • Save both files and close the workbook with the source data
  • Rename or delete the Excel file with the source data

The link is no longer valid as the file does not exist, So what happens if you change the lookup value?

Answer: The new lookup value is returned.  Hmm…. that should not work, but it does. This means you can keep retrieving values from files which no longer exist!!!

You might see this message.

Linked files - unable to update

If you click Continue the  calculation will continue to return values from the cached source file.

If Excel caches external data in this way, it means any file may contain data from linked files even if the file cannot be found. Any users receiving that file could access the cache and see data they should not see.

Let’s suggest a HR department sends workbooks with salary information to each individual employee.  If there are links to a file containing all the salaries, the cached salary information will be distributed to every employee!

Where is the cached data?

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

  1. Create and save an Excel file with links to other files
  2. Rename the Excel file created in (1) above to have a .zip file extension, rather than a .xlsx file extension
  3. Open the zip file and find the \xl\externalLinks\externalLink1.xml file.
  4. The file will look something link this:
    Linked File xml source

The .xml file above displays all of the linked data.  If it were salary information we could see that Jack has a salary of $24,000 and Sally a salary of $45,000.

When is it OK to link files?

As you’ve seen so far, linking files doesn’t always behave the way you might think.  So, when is it OK?  I use the following guidelines:

  • I must ‘control’ all the files involved
  • There must not be sub-links (i.e. linked files with links to other files)
  • Named ranges must be used, not standard cell references
  • If sharing the file, it must not contain sensitive data

These will avoid most of the potential issues.  But will also significantly reduce the number circumstances when it is useful.  Now that we have PowerQuery, it is worth asking yourself if that is now better solution for your particular scenario.


Linking files is not for the faint-hearted.  What seems like a simple solution opens up a whole world of complexity, which most Excel users are completely unaware of.

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

20 thoughts on “Getting values from a closed Excel workbook

  1. Stefan says:

    Link to external cell value :
    I have (in my current workbook) a formula defining a text string which should later be the path to a (closed) file/sheet/cell I want to refer to.
    The result of that formula is actualy a text string starting with ‘ and ending with ‘!$k$1
    (I want to refer to cell K1 from a specific sheet of a specific closed workbook)

    How can I, in my current workbook, show the value that is in that specific cell of that closed workbook from which I “calculated” the path ?

    • Excel Off The Grid says:

      Unfortunately I’m not aware of a solution for this.

      The INDIRECT function can convert a text string into a range. However, INDIRECT does not work with closed workbooks.

      • Peter Nganga says:

        You are right. I tried engaging INDIRECT function to return results from a closed excel workbook to no avail. Still looking for that function that does that. Will be glad to be informed.

      • Jerry says:

        This is unfortunate, since Lotus can easily link to closed Lotus files using dynamic information input in cells with:


        I used this a lot, and when I convert old Lotus files to Excel I miss this capability

    • Antuan Kouros says:

      If the “calculated” file names are not many, then you can use many IF, commands inside eash other to finaly take the desired source value…

  2. Kashan says:


    When I make external reference to another worksheet it works well but when I close both worksheets(soruce and destination) and after I re-open the source file, excel asks for update. When I update it returns a zero value. Please help

    • Excel Off The Grid says:

      Is the external reference included within a formula or just a reference by itself? I think (though I might be wrong) that only formulas which use an array argument (such as VLOOKUP) work with the external reference embedded directly in the formula.

      • Megan says:

        I currently use the VLOOKUP formula to pull data from 5 workbook to 1, when all 6 files are open the data populates but when I only want to have the 1 file that is meant to have all data open by itself formulas revert to zero. I use the IFERROR(VLOOKUP) formula

        in same workbook I have an index formula in another cell that link to a different file but only one and I don’t have any issues at all.

        What is the difference and what can I do to have the formulas works with closed workbooks?

        • Excel Off The Grid says:

          INDEX is superior to VLOOKUP in many ways. It’s difficult to comment without seeing the workbooks.

          I would no longer advise linking across workbooks in this way, as you’re building a house of cards, which takes huge efforts to maintain. Power Query is a much better and more stable option.

  3. Antuan Kouros says:

    How can I update the values from a linked but also locked Workbook without need the pasword. Probably there is no way…

    • Excel Off The Grid says:

      I hope there isn’t a way, as that’s the whole point of locking it, right?

      If it’s locked with a file open password then the contents are encrypted, so I don’t think there is a way around it.

    • Excel Off The Grid says:

      That’s a good question. I’m not sure if there is a list anywhere. Even the functions that don’t work can normally use INDEX or other functions to extract the values first.

      But Power Query is a significantly better option for getting data from other worksheets these days.

  4. Kevin Foote says:

    You seem to have done a lot of research on this and obviously know what you’re talking about. Maybe if you have the time you can help me?

    Every year I prepare 25 or so workbooks of complicated calculations which I then route to 3 or 4 people who review it & send it back to me. Once it gets back to me approved by my colleagues, I use it to mailmerge a bunch of bills to our customers. There’s a ‘mailmerge’ sheet in the workbooks with about a thousand or so external references (billing information like current balance, addresses, etc. which is kept current in another workbook) which I keep locked & hidden when it’s in review because it’s only used for the purpose of automatically populating those bills, thus greatly reducing time spent and room for human error.

    The problem I’m having is when my workbooks get opened on another computer, the external references change from the simple [Workbook] to the full path, and it no longer updates even when I have both workbooks open at the same time. The workaround I’ve been using to date is to copy the path part of the reference, then do a Find & Replace All with a blank. That works like a charm, but it’s annoying to have to do it each time.

    Is there a way that these external references can be kept in-tact even when being passed around by my colleagues reviewing it? There’s no reason for it to try to update when they’re reviewing it because the data from the external references is completely irrelevant to their review, and as I said before that whole sheet is hidden from them anyway. Please help! 🙂

    • Excel Off The Grid says:

      Hi Kevin – what’s odd is that reference do not re-link back when both workbooks are open. They should do.

      You could try opening the linking sheet, selecting a cell with a link and pressing Ctrl + [ to see if the correct linked file opens up. Maybe there is something odd with the file paths.

Leave a Reply

Your email address will not be published.