How to stop #ref! errors when moving excel files by converting formulas to values first
- Step 1Open the file while it can still reach its sources — Do this on the machine and in the folder where the linked source files still exist. If links are already broken (
#REF!), the cached value is the error itself — restore the sources or repoint links via Data → Edit Links first. - Step 2Recalculate and save — Press F9 so every external-reference formula pulls a fresh value from its source, then save. The tool freezes the cached result, so this refresh is what guarantees the static values are correct rather than stale.
- Step 3Upload the recalculated file — Drop the
.xlsxonto the tool. ExcelJS loads it locally — nothing is transmitted. This is a Pro tier tool (Pro allows 50 MB / 100,000 rows per file). - Step 4Flatten every formula — The tool replaces each formula cell — local and external-reference alike — with its cached value across all sheets. No options to set; it is all-cell, all-sheet.
- Step 5Confirm conversion — Check the
change(s)count in the result panel. It should at least cover the external-linking formulas you wanted gone. A low count can mean caches were stale — recalc and re-run. - Step 6Move the self-contained file freely — Download
…-values-only.xlsxand move, email, or sync it. With no live links left, it opens without #REF! or 'Cannot find file' anywhere. Click a former-link cell to confirm the formula bar shows a value, not=[...].
Why external references break — and what flattening does
External links resolve against a path. Move the file or the source and the path no longer resolves. Flattening removes the dependency entirely.
| Trigger | What happens to the link | After flattening to values |
|---|---|---|
| Move file to a new folder | Relative/absolute path no longer resolves → #REF! | No link — cell holds the cached value |
| Email file as attachment | Recipient lacks the source file → 'Cannot find file' | Self-contained — opens cleanly |
| Rename or delete the source workbook | Link target gone → #REF! | Value already frozen in; source no longer needed |
| Upload to a cloud drive (different path) | Path differs from local → broken link prompt | No external dependency to resolve |
| Source moved but file stayed | Link points at old location → #REF! | Convert source-side or flatten before the move |
Flatten vs. native Excel alternatives
How one-pass flattening compares to Excel's built-in ways of dealing with external links.
| Approach | Scope | Self-contained result? |
|---|---|---|
| This tool (flatten all formulas to values) | Every formula on every sheet | Yes — no links remain anywhere |
| Excel: Data → Edit Links → Break Link | Per external link source | Yes for that source, but you must repeat per source |
| Excel: Paste Special → Values | Per selection, per sheet | Yes for selected cells only — easy to miss tabs |
| Excel: keep links + ship source files | Whole dependency graph | No — recipient must place files at matching paths |
Cookbook
Before/after for the external-reference formulas that turn into #REF! after a move. Flattening removes the link, leaving the cached number.
Cross-workbook link flattened before email
The classic case: a summary cell pulling from another workbook. After flattening, the cell is just a number with no link for the recipient to resolve.
Before (recalculated while Budget.xlsx is reachable): ='[Budget.xlsx]Summary'!B2 Shows: 128,400 After flatten: 128400 (no [Budget.xlsx] link — opens cleanly in any inbox)
Absolute-path link removed before folder move
Absolute paths break the instant a file leaves its home folder. Flattening removes the path dependency outright.
Before: ='C:\Finance\Q3\[Costs.xlsx]Data'!A1 Shows: 4,210 After flatten (moved to \\server\share\): 4210 (no path to resolve → no #REF! at the new location)
Flatten AFTER recalc, not on an already-broken file
If you flatten a file whose links are already #REF!, you freeze the error. Recalculate while sources are reachable first.
WRONG order: Move file → links break → #REF! cached → flatten Result: cell shows hard-coded #REF! RIGHT order: Open at home → F9 (links resolve) → save → flatten → move Result: cell shows the correct cached number
Mix of local and external formulas
Flattening is indiscriminate — local SUMs and external links are both frozen, which is exactly what you want for a portable file.
Before: B10: =SUM(B2:B9) -> local C10: ='[Rates.xlsx]FX'!D4 -> external After flatten: B10: 48200 C10: 1.0925 (both static; file is fully portable)
Confirming no links remain
After conversion, Excel's Data → Edit Links should report no external links — the proof the file is self-contained.
After downloading -values-only.xlsx and opening it: Data tab → Edit Links is greyed out / 'This workbook contains no links' change(s) count in the tool reported: 1,204 => all 1,204 formula cells (incl. external links) flattened
Edge cases and what actually happens
Links already broken before flattening
Error frozenIf the file's external links already show #REF! because the source moved, the cached value IS the error — flattening freezes the #REF! into the cell as static text. Restore or repoint the sources (Data → Edit Links) and recalculate so a real value is cached, then flatten.
File not recalculated while sources were reachable
Stale value riskThe tool freezes cached results, not live ones. If you didn't press F9 while the source files were accessible, an external-reference cell may hold an outdated value. Open at the source location, F9, save, then flatten.
Power Query / data connections to external files
Out of scopePower Query connections, ODBC links, and workbook data connections are not cell formulas and are not touched by this tool. They live in the workbook's connection parts. Manage them in Excel via Data → Queries & Connections; flattening only removes external links expressed as cell formulas.
Linked named ranges / 3-D references
ConvertedFormulas using externally-linked named ranges or 3-D references across workbooks are still formula cells, so they are flattened to their cached values. The defined names may remain in the file but no longer pull from anywhere.
Hyperlinks vs. formula links
Not affectedA clickable hyperlink to another file is not a formula and is not what causes #REF! — it is metadata on the cell, untouched by this tool. Only formula-based external references (=[file]Sheet!cell) are flattened. A broken hyperlink is a separate issue.
Empty cache on an external formula
Converts to blankIf an external-reference formula was never calculated (e.g. Excel prompted to update links and you declined, leaving no cache), fv.result is undefined and the cell becomes blank after flattening. Update links and recalculate before converting.
.xls legacy file
Invalid formatOnly .xlsx/.xlsm are supported. Re-save a legacy .xls as .xlsx in Excel before uploading.
File over the tier limit
RejectedPro 50 MB / 100,000 rows, Pro-media 200 MB / 500,000, Developer 500 MB / unlimited. A file above your tier is rejected before processing. (This is a Pro tool, so Free cannot run it.)
Charts / pivots fed by external links
Values retainedA chart or pivot reading from former external-link cells now reads the frozen values, so it still displays. ExcelJS round-trips the workbook rather than rebuilding these objects — confirm them in Excel after conversion for complex files.
Frequently asked questions
How does converting to values stop #REF! errors?
#REF! from a moved file comes from a formula that can no longer resolve its external reference. Replace that formula with its cached value and there is no reference left to resolve — the cell holds a static number that travels with the file. No link, no #REF!.
Do I need the source files present when I flatten?
You need them present when you recalculate (F9) so a correct value is cached. You can flatten afterwards even if you then move the sources, because the tool reads the cached value, not the live link. The recommended order is: open at source location → F9 → save → flatten.
What if my links are already showing #REF! before I flatten?
Then the cached value is the error, and flattening will freeze a literal #REF! into the cell. Restore or repoint the sources via Data → Edit Links and recalculate first, so a real value is cached before you convert.
Does this fix broken Power Query connections too?
No. Power Query queries are stored separately from cell formulas and are not touched by this tool. Fix Power Query source paths in Excel via Data → Queries & Connections; flattening handles only external references written as cell formulas.
Can I convert only the external-reference formulas and keep the local ones?
No — the tool flattens every formula on every sheet. For a portable, self-contained file that is usually what you want anyway. If you need to keep local formulas live, do a targeted Paste Special → Values on just the external-link cells in Excel instead.
Will the file open without the 'update links' prompt afterwards?
Yes. Once formulas are flattened there are no external links, so Excel won't prompt to update links and Data → Edit Links will report none. That's the confirmation the file is self-contained.
Are hyperlinks to other files removed?
No. Clickable hyperlinks are cell metadata, not formulas, and aren't what causes #REF!. This tool only flattens formula-based external references. Manage hyperlinks separately in Excel.
Is the original linked workbook preserved?
Yes. The tool writes a new -values-only.xlsx and never modifies your input. Keep the original if you might want to re-establish live links later.
Does my file get uploaded?
No. ExcelJS processes the workbook entirely in your browser. Neither the file nor any cached data from linked sources is transmitted to a server.
What size files can I process?
Pro 50 MB / 100,000 rows, Pro-media 200 MB / 500,000, Developer 500 MB / unlimited. Free's 5 MB / 10,000-row tier cannot run this Pro tool. Oversized files are rejected before processing.
How can I verify there are no links left?
Open the output and check Data → Edit Links — it should report no external links. Cross-reference with the tool's change(s) count, which equals the number of formula cells (local + external) that were flattened.
What about formulas that reference SharePoint/OneDrive-hosted files?
If the link is a stable cloud URL, Excel Online may still resolve it — but for portability across machines and folders, flattening to values removes the dependency entirely so it never breaks. See also the SharePoint-upload guide for cloud-specific advice.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.