Excel: Why Do I Have Only 65,536 Rows?

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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?

  1. 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, ROI2016, 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 _ROI2016 (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.