You converted a workbook from .xls to .xlsx and now it's full of #REF! errors where formulas used to work. The data is there, the sheets look the same, but something about the conversion broke the references. Before you start manually rebuilding formulas, it's worth understanding why — because the fix is usually much faster than starting over.
#REF! means "this formula is pointing to something that doesn't exist." The conversion process itself doesn't change formula references — but it can change the context those references live in, which causes the error. Here are the five most common causes.
The .xls format allowed sheet names up to 31 characters. The .xlsx format has the same limit, so this usually isn't the issue — unless your conversion tool renamed or sanitized sheets for some reason. More commonly, this happens when sheets are referenced by name in formulas and the sheet was deleted or renamed after conversion.
Check the formula bar for any formula showing #REF!. If you see something like ='[OldFile.xls]Sheet1'!A1 in the cell, the formula is trying to reach an external workbook that no longer exists at that path.
Use Ctrl+H (Find and Replace), set Find what to #REF!, leave Replace with empty, and click Find All. Excel lists every affected cell across the workbook. You can also use Ctrl+G → Special → Formulas → Errors to select all error cells on the current sheet.
This is the most common cause of post-conversion #REF! errors, especially in workbooks that pull data from other files. If your workbook contains formulas that reference another .xls file — like ='C:\Reports\[budget.xls]Summary'!B4 — that link is now broken if the source file was renamed to budget.xlsx or moved.
The .xlsx file can't automatically find the renamed source. From Excel's perspective, the original file doesn't exist anymore.
Go to Data → Edit Links. Any broken link shows a status of "Error: Source not found." Select the broken link, click Change Source, and navigate to the new .xlsx version of the file. Excel updates all formulas referencing that source in one step.
LegacyLeaps's free scan flags external links, volatile formulas, and features that need attention — before you convert and discover errors the hard way.
Run the Free ScanThe INDIRECT function builds a cell reference from a text string. If your workbook uses INDIRECT to reference sheet names, file names, or cell addresses that were hardcoded as text, those strings don't update automatically when you rename files or sheets.
For example:
=INDIRECT("'[budget.xls]Summary'!B" & ROW())After converting budget.xls to budget.xlsx, this formula still has the old filename hardcoded. It can't find the source and returns #REF!.
Use Find and Replace (Ctrl+H) to find the old filename or sheet name pattern within your formulas. Make sure to check the Look in: Formulas option. Replace budget.xls with budget.xlsx (or whatever the new name is) across the whole workbook at once.
This sounds counterintuitive — .xlsx supports more rows and columns than .xls, not fewer. But there's a scenario where this happens: if rows were inserted above a formula's reference range during a previous edit, and the workbook was saved with those changes, the formula's row reference may have shifted during the Save As conversion.
More practically: if your .xls file had formulas referencing columns beyond column IV (column 257+), those references are invalid because .xls only supports 256 columns. The .xls would show #REF! there too — but if the workbook was in compatibility mode and those sheets were hidden, you might not have noticed until conversion.
Look at the failing formula in the formula bar. If the reference includes a column letter beyond IV (like IW4 or AAB12), that was always going to be a problem in the .xls. Correct the column reference to a valid location.
Named ranges are invisible until they break. If your workbook defines named ranges (visible under Formulas → Name Manager) that point to sheets or ranges that were deleted or rearranged during conversion, formulas using those names will return #REF!.
Go to Formulas → Name Manager and look at the "Refers To" column. Any entry showing #REF! in the refers-to is broken. Either update the reference to the correct location, or delete the name if it's no longer needed. Deleting a named range doesn't delete the data — it only removes the name. Formulas that used the deleted name will now show #NAME? instead of #REF!, which is a different error to fix.
LegacyLeaps validates named range integrity during .xls to .xlsx conversion — so your formulas come out clean on the other side.
Download Free ScannerWhen you click on a cell showing #REF!, use the formula auditing tools to trace exactly what it was pointing to:
#REF! is sometimes embedded inside a longer formula. For example: =SUM(#REF!,B1:B10) means one part of the SUM range is broken, not both.If #REF! is appearing in many cells at once after conversion, the most likely culprits are a broken external link (Cause 2) or a broken named range (Cause 5) — because both can propagate across all the formulas that depend on them. Fix the source (the link or the named range), and the downstream formulas should resolve automatically without touching each one individually.
If the errors are scattered without a clear pattern, a formula audit with Formulas → Error Checking can step through each one and explain the specific reference failure.
For workbooks with hundreds of formulas and multiple broken references, the done-for-you service includes full formula audit and repair as part of the migration — one flat rate, everything working when we're done.
Practical fixes for legacy Excel and Access problems. No spam.