Excel #REF! After Deleting a Sheet: How to Recover

A workbook that calculated fine yesterday now shows #REF! across a summary tab. Nine times out of ten a worksheet was deleted recently — by you, by a colleague in the same file, or by a macro that ran on open. The detail that decides everything: Excel overwrites the reference itself, so this is not a calculation problem F9 will clear. What you get back depends on whether the file was saved after the deletion, and the first two steps below are about not making that worse.

Why the reference disappears for good

A formula does not store “the third sheet, cell B4”. It stores a token: =SUM(Sheet3!B4:B40). Delete Sheet3 and Excel rewrites the formula text in place to =SUM(#REF!). The damaged text is then written into the cell and saved to disk like any other edit.

That is why recalculating changes nothing, and why the sheet part cannot simply be reattached. On the next save Excel drops xl/worksheets/sheet3.xml from the package and removes its row from xl/workbook.xml, so the file that reaches disk no longer contains the deleted sheet at all.

Two less obvious paths produce the same error. A 3D reference such as =SUM(Jan:Dec!B2) breaks if any sheet in that span is deleted, even one you never referenced by name. And a defined name that pointed at the deleted sheet becomes =#REF! in Name Manager, which then poisons every cell that uses the name — those formulas look untouched when you click them.

Fix 1 — Undo before you do anything else

Press Ctrl+Z once. If the deletion was the last action in the workbook, the sheet returns with its data and every formula pointing at it is repaired at the same moment. If something else happened afterwards, keep pressing Ctrl+Z; the undo stack holds roughly the last 100 actions and the deletion is still in there.

Do not save and do not close the file: saving commits the deletion and closing clears the stack. If a colleague has the workbook open from a shared folder, tell them to stop first.

Fix 2 — Check that the sheet is only hidden

Right-click any visible tab and choose Unhide. Excel refuses to delete the last visible sheet, so a workbook that suddenly shows one tab has often just had the others hidden. If the Unhide list is empty or the command is greyed out, the sheet is either very hidden or the workbook structure is protected — both recoverable, neither involving data loss. I went through the four states in Excel sheet tabs missing; confirm this before assuming deletion, because a hidden sheet needs no recovery at all.

Fix 3 — Map every broken formula in one pass

Press Ctrl+H, set Find what to #REF!, set Look in to Formulas, then click Find All. The list at the bottom of the dialog is your work order: press Ctrl+A inside it to select all matches, close the dialog, and fill those cells with a colour. You now have a marked set that survives scrolling and sorting.

Resist the temptation to replace #REF! with an empty box. =SUM(#REF!) becomes =SUM(), which is valid and silently returns zero, and =#REF!+A1 becomes =+A1, which looks like a working formula. Both hide the damage instead of fixing it. Replace with a real reference instead, clicking the correct range while the cell is in edit mode.

Fix 4 — Clear the defined names that broke

Go to Formulas → Name Manager and sort by the Refers To column. Any name showing =#REF! will throw the error into cells whose formulas read perfectly. Delete the name if it is obsolete, or click Edit and point it at the worksheet that now holds the data. Check the same list for chart series and table references — a chart whose source range was on the deleted sheet keeps drawing a flat line at zero without any warning.

Fix 5 — Take the sheet from an older version

If the file lives in OneDrive or SharePoint, open File → Info → Version History and pick the version from before the deletion. Open it rather than restoring it, then right-click the surviving tab and use Move or Copy → To book: [your current file] → Create a copy. That brings the sheet back into the live workbook without discarding the work done since.

For a file on a local drive, right-click it in File Explorer and open Properties → Previous Versions to look for a File History or restore point copy. Excel’s own safety net is File → Info → Manage Workbook → Recover Unsaved Workbooks, with backups in %AppData%\Microsoft\Excel\. Picking the right version works the same way as restoring a file that was saved over.

Fix 6 — Look outside the workbook

The deleted tab often exists somewhere else: the email attachment the file arrived in, a copy a colleague downloaded, the accounting export it was pasted from, or the Power Query source behind it. Any of those returns the values in minutes, and pasting them into a fresh sheet lets Fix 3’s marked cells relink.

Unzipping the current .xlsx usually disappoints. If the workbook saved normally the worksheet part is gone, so there is nothing in xl/worksheets/ to extract. It is only worth opening the archive when Excel crashed mid-save: a stray sheetN.xml sometimes survives without an entry in xl/workbook.xml, and its values can be read in a text editor.

When it is not worth fixing

Formulas are rebuildable. Values are not. If the only copy of the numbers lived on the deleted sheet and no version history, backup, or outside copy exists, no repair tool will produce them — anything that claims otherwise is guessing. A summary tab rebuilt around empty inputs looks right and reports wrong, which is worse than a visible error.

The other stop sign is scale. When a few hundred formulas point at a sheet that held raw transactions, the job is data re-entry, not formula repair. I would rather say the sheet is unrecoverable than bill for a file you cannot trust.

What is worth paying for is the middle case: the sheet part still exists in a version history or a backup, but the workbook has grown to dozens of links, pivot caches, and charts that all need to line up again. That is the work the repair service takes on, usually on a copy, with the original left untouched.

FAQ

Can I get the sheet back after saving and closing? Only from a copy that predates the deletion. Closing clears the undo stack, so version history or a backup is the remaining route.

Why does #REF! appear in cells I never edited? A defined name, chart series, or 3D reference pointing at the deleted sheet. Fix 4 covers the first two.