This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Why Do I Have Only 65,536 Rows?
Problem: Hey! Microsoft said that the grid in Excel 2010 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?
Figure 40 This workbook only has 65,536 rows.
Strategy: Files created in Excel 2003 and stored with an .xls extension are opened in Excel 2010 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 2010’s larger grid introduces an interesting problem. In Excel 2003, you might have a spreadsheet with named ranges such as TAX07, ROI2011, and so on. In Excel 2010, these names are now actual cell addresses! If you open a workbook that had these names defined and then convert to Excel 2010, Excel will change the named range to _ROI2011 (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.