Your Excel spreadsheet just ate your data. You imported 80,000 rows from a database and only 65,536 showed up. No error message. No warning dialog. The other 14,464 rows are gone — silently dropped because your file is in .xls format.
This is the most dangerous thing about the legacy .xls format: it doesn't tell you when it truncates your data. You find out weeks later when your reports are wrong, your totals don't match, and nobody can figure out why.
Here's exactly what's happening, why it happens, and how to fix it permanently.
The .xls format (Excel 97-2003 Binary File Format) has a hard ceiling: 65,536 rows and 256 columns per worksheet. This isn't a setting you can change. It's baked into the binary file specification. No amount of Excel configuration will raise it.
The .xlsx format (introduced with Excel 2007) rewrote the rules completely:
| Feature | .xls (Excel 97-2003) | .xlsx (Excel 2007+) |
|---|---|---|
| Maximum rows | 65,536 | 1,048,576 |
| Maximum columns | 256 (A-IV) | 16,384 (A-XFD) |
| Max file size | ~56 MB practical limit | Much larger (compressed XML) |
| Macro support | Yes (embedded) | No (use .xlsm) |
| File format | Proprietary binary (BIFF8) | Open XML (ZIP of XML files) |
| Formula length | 1,024 characters | 8,192 characters |
| Conditional formats per cell | 3 | Unlimited |
| Sort levels | 3 | 64 |
That's a 16x increase in rows and a 64x increase in columns. The .xls format was designed in 1997 when 65,536 rows seemed like plenty. It's not 1997 anymore.
Nobody plans to hit 65,536 rows. It sneaks up on you. Here are the common patterns:
.xls and nobody thought to change it..xls, the import truncates at 65,536 rows. You save the file, close it, and the truncated data is now the only copy.This is the part that should concern you. The behavior is not consistent, and that makes it worse:
.xls workbook, Excel may load only the first 65,536 rows with no error and no warning. Your data looks fine. It just isn't all there..xls file, Excel may paste only what fits. The rest vanishes..xls context drops everything after the limit. If you then save and close, the original CSV data is lost if you overwrote the source.The silent truncation is the real danger. You can run a business on wrong numbers for months before anyone notices.
The fix is straightforward — with one critical caveat:
.xls file in ExcelYour new file now supports 1,048,576 rows. Re-import your full dataset if the original data was truncated.
Do not just rename the file extension. Changing report.xls to report.xlsx in your file explorer does not convert the file. It corrupts it. The internal binary format is completely different. You must use Save As from inside Excel.
And here's the caveat that catches people: if your .xls file contains macros, saving as .xlsx strips them out. Excel will warn you, but if you click through the dialog without reading it, your VBA code is gone.
Upload your file and LegacyLeaps will scan for row limit problems, macro compatibility issues, and data integrity risks in 60 seconds. Free.
If your .xls file contains VBA macros, you need .xlsm — not .xlsx.
The .xlsm format gives you everything .xlsx offers (1,048,576 rows, 16,384 columns, Open XML compression) while preserving your VBA code. Same Save As process, different format selection:
How do you know if your file has macros? Press Alt+F11. If you see any code modules with actual VBA code (not just empty modules), you need .xlsm.
Quick decision tree:
.xlsx.xlsm.xlsm (safe default)The Save As works for most files. But if your .xls file has been in production for years, there are edge cases that can bite you:
Some VBA code that worked in the .xls binary format behaves differently in .xlsm. Code that manipulates the file at a binary level, references .xls-specific properties, or uses deprecated Excel 4.0 macro functions may break. You won't know until you run it.
Named ranges that reference the old column limit (column IV, or column 256) may need updating. If a named range is defined as $A$1:$IV$65536, it will still work in .xlsx but won't automatically expand to the new limits. You have to update the range definitions manually.
If other workbooks link to your .xls file by path and filename, saving as .xlsx changes the filename. Every linked workbook needs its references updated, or the links break. In a shared network environment with dozens of interconnected files, this cascading update is where most conversions stall.
Pivot tables in .xls files store a cache of the source data. When you convert to .xlsx, the pivot cache converts too — but if the original source data was truncated at 65,536 rows, the pivot cache only contains the truncated data. You need to refresh the pivot from the actual source after conversion.
The .xls format limits you to 3 conditional formatting rules per cell. The .xlsx format removes that limit. Your existing rules will convert fine, but if you had workarounds for the 3-rule limit (helper columns, nested formulas), those workarounds are now unnecessary complexity.
One file is easy. A hundred files across an organization is a project.
Most IT teams start with PowerShell and Excel COM automation:
# Basic PowerShell conversion (requires Excel installed)
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
Get-ChildItem "C:\Legacy\*.xls" | ForEach-Object {
$wb = $excel.Workbooks.Open($_.FullName)
$newPath = $_.FullName -replace '\.xls$', '.xlsx'
# 51 = xlOpenXMLWorkbook (.xlsx)
# 52 = xlOpenXMLWorkbookMacroEnabled (.xlsm)
$wb.SaveAs($newPath, 51)
$wb.Close()
}
$excel.Quit()This works for basic files. Here's where it falls apart:
.xlsx, stripping macros from any file that had them. You need logic to check for VBA projects and route to .xlsm instead..xls, you've just converted the truncated version..xlsm but still break because the VBA code uses 32-bit API calls, missing references, or deprecated functions.LegacyLeaps handles all of this automatically. Upload a batch of .xls files and the platform:
.xlsx or .xlsm accordinglyYour file is in .xls format (Excel 97-2003), which has a hard limit of 65,536 rows per worksheet. This is a format constraint — not a bug. Any data beyond row 65,536 is silently dropped during import or save. Convert to .xlsx to get 1,048,576 rows.
Not always. When importing from CSV or external sources into .xls, Excel may silently truncate data past row 65,536 without any warning dialog. You lose rows and don't know it. This is one of the most dangerous aspects of the .xls format.
Yes. The .xlsx format does not support VBA macros. If your .xls file contains macros, save it as .xlsm (macro-enabled workbook) instead. This preserves your VBA code while giving you the higher row and column limits.
The .xlsx format supports up to 1,048,576 rows and 16,384 columns per worksheet. That is 16 times more rows and 64 times more columns than the .xls format.
Yes. You can use PowerShell scripts with Excel COM automation to loop through files, but this is slow and error-prone with macros. LegacyLeaps handles batch conversion automatically — detecting macros, preserving VBA code, validating data integrity, and outputting .xlsx or .xlsm as appropriate.
Upload your .xls files and get them converted to .xlsx or .xlsm with full data validation and VBA compatibility fixes. Use code FIRSTFILE for 20% off your first file.
Related Articles
Practical fixes for legacy Excel and Access problems. No spam.