Excel embeds the last value it pulled from every external workbook link directly into the saved file. When the source workbook no longer exists, recalculation has nothing to read, so Excel silently falls back to that stored value instead of showing an error. The fix is to refresh or remove the stale link, and the prevention is to uncheck Save external link values in the workbook's advanced options.
Why the stale value survives every recalculation
A formula such as the one below is normally resolved against the source file:
='C:\Data\[Nov.xlsx]Sheet1'!A1To keep workbooks usable while their sources are closed, Excel writes the retrieved numbers into the file when you save. On the next open, if Nov.xlsx cannot be found, Excel has no way to fetch a fresh value, so it keeps displaying the cached one. Pressing F9, or forcing a full recalculation with Ctrl+Alt+F9, does not change this: recalculation re-reads sources that are available, but it cannot conjure values for files that are missing.
Find and clear the stale link
Open the Data tab and click Workbook Links. A side panel lists every external workbook the file references, with Refresh and remove controls. Refresh tells Excel to re-read the source; if the file is truly gone, use the remove control to drop the link. In older builds the same management lives in the classic Edit Links dialog on the Data tab, which only appears when the workbook actually contains external references. Removing a link converts the affected formulas to their current values, so the last good numbers stay in place as static data.
Stop Excel from caching external values
If you would rather see a visible error than silently stale data:
- Open File > Options > Advanced.
- Scroll to the When calculating this workbook section.
- Uncheck Save external link values.
- Save, close, and reopen the workbook.
From then on, Excel will not embed retrieved values for that workbook. If a source disappears, the affected cells show an error or zero rather than the last known numbers, which makes broken links immediately obvious instead of quietly misleading.
If the source workbook is gone for good
When the data moved rather than disappeared — say the monthly file became Dec.xlsx — point the formulas at the new file and the link refreshes normally. If the file was merely relocated, correcting the path in the link settings restores the connection without rebuilding anything. If the numbers are final and you only need the values, break the link from the panel described above to freeze them.
Why a VBA workaround does not help
A user-defined function that calls Worksheet.Evaluate on an external reference goes through the same calculation engine, and Evaluate cannot resolve references to closed workbooks — so it returns an error or the same cached value, never fresh data. Reading a genuinely closed workbook from VBA requires opening it with Workbooks.Open or querying it externally.
Two further gotchas appear in this kind of code. First, a check such as FileFound = (Len(Dir$(...)) > 0) is pointless if the next line unconditionally sets FileFound = True; the "file missing" branch can never run. Second, a reference to an empty cell returns 0, not an empty string, because Excel converts blanks to zero in direct references. Wrap the reference if you need blanks: =IF(A1="","",A1).
Preventing the problem
- Keep linked source files in a stable path and avoid deleting them while reports still point at them.
- Review the Workbook Links panel periodically so dead links surface early.
- Uncheck Save external link values on reporting workbooks that must fail loudly when a source is missing.
- Consider Power Query or copied values for one-way data, since both make the state of the source more explicit.
Frequently Asked Questions
Why doesn't F9 update my external links?
Recalculation re-reads only sources Excel can reach. When the source workbook is missing, Excel keeps the value it cached at the last save, because there is nothing for it to fetch.
Where do I find Edit Links?
On the Data tab. Current builds show a Workbook Links side panel; legacy builds show an Edit Links dialog in the Connections group. Either only appears when the workbook contains external references.
What exactly does "Save external link values" do?
It tells Excel to store the values retrieved from closed workbooks inside the file itself. Unchecking it stops that storage, so unavailable sources surface as errors or zeros instead of stale numbers.
Will removing a link delete my data?
No. Breaking or removing a link converts the linked formulas to their current values. The numbers remain as static entries; Excel simply stops tracking the source.
Why do my linked cells show 0 instead of blank?
Direct references to empty cells evaluate to zero by design. Test the reference and return an empty string when it is blank, for example =IF('Book'!A1="","",'Book'!A1).
Can VBA read a cell from a closed workbook?
Not with Evaluate, which shares the normal engine's limits. Open the workbook with Workbooks.Open, read the cell, then close it, or pull the data in through a query instead.