You've inherited a pile of legacy Excel files — some from the early 2000s, some from a system that was last touched during the Obama administration — and you want to use Power Query to do something modern with the data. Combine files, clean columns, load to a data model, feed a Power BI report. Reasonable goals.
Then you discover that Power Query and old Excel files have a complicated relationship.
This guide explains exactly what works, what breaks, and the right order of operations for getting legacy workbook data into a modern Excel workflow cleanly.
Power Query can import .xls files. The Get Data → From File → From Workbook dialog accepts both .xls and .xlsx. So far, so good.
The problems start when you need Power Query to do anything beyond a basic table read:
| Operation | .xls source | .xlsx source |
|---|---|---|
| Read cell values from a named sheet | Works | Works |
| Reference a named range | Unreliable — may return null or error | Works |
| Reference a structured Table (ListObject) | Not supported | Works |
| Refresh query and get updated VBA-generated values | Cannot trigger VBA on refresh | Cannot trigger VBA on refresh |
| Combine multiple files from a folder | Works but slow; errors on corrupted files | Works, faster |
| Load to Power Pivot data model | Works | Works |
| Incremental refresh (Power BI) | Not supported | Supported |
The short version: if you're doing anything beyond "read the data from this sheet," .xls will cause problems.
Named ranges are one of the most common ways legacy Excel workbooks organize data. Finance teams especially love them — a named range called RevenueData or FiscalYear_Actuals is much cleaner than a raw cell reference.
In Power Query, you can reference a named range directly:
= Excel.Workbook(File.Contents("C:\Reports\budget.xls"), true)
[Name="RevenueData"][Data]{0}This works reliably in .xlsx files. In .xls files, it's unreliable for several reasons:
.xls named ranges that span multiple sheets use a syntax Power Query doesn't always parse correctly.The fix is always the same: migrate the source file to .xlsx first. Once the file is in the modern format, named ranges translate correctly and Power Query references resolve as expected.
Power Query works best with Excel Tables — the structured ListObject type you create with Insert → Table (or Ctrl+T). Tables have named headers, auto-expand as rows are added, and are directly accessible in Power Query by name.
Legacy .xls files don't have structured Tables. They have flat ranges that happen to look like tables. Power Query can read flat ranges, but you lose the clean table-name reference and have to specify column positions or header row detection manually.
When you migrate a legacy workbook to .xlsx, you also have the opportunity to convert flat ranges to structured Tables — which makes every downstream Power Query operation simpler and more maintainable.
Many legacy workbooks contain data that was generated, transformed, or validated by VBA macros. The raw cell values in the file are the output of the last time the macro ran — which might have been years ago.
Power Query reads cell values, not VBA logic. If a macro hasn't been run recently, Power Query will import stale data. If the macro runs automatically on file open (Workbook_Open or Auto_Open) and hasn't been run since the last edit, the Power Query refresh will import whatever was in the cells when the file was last saved.
This is not a .xls-specific problem — it applies to .xlsm files too. But it's most common with legacy files because they're often opened infrequently, and users don't realize the values they're querying are months or years out of date.
Before querying any legacy workbook with Power Query: Open it in Excel, let any macros run, verify the data looks current, save the file, then build your Power Query on top of it. Don't build a Power Query on a file you haven't opened and validated recently.
Power Query's Folder connector is one of its most powerful features: point it at a directory and it combines all matching files into one table. This is extremely useful when you have dozens or hundreds of legacy report files in the same format.
To combine .xls files from a folder:
.xls files.Performance note: Combining .xls files is significantly slower than combining .xlsx files because the legacy binary format requires more processing. For folders with 50+ files, migrating to .xlsx first can reduce refresh time from minutes to seconds.
If you're working with legacy files and want a reliable, maintainable Power Query setup, do this in order:
.xlsm; files without go to .xlsx.Doing it in this order — migrating first, then querying — avoids the frustrating experience of building a Power Query workflow on legacy files, having it break on refresh, and not knowing whether the problem is in your M code or in the source file format.
Usually means Power Query opened the file but couldn't find any named sheets or usable ranges. Check whether the file opens correctly in Excel itself. If it opens with compatibility mode warnings, that's your signal — migrate the format first.
Typically means a named range or Table name doesn't exist in the file. In legacy .xls files, this happens when the named range was defined with BIFF8 encoding that Power Query can't resolve. Migrate to .xlsx and recheck the Name Manager to confirm the range name survived correctly.
Often appears when combining .xls files from a folder and one file has a password, is corrupted, or is already open in another application. Check which file is causing the error (look at the error details in the Power Query editor), and either fix or exclude that file from the folder combine.
When legacy files are migrated from .xls to .xlsx, cells that were stored as numbers but formatted as text (or vice versa) may change their effective type. Power Query detects types on import — refresh your queries and use Transform → Detect Data Type to reset column types after migration.
Power Query can also connect directly to Access databases (.mdb and .accdb). This is common when legacy Access databases are used as data sources for Excel reports.
Key differences:
.mdb files use the Jet engine, which is 32-bit only. If you're running 64-bit Office (the default for most organizations now), you cannot connect to .mdb files from Power Query. This is one of the most surprising and frustrating compatibility issues in modern Excel..accdb files use the ACE engine, which is available in both 32-bit and 64-bit versions. Once migrated to .accdb, your Access data source works with Power Query in 64-bit Office.If you're building Power Query reports that pull from Access databases, migrating those databases from .mdb to .accdb isn't optional — it's a prerequisite for the connection to work at all in modern Office.
LegacyLeaps converts .xls to .xlsx/.xlsm and .mdb to .accdb while preserving formulas, macros, and formatting. Run the free scan first to see exactly what's in your files before committing to a conversion.
Once your source files are properly migrated to modern formats, Power Query becomes substantially more reliable:
The migration is a one-time investment. Build the Power Query workflows on clean, modern files and they'll refresh reliably for years.
Yes, Power Query can import .xls files using the Get Data → From File dialog. However, named range references and structured Table references are unreliable in legacy format, refresh performance is slower, and some features (incremental refresh, structured Table access) aren't supported. Migrating source files to .xlsx first produces more reliable results.
Legacy .xls files store named ranges in the BIFF8 binary format. When Power Query reads these, names referencing multiple sheets or using legacy syntax sometimes don't resolve correctly. The fix is to migrate the source file to .xlsx and verify the named ranges in Excel's Name Manager before rebuilding the query.
Renaming the extension doesn't convert the file format — the BIFF8 binary structure remains. Excel will either refuse to open it or open it in compatibility mode with errors. Proper format conversion (using LegacyLeaps or Excel's Save As) is required.
Queries stored in the workbook itself survive proper format conversion. However, queries referencing external .xls source files by path need their source paths updated after those files are migrated. Queries referencing named ranges may need minor adjustments if any ranges changed during conversion.
Yes — Power Query's Folder connector works with .xls files. For large file sets, migrating source files to .xlsx first is recommended: refresh is faster, errors are easier to diagnose, and the combined table is more reliable. Files with macros should be migrated to .xlsm.
Related reading:
A practical checklist for auditing VBA, named ranges, external links, and format compatibility before you migrate. Free.
Practical fixes for legacy Excel and Access problems. No spam.