65,536 is the maximum number of rows for the Excel 97-2003 file format (.xls). In the Excel Workbook file format (.xlsx) the row limit is 1,048,576.
If you’ve specified that you want to save Excel files in the Excel 97-2003 file format (.xls) by default then when you create a new workbook it will be limited to 65,536 rows and [compatibility mode] will appear in Excel’s title bar.
If you want to take advantage of the 1,048,576 row limit in Excel 2010 then you need to change your Default Save Format to Excel Workbook (.xlsx). To do this complete the following steps:
- Go to the File ribbon in Excel
- Select Options
- Select Save on the left menu
- Change the Save files in this format setting to Excel Workbook (*.xlsx)