Why Do I Have Only 65,536 Rows?

July 30, 2021 - by Bill Jelen

Problem: Hey! Microsoft said that the grid in Excel was massively large—1.1 million rows by 16,384 columns. I opened my favorite Excel file, and I have only 65,536 rows. What’s going on?

In Compatibility Mode, the worksheet ends at IV65536 — Figure 39. This workbook only has 65,536 rows.

Strategy: Files created in Excel 2003 and stored with an .xls extension are opened in Compatibility mode. In this mode, you can only access the original grid size.

If you will not be using this file in Excel 2003 anymore, you should convert it to the new file format. Open the File menu and choose Convert. Excel will update the file, save the file, close the file, and reopen the file. You will have access to the entire grid.

Additional Details: Excel’s larger grid introduces an interesting problem. In Excel 2003, you might have a spreadsheet with named ranges such as TAX15, ROI2019, and so on. These names are now actual cell addresses! If you open a workbook that had these names defined and then convert to a new file format, Excel will change the named range to _ROI2019 (with an underscore). While most of your formulas will update, any functions that use the INDIRECT function or VBA code might need to be manually updated.

This article is an excerpt from Power Excel With MrExcel

Title photo by AZGAN MjESHTRI on Unsplash