How to locate every #ref! error caused by column or row deletion in excel
- Step 1Save the workbook after the deletion — If the
#REF!errors are already in your saved file, you're ready. The tool reads the saved state, so save in Excel before uploading if you've just made the deletion. - Step 2Open the Error Locator — Go to the Error Locator. There are no options to set — the scan always checks every cell on every sheet for all error types, including
#REF!. - Step 3Drop the broken `.xlsx` — Drag in or select the workbook. One file per run; the picker accepts
.xlsx,.xls, and.ods. SheetJS parses it locally. - Step 4Read the count and red cell addresses — The panel shows the total error count and the first 10 errors as
[sheet] cell: errorType, cell address in red. A spike in#REF!confirms the deletion's reach. - Step 5Download the full report with formulas — Copy or Download to get every error line as
[sheet] cell: errorType (=formula). The formula text reveals which reference was severed. - Step 6Repoint references in Excel, then re-scan — Fix each
#REF!by re-pointing the formula at the correct range (or restoring the column). Save and re-upload to confirm the report reads zero errors.
Errors a column/row deletion typically creates
A single deletion rarely produces only #REF!. These are the error types that commonly appear together in a post-deletion scan and how the locator handles each.
| Error | Why deletion causes it | Appears in report | Where to fix |
|---|---|---|---|
#REF! | Formula referenced the deleted column/row directly | Yes, with #REF! token in the formula | Re-point the formula in Excel |
#REF! (cascade) | Cell referenced another cell that became #REF! | Yes | Fix the upstream #REF! first; cascade clears |
#N/A | Lookup column index shifted or the lookup range shrank | Yes | Adjust column index or range in VLOOKUP/INDEX |
#VALUE! | Math now references a blank/shifted text cell | Yes | Re-point or coerce the argument |
#NAME? | A named range pointed at the deleted area | Yes | Redefine the name in Name Manager |
Reading a #REF! report line
Each line of the downloadable report breaks down like this. The formula often retains a literal #REF! token where the deleted reference used to be.
| Part | Example | What it tells you |
|---|---|---|
| Sheet | [Summary] | Which tab holds the broken cell |
| Cell | E14 | The exact address to open in Excel |
| Error type | #REF! | Confirms it's a reference break, not a lookup miss |
| Formula | (=Inputs!#REF!+10) | Shows the dead reference inside the formula |
Tier limits
Error Locator is free-tier. Limits apply to the uploaded file (Excel family), not to how many #REF! errors are found.
| Tier | Max file size | Max rows | Files per run |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 |
| Pro | 50 MB | 100,000 | 5 |
| Pro + Media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Post-deletion error reports in the exact format the tool produces. The #REF! token inside formulas is the fingerprint of a severed reference.
Direct #REF! from a deleted column
A formula multiplied a value from column C; column C was deleted, so the reference became a #REF! token embedded in the formula.
Report: Found 1 error cell(s) across 1 sheet(s): [Sales] D2: #REF! (=#REF!*1.08)
The cascade across sheets
The deletion broke one input cell; every downstream cell that referenced it now also stores #REF!. Fixing the source clears the chain.
Report: Found 3 error cell(s) across 2 sheet(s): [Inputs] B5: #REF! (=Raw!#REF!) [Summary] C10: #REF! (=Inputs!B5*12) [Summary] C11: #REF! (=Inputs!B5/4)
Mixed fallout: #REF! plus a #N/A index shift
Deleting a column inside a lookup table shifted the column index, turning a working VLOOKUP into #N/A while another formula went #REF!.
Report: Found 2 error cell(s) across 1 sheet(s): [Report] F3: #REF! (=#REF!&" units") [Report] G3: #N/A (=VLOOKUP(A3,Table,4,0))
A named range pointing at deleted cells
When a defined name referenced the deleted area, formulas using that name return #NAME? rather than #REF!.
Report: Found 1 error cell(s) across 1 sheet(s): [Calc] H8: #NAME? (=SUM(TaxRates))
Confirm the fix cleared everything
After re-pointing references in Excel and saving, re-scan. A clean workbook returns the no-errors message.
Report (after fix + save): No formula errors found in this workbook.
Edge cases and what actually happens
Ctrl+Z already undid the deletion
By designIf you undid the deletion and the formulas recalculated to valid values before saving, there are no stored error cells to find. The locator only reports what the saved file holds — undo first, save, and you'll get a clean report.
Deletion broke a chart or pivot but not a cell
Not detectedCharts and pivot caches aren't cells, so a broken chart series isn't a cell error type. The locator finds cell errors only. Repair charts and pivots in Excel directly.
Workbook saved with calculation set to manual
May miss errorsSheetJS reads cached results. If calculation was manual and the sheet wasn't recalculated after the deletion, some cells may still hold stale valid values. Press F9 in Excel, save, then re-scan to catch them all.
More than 10 #REF! errors
By designBig cascades exceed the 10-row on-screen preview. The total count is accurate, and the downloadable report lists every #REF! with its formula. Use the full report for cascade cleanup.
Cell shows a literal '#REF!' text from a paste
Not detectedIf a paste-as-values operation turned a #REF! into the text string #REF!, the cell type is text, not error, so it's skipped. Use Find & Replace in Excel to locate literal #REF! text.
File over the free 5 MB / 10,000-row limit
Blocked on freeLarge models exceed the free Excel limits (5 MB, 10,000 rows, 1 file). Upgrade to Pro (50 MB / 100,000 rows) or higher to scan them.
Hidden sheet holds the broken reference
DetectedThe scan covers every sheet including hidden ones, so a #REF! on a hidden helper tab is reported with that sheet's name — exactly the cell Go To Special would never reach.
Two files dropped together
One file onlyThe Error Locator scans a single workbook per run. Handle the broken file, fix it, then scan the next one separately.
Encrypted/password-protected workbook
Cannot parseSheetJS can't open an encrypted file without the password, so the scan can't run. Remove protection in Excel first and re-upload.
Frequently asked questions
Why did deleting one column create dozens of errors?
Excel formulas form chains. The cell that referenced the deleted column becomes #REF!, and every cell that referenced that cell becomes #REF! too. The locator reports all of them so you can see the full cascade and fix the source first.
Does the report show which reference was deleted?
It shows the broken formula, which typically still contains a literal #REF! token where the deleted reference used to be — for example =#REF!*1.08. That token marks exactly which argument died.
Will it fix the #REF! errors for me?
No. It finds and lists them with addresses and formulas; you re-point or rewrite the references in Excel. To trace what should feed a cell, the dependency map helps.
Can I recover the deleted data?
Not with this tool — it doesn't restore data. Use Excel's undo or a prior file version. The locator's job is to show you every cell the deletion broke so nothing slips through.
Why does my scan show zero errors when I see #REF! in Excel?
Save the file first. The locator reads the saved workbook; if the deletion happened after your last save, or calculation is set to manual, recalc (F9) and save, then re-scan.
Does it scan hidden sheets?
Yes — every sheet is scanned, including hidden and very-hidden tabs, so a broken reference on a helper sheet is still reported.
What's the difference between #REF! and #NAME? after a deletion?
#REF! means a direct cell/range reference was deleted. #NAME? usually means a named range that pointed at the deleted area is now undefined. The report shows both, so you fix references and named ranges separately.
Does it find errors only in formulas?
It finds any cell whose stored type is an error value. That's almost always a formula result, since hand-typed text that looks like an error stays text and is skipped.
How many errors can it report?
All of them in the downloadable report — there's no cap. Only the on-screen preview is limited to the first 10 for quick reading.
What file types are supported?
Excel workbooks. The registry declares .xlsx; the picker also allows .xls and .ods. CSV isn't supported because it has no formulas or stored errors.
Is anything uploaded to a server?
No. SheetJS parses the workbook in your browser; the file never leaves your machine.
What should I run after fixing the references?
Re-scan to confirm zero errors, then use the formula explainer to sanity-check repaired formulas and the format inspector to catch any numbers-as-text the shuffle introduced.
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.