A worksheet in current Microsoft Excel for Microsoft 365 can hold 1,048,576 rows, numbered 1 through 1,048,576. That is the limit for one sheet; a workbook can contain multiple sheets, but the total data it can handle depends on available memory and other practical constraints.
The row limit in current Excel
The modern Excel grid has 1,048,576 rows and 16,384 columns per worksheet. The last column is XFD. These are fixed worksheet dimensions, not a setting that can be increased by changing an option or buying a larger Excel license. Microsoft 365, Excel 2021, and later desktop versions use this grid, as do current versions of Excel for the web.
The row number at the left edge of the worksheet is the clearest way to see the boundary: the final possible row is 1,048,576. You can select it, enter data in it, or include it in a formula. You cannot insert another row below it, because there is no row 1,048,577 in that worksheet.
The limit applies separately to each worksheet. For example, one sheet could contain a million transaction records while a second contains a million customer records. That does not mean Excel can always work smoothly with that much information: formulas, formatting, charts, connections, and the computer’s memory all affect performance.
Why older Excel files have fewer rows
Excel’s row capacity changed substantially with the 2007 release. Excel 2007 and later expanded the worksheet from 65,536 rows to 1,048,576. The older grid had 256 columns, ending at IV; the expanded grid has 16,384 columns, ending at XFD. The larger grid is one reason modern workbooks commonly use the .xlsx format instead of the older .xls format.
Excel 97–2003 workbooks in the .xls format retain the older worksheet limits. If you open one of these files in a current version of Excel, the file does not automatically become an unlimited or larger worksheet just because the newer program can display more rows. Saving a workbook in the older format can also mean that data or features outside that format’s capabilities cannot be preserved. Excel may display a compatibility warning when you save in an older format.
If an old file seems to stop at row 65,536, check its file type and the format in which it was saved. For ongoing work that needs the modern grid, save a copy in a current workbook format such as .xlsx, after checking that doing so will not disrupt a workflow that depends on the older file.
What counts as a row of data?
The limit counts worksheet rows, whether they are blank, formatted, or filled with values. It is not a limit of 1,048,576 records plus a separate header row. If a dataset has a heading in row 1, the remaining rows can hold up to 1,048,575 records on that sheet. For instance, a table with one header row and records in rows 2 through 1,048,576 uses the full grid.
A row can contain values in many columns, but adding columns does not create more rows. Nor does splitting a record across several columns reduce the row count. If a dataset has 1.2 million records, it will not fit in one worksheet simply because each record uses only a few columns.
Also distinguish a worksheet’s physical capacity from its used range. The used range is the area Excel regards as having been used, and it can extend beyond the cells that currently show data. Formatting applied to distant cells, or content that was later deleted, can sometimes make the used range appear much larger than the visible dataset. This can affect navigation and file size, but it does not change the fixed row limit.
How to check the last row or go to the limit
Look at the row labels along the left side of the grid to confirm the worksheet’s boundary. To jump directly to the final row in desktop Excel for Windows, click the Name Box to the left of the formula bar, type A1048576, and press Enter. Excel selects that cell. You can replace A with another column letter if you want to inspect a different column at the bottom of the sheet.
To check where your data actually ends, inspect the relevant column rather than assuming the bottom of the used range is the last record. A column may have blank cells between records, or formatting may extend below the data. Excel’s Ctrl+End shortcut on Windows moves to a cell Excel considers the end of the used range, which is useful for orientation but is not a reliable count of records by itself. On Mac, use Command+End where that shortcut is available; keyboard behavior can vary with the keyboard and system settings. You can also use the Name Box to move to a known address without relying on a shortcut.
For a structured dataset, keep the headings in one row and make each record occupy one row. This makes it easier to sort, filter, count, and import the data accurately. If you need to verify the number of records, count the populated entries in a column that should be filled for every record, and account for the heading separately.
What happens when data exceeds the limit?
Excel cannot place more than 1,048,576 rows on one worksheet. If you try to insert a row at the bottom, the operation is blocked. Pasting a dataset that is taller than the grid cannot put all of its records into the sheet. When importing data, the chosen destination matters: a worksheet load is restricted by the worksheet limit, while a data model or an external database is a different storage destination.
Be particularly careful when opening a large CSV file directly in Excel. A CSV is a text file, not an Excel worksheet, and it may contain more lines than Excel can display in a sheet. Excel’s grid cannot show the excess rows. If every record matters, do not assume that a worksheet display represents the complete source file; preserve the original and use an import or data-storage method appropriate to its size.
Splitting an oversized dataset into multiple sheets can help when you need to work with it in a workbook, but it introduces practical complications. Formulas and summaries must account for every sheet, records may be harder to filter together, and manual splitting can create gaps or duplicates. For regular analysis of data larger than a sheet, an appropriate query, database, or data model is usually more reliable.
Ways to work with more than one million records
Choose a method based on what you need to do with the data, not just on whether Excel can open the source file. Common options include:
- Use Power Query and the Data Model: In Excel for Microsoft 365 on Windows, start with Data > Get Data > From File > From Text/CSV for a text file. In the import process, choose a data-model or connection destination rather than loading the full result onto a worksheet. This can support analysis of larger datasets without putting every record in the grid, although available memory and model constraints still matter.
- Keep the source in a database: A database is a better home for very large or frequently updated datasets. Excel can connect to external data for analysis, so the entire source does not need to be copied into a single sheet.
- Filter or aggregate before loading: If the question concerns totals by month, region, or category, summarize the source first rather than importing every detail row. This can make analysis faster and the workbook easier to maintain.
- Split data only when the workflow supports it: Separate sheets or files may be practical for archival or staged processing. Document the split criteria and use a repeatable process so records are not omitted or counted twice.
Power Query is available in Excel for Microsoft 365, though some data sources and interface details can vary by platform. Excel for the web has a different set of capabilities from desktop Excel, so check which import and data-model features are available in the version you use. Loading a query to a worksheet does not bypass the worksheet’s row cap; the destination must be chosen deliberately.
Row capacity is not the same as performance
A worksheet’s maximum size tells you how many rows can exist, not how quickly a workbook will calculate or how large a file should be. A sheet with a million simple text values may behave differently from one with many volatile formulas, conditional formatting rules, images, or complex references. The computer’s memory, Excel’s architecture, and the workbook’s design all matter.
For example, a formula copied into every row can add a large calculation burden even though the row count is legal. Formatting entire columns or very large ranges can also make files cumbersome. Use only the ranges needed, remove unused formatting when appropriate, and avoid filling every row with formulas if a smaller data range will do.
There is no universal workbook-size or row-count threshold at which Excel will always become unusable. Two workbooks with the same number of rows can have very different performance. If a workbook becomes slow, identify the source of the cost—such as calculations, external connections, or excess formatting—rather than treating the row limit as a performance guarantee.
Frequently Asked Questions
Can I increase the number of rows in an Excel worksheet?
No. The worksheet grid has a fixed maximum of 1,048,576 rows in current Excel. To handle more records, use multiple sheets with care, or use a data model, database, or other suitable storage and analysis method.
How many rows can an Excel file have in total?
There is no single practical total-row figure for a workbook that applies to every computer and workbook. Each worksheet has the fixed row limit, and a workbook can have multiple sheets. The amount of data the workbook can actually store and use depends on memory, workbook features, and other resource limits.
Does Excel have 1,048,576 rows including the header?
Yes. The limit counts every worksheet row, including a row used for column headings. With headings in row 1, there are 1,048,575 remaining rows for records on that sheet.
Why does my Excel file stop at 65,536 rows?
The workbook may be using the older Excel 97–2003 .xls format, whose worksheets are limited to 65,536 rows. Check the file format and consider saving a copy in .xlsx if your work requires the modern worksheet size and compatibility permits.
Can Excel open a CSV with more than one million lines?
A CSV can contain more lines than fit in an Excel worksheet, but the worksheet cannot display them all. For a large CSV, use an import workflow that can load to a data model or connect to another data store, and keep the original file so you can verify that no records were lost.
Related
- Excel Power Query: Combine Columns Without Losing Data
- Excel Not Summing Numbers? Causes and Fixes
- Excel Not Showing All Rows? Find and Restore Hidden Rows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create a Hanging Indent in Word for APATo create an APA-style hanging indent in Microsoft Word, select the reference entries, open the Paragraph settings, and…
- Google Sheets Spreadsheet: What It Is and How to Use ItGoogle Sheets is Google’s web-based spreadsheet app: you can enter information in a grid, calculate values with formulas,…
- How to Change the View in Outlook Sent ItemsTo change how sent messages appear, open the Sent Items folder and use Outlook’s view controls: in classic…
- Excel Freeze Panes in Two Directions: Rows and ColumnsYes. In Excel for Microsoft 365 on Windows, you can freeze rows and columns at the same time:…
- Google Sheets VLOOKUP: How to Find and Return DataGoogle Sheets VLOOKUP searches the first column of a range for a value and returns a value from…
- Out of Office in Outlook: Set It Up on Desktop or MobileTo set an out-of-office reply in Outlook, open Automatic Replies for the account you want to use, turn…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.