Established onExcel 365 and 2016 and later: cached values, relative pathsExcel: links hide in names, formatting and validationGoogle Sheets, web: IMPORTRANGE, permission grant and caching
A formula referring to another workbook stores a path to that file. The path is the whole of the relationship, and everything in this entry follows from that.
The value you are looking at
When the other file is not open, the formula shows the value it last retrieved, cached in your workbook. It is a real number, correctly formatted, and nothing on the sheet records when it was obtained.
Opening the file produces a prompt asking whether to update the links. That prompt appears once, at the moment somebody is trying to get to the data, and it is dismissed by habit. A workbook whose links have not been updated for a year looks exactly like one updated this morning.
What breaks the path
Moving either file. Renaming either file. Renaming a folder anywhere above them. Sending the workbook to somebody whose drives are lettered differently. And sharing a file that points at a location only you can reach.
That last one is the common case in practice: a link to a path beginning with a personal folder works for its author and for nobody else, and the error the recipient sees names the author's directory structure.
Where links hide
The links dialogue lists workbook references and it does not find everything. Links also live in defined names, in conditional formatting rules, in data validation lists, in chart series and inside objects.
Which is why a file reports that it contains links after somebody has removed every formula they can find. The remaining one is usually a name, and the name list is the first place to look.
The version in the other product
Google Sheets pulls from another spreadsheet through a function, which requires a one-time permission grant from the source document and then caches the result for a period before refreshing.
The failure modes differ: instead of a stale value it produces a transient error while refreshing, and instead of a broken path it produces a permission failure when the source's sharing changes. Both are visible, which is better, and both appear intermittently, which is worse for diagnosis.
Why one file is usually the answer
Nearly every reason for splitting a model across files is a reason that has stopped applying: file size limits, calculation speed, and separate people owning separate parts. The first two are rarely binding now, and the third is better solved by the arrangements in the previous entries.
If files must be linked
Keep them in one folder and never move them individually. Excel stores a relative path where the files share a folder, which survives the pair being moved together and breaks the moment they are separated.
Put a cell in the dependent workbook showing when the links were last updated. It is one formula and it converts an invisible staleness into a date somebody can read.
The better arrangement
Import rather than link. A stored query pointing at the other workbook brings the data in as a table, with declared types and a refresh that is a deliberate act with a visible result.
The difference is that a link is a hidden dependency and an import is a documented step, which is the argument made throughout the third part of this site.
Auditing an inherited workbook
Open the links dialogue and list what it points at. Then check the name manager, the conditional formatting rules and the validation lists for references to other files, because the dialogue will not have shown them.
Every path found is a question: does that file still exist, who owns it, and does anybody know this workbook depends on it.
What we cannot verify
The caching of external values, the update prompt, relative path storage and the categories of hidden link are documented by the vendor and were confirmed on a current build. The Google Sheets caching interval is stated by the vendor and has changed over time; it should be checked rather than remembered.
What to do when a link cannot be resolved
Excel offers to break the links, which replaces every external formula with its last cached value. That is irreversible and is frequently the right answer, because a broken link produces a stale number and a broken formula produces an error somebody will investigate.
Before doing it, record what the links pointed at on the documentation sheet, because after breaking them the file contains no evidence that it ever depended on anything.
The dependency nobody documented
The most valuable output of a link audit is the list of files your workbook needs. That list is what somebody would need in order to keep it running after you leave, and in most organisations it exists nowhere.
In short
- A link stores a path, and the value shown is cached from a moment nothing records.
- The update prompt appears once and is dismissed by habit.
- A link to a personal folder works for its author and names their directory to everybody else.
- Links also hide in names, conditional formatting, validation lists and charts.
- The other product fails visibly and intermittently instead, which is better and harder to diagnose.
- Import rather than link: a link is a hidden dependency and an import is a documented step.