How to convert excel formulas to values before uploading to sharepoint
- Step 1Open the file where its sources still resolve — Do this on the machine/folder where any linked source workbooks exist. If links are already broken locally, fix them via Data → Edit Links first — otherwise you'll flatten an error into the cloud copy.
- Step 2Recalculate and save — Press F9 so external references pull fresh values, then save. The tool freezes the cached result, so this refresh is what makes the cloud copy's numbers correct.
- Step 3Upload the recalculated file to the tool — 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 — including the external-link ones — with its cached value across all sheets. No options to configure.
- Step 5Confirm conversion — Check the
change(s)count. It should cover the linking formulas you wanted removed; a low count signals stale caches — recalc and re-run. - Step 6Upload the static file to SharePoint / OneDrive — Put
…-values-only.xlsxin the document library. With no live links, recipients see correct values and no external-reference errors. Optionally confirm in Excel Online that Data → Edit Links reports none.
Why cloud uploads break links — and the fix
SharePoint/OneDrive store the file at a new location with a new addressing scheme; local-path links no longer resolve. Flattening removes the dependency.
| Cloud scenario | What recipients see | After flattening |
|---|---|---|
| Workbook links to a local C:\ file | 'Cannot find file' / #REF! | Cached value frozen in — no link to resolve |
| Linked source not also uploaded | Broken link prompt on open | Self-contained — opens cleanly |
| Co-author edits in Excel Online | Link update prompts / errors | No links to update |
| File downloaded again to a different PC | Path differs → #REF! | Portable — value travels with the file |
| Source IS on SharePoint with stable URL | May resolve in Excel Online | Flattening still safest for portability |
Cloud-sharing prep checklist
Flattening fixes link errors; other cleanup tools handle the rest of a safe cloud share.
| Concern before upload | Handled by this tool? | Otherwise use |
|---|---|---|
| External-link #REF! errors | Yes — formulas flattened to values | — |
| Audit which links exist first | No — flatten is blind to link inventory | External Link Auditor |
| Sensitive author/company metadata | No | App Metadata Wiper |
| Internal review comments | No | Comment Purger |
| Hidden tabs you don't want shared | Values flattened, sheet remains | Hidden Sheet Destroyer |
| Macros in an .xlsm | No — VBA remains | VBA Macro Stripper |
Cookbook
Before/after for the formulas that turn into cloud #REF! errors. Flattening before upload makes the SharePoint copy self-contained.
Local-path link flattened before upload
The link works on your PC but not in SharePoint. Flattening removes it so the cloud copy is clean.
Before (recalculated while Forecast.xlsx is reachable): ='[Forecast.xlsx]Q3'!B2 Shows: 92,300 After flatten -> upload to SharePoint: 92300 Recipients see the number, no 'Cannot find file' prompt.
Flatten before, not after, the cloud move
Upload the linked file and you've already broken it for recipients. Flatten first, while the link still resolves locally.
WRONG: Upload linked file to OneDrive -> recipients get #REF! RIGHT: Open locally -> F9 -> save -> flatten -> upload -values-only.xlsx Recipients open a self-contained file.
Already-broken link freezes the error
If the file already shows #REF! before flattening, that error becomes static and travels to the cloud. Repair links first.
File already showing #REF! (source moved): ='[Old.xlsx]Data'!A1 -> cached value: #REF! If flattened now -> cell holds literal #REF! in SharePoint. Fix: Data -> Edit Links -> repoint/restore -> F9 -> then flatten.
Co-authoring-safe static workbook
Files meant for Excel-Online co-authoring shouldn't carry live external links that prompt every collaborator. Flattening removes them.
Before: report links to 3 local source workbooks. In Excel Online co-authoring: each opener gets update-link prompts. After flatten: No links -> no prompts -> clean co-authoring on the values.
Verifying the SharePoint copy is link-free
Confirm in Excel Online (or desktop) that no links remain before relying on the shared file.
Open -values-only.xlsx from SharePoint: Data -> Queries & Connections / Edit Links -> 'no links' Tool reported: 2,015 change(s) => all formula cells (incl. external links) flattened before upload
Edge cases and what actually happens
Linked file already broken before upload
Error frozenIf links show #REF! locally before you flatten, the cached value is the error and flattening freezes a literal #REF! that then sits in the SharePoint copy. Repair or repoint links (Data → Edit Links) and recalculate so a real value is cached before flattening.
Source workbook also lives on SharePoint
May resolveIf the linked source is itself on SharePoint with a stable URL, Excel Online may resolve the reference even after upload. But flattening is still the most portable option — it removes the dependency entirely so the file behaves the same whether opened in the cloud, downloaded, or moved.
SharePoint does not repair links for you
By design (cloud)SharePoint/OneDrive store files as-is; they do not rewrite or repair external references that break on upload. The fix has to happen in the file before it goes up — which is exactly what flattening does.
Power Query / cloud data connections
Out of scopePower Query queries and data connections aren't cell formulas and aren't touched by this tool. They may behave differently in Excel Online. Manage them via Data → Queries & Connections; flattening only removes external links written as cell formulas.
Not recalculated before flattening
Stale value riskThe tool freezes cached results. If you didn't F9 while sources were reachable, the cloud copy may carry outdated numbers. Open locally, recalc, save, then flatten and upload.
Empty-cache external formula
Converts to blankAn external formula with no cached result (e.g. you declined 'update links' so nothing was computed) becomes a blank cell after flattening. Update links and recalculate first so a value is cached.
Macro-enabled file uploaded to SharePoint
Macros remainFlattening an .xlsm doesn't strip its VBA, and SharePoint may block or warn on macro-enabled files. Run the output through the VBA Macro Stripper and consider saving as plain .xlsx for cloud distribution.
File over the tier limit
RejectedPro 50 MB / 100,000 rows, Pro-media 200 MB / 500,000, Developer 500 MB / unlimited. A workbook above your tier is rejected before processing. (This is a Pro tool, so Free can't run it.)
Charts / pivots referencing former links
Values retainedCharts and pivots reading former external-link cells now read the frozen values, so they still display. ExcelJS round-trips these objects rather than rebuilding them — verify them in Excel Online after upload for complex files.
Frequently asked questions
Why do my Excel formulas break after uploading to SharePoint?
Formulas that link to a file on your local drive can't resolve in the cloud — the path doesn't exist there — so they flip to #REF! or 'Cannot find file' for anyone who opens the file from SharePoint or OneDrive. Flattening those formulas to values before upload removes the dependency entirely, so there's nothing left to break.
Does SharePoint fix broken external references automatically?
No. SharePoint and OneDrive store files as-is and do not repair external links that break on upload. The fix must happen in the file before it goes up — flattening formulas to values is that fix.
Do I need the source files present when I flatten?
You need them present when you recalculate (F9) so a correct value is cached. After that you can flatten and upload even though the cloud copy can't reach the sources, because the tool reads the cached value, not the live link. Order: open locally → F9 → save → flatten → upload.
What if my links already show #REF! before I flatten?
Then the cached value is the error and flattening freezes a literal #REF! into the cloud copy. Repair or repoint the sources via Data → Edit Links and recalculate first, so a real value is cached before you convert.
What about workbooks that reference SharePoint-hosted files?
If the referenced file is also on SharePoint with a stable URL, Excel Online may resolve the reference. But for guaranteed portability — across download, move, and re-share — flattening to values is the safest choice, since it removes the dependency entirely.
Can I keep conditional formatting and data validation after flattening?
The tool only changes formula cell values; it doesn't edit conditional-formatting rules or data-validation lists, which ExcelJS writes back on the round-trip. Rules that referenced formula cells now evaluate against the static values. Spot-check them in the output if your file relies on them heavily.
Will co-authors in Excel Online still get 'update links' prompts?
Not after flattening — once formulas are values there are no external links to update, so collaborators won't be prompted and won't hit #REF! from missing sources.
Is my file uploaded to your servers on the way to SharePoint?
No. ExcelJS processes the workbook entirely in your browser. You upload the cleaned -values-only.xlsx to SharePoint yourself; the tool never transmits your file.
Should I also remove macros before uploading?
If it's an .xlsm, yes — SharePoint may block or warn on macro-enabled files, and flattening doesn't remove VBA. Run the output through the VBA Macro Stripper and consider saving as .xlsx for cloud distribution.
How do I confirm the SharePoint copy has no links?
Open it (Excel Online or desktop) and check Data → Edit Links / Queries & Connections — it should report no external links. Cross-reference the tool's change(s) count, which equals the formula cells (including external links) flattened.
Is my original linked workbook preserved?
Yes. The tool writes a new -values-only.xlsx and never modifies your input, so you keep the linked master for internal use and upload only the static copy.
How large a file 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 can't run this Pro tool. Files over your limit are rejected before processing.
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.