Named ranges are one of Excel's most useful features — and one of the most anxiety-inducing things to think about when migrating a complex .xls file. If your workbook has dozens of named ranges wired into formulas, VBA macros, and data validation rules, you want to know exactly what happens to them before you convert.
The short answer: named ranges survive XLS to XLSX conversion cleanly. But there are edge cases worth knowing about — particularly around dynamic ranges, sheet-scoped names, and external references. For a broader look at all the differences between the two formats, see what changed between XLS and XLSX.
A static named range is a name pointing to a fixed cell reference — =Sheet1!$A$1:$B$100, for example. These transfer from .xls to .xlsx with names, references, and scope completely intact. Nothing changes. Formulas that reference them by name continue to work exactly as before.
Excel allows names to be scoped to the entire workbook or to a specific sheet. Both scoping models transfer correctly. A sheet-scoped name defined in Sheet2 stays scoped to Sheet2 after conversion.
Formula references to named ranges — =SUM(AnnualSales), =VLOOKUP(A1, LookupTable, 2, FALSE) — all carry over without modification. The formulas see the same names pointing to the same cells.
VBA code that references named ranges by name string — Range("SalesData"), Names("TaxRate").RefersToRange — continues to work after conversion. VBA refers to names by string, not by cell address, so as long as the name exists in the converted file (it does), the code finds it.
One caveat: if the .xls file has macros, save as .xlsm (not .xlsx) to preserve the VBA. If you save as plain .xlsx, the macros are stripped and your VBA code is gone regardless of named range preservation.
Rule of thumb: If your .xls has VBA macros, convert to .xlsm. If it has no macros, .xlsx is fine. LegacyLeaps detects VBA automatically and chooses the right target format.
Dynamic named ranges use formulas like =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1) to define a range that grows or shrinks with data. These transfer to .xlsx, but they behave differently in some versions of Excel when the file is first opened after conversion.
The name definition itself converts correctly. The issue is that .xlsx supports dynamic array functions (FILTER, UNIQUE, SEQUENCE) which can interact with OFFSET-based dynamic ranges in unexpected ways in newer Excel builds. If you have complex dynamic named ranges, test them after conversion — don't assume they work just because the Name Manager shows them.
In .xls, a workbook-scope name and a sheet-scope name can have the same string — the sheet-scope name shadows the workbook name when referenced from that sheet. This works in .xls but can cause Name Manager warnings in .xlsx. After conversion, check the Name Manager for any names highlighted in yellow or showing scope conflicts.
If a named range in your file references an external .xls file — =[OtherFile.xls]Sheet1!$A$1 — that external reference is preserved in the converted .xlsx, but it still points to the .xls file. If the external file later gets converted or renamed, the reference breaks. Audit external references in named ranges if cross-file dependencies are part of your workflow.
This is rare, but some very old .xls files use legacy function names (like CONCATENATE or XLM macro functions) inside named range formulas. These should still evaluate in modern Excel, but they may show as deprecated in the Name Manager. Not a blocker, but worth noting.
LegacyLeaps scans your .xls files before conversion — named ranges, VBA modules, ActiveX controls, external links — so you know exactly what you're working with before you convert.
Try the Free ScanFollow these steps to confirm named ranges transferred correctly:
#REF! in the "Refers To" column indicates a broken reference. This is unusual in a clean conversion but can happen with external references.Drop-down lists built with Data Validation → List → Source pointing to a named range transfer correctly. The named range reference in the validation rule is preserved. After conversion, verify your drop-downs still show the expected options — particularly if the named range was dynamic.
Excel Tables (created with Insert → Table) use structured references like =Table1[Column1] rather than traditional named ranges. These are a .xlsx feature that didn't exist in .xls — so a genuine Excel Table in your .xls file is actually stored as a ListObject and converts to a proper Table in .xlsx. The table name is preserved and structured references continue to work.
If your .xls file uses a named range to simulate a table (a common workaround from the pre-Table era), that named range transfers as-is. It won't automatically become an Excel Table after conversion.
Yes. Static named ranges transfer cleanly — name, reference, and scope are all preserved. Formulas and VBA that reference them by name continue to work without modification.
Check for: duplicate names with scope conflicts, dynamic ranges using OFFSET that may behave differently in .xlsx, names with external references to other .xls files, and any names showing #REF! errors in the Name Manager after conversion.
Yes, as long as you save as .xlsm (not .xlsx) to preserve the VBA. VBA references named ranges by name string, not cell address. If the name exists in the converted file — which it does — the VBA finds it.
LegacyLeaps preserves your named ranges, formulas, macros, and formatting. Download the free scanner to see exactly what's in your files before you convert.
Download Free ScannerPractical fixes for legacy Excel and Access problems. No spam.