In Microsoft Excel for Microsoft 365, a worksheet can contain up to 1,048,576 rows, numbered 1 through 1,048,576. That limit applies to each worksheet—not to the entire workbook—and includes header rows, blank rows, and hidden rows.
What Excel’s row limit means
A worksheet is the grid you see on one tab at the bottom of an Excel workbook. Its maximum size is 1,048,576 rows by 16,384 columns; the last column is XFD. The bottom-right cell is therefore XFD1048576. These grid dimensions are the same in Microsoft 365 for Windows, Excel for Mac, and Excel for the web.
The row count is a hard boundary, not a recommended dataset size. A sheet with 1,048,576 rows has reached its maximum even if most cells are empty. A header uses a row, too: if row 1 contains column names, the sheet has room for at most 1,048,575 subsequent records, assuming each record occupies one row.
Hidden rows still exist and count toward the total. Filtering a table hides records from view but does not remove those rows or create space for additional ones. Likewise, formatting an empty row does not make the worksheet longer; the grid already extends to its fixed maximum.
How the limit compares with older Excel versions
Modern Excel has supported 1,048,576 rows per worksheet since Excel 2007. Earlier versions, including Excel 2003, were much smaller: they allowed 65,536 rows and 256 columns, with the last column labeled IV. A file saved in the older Excel 97–2003 format, .xls, retains those older worksheet limits even when opened in a current version of Excel.
If an older workbook stops at row 65,536, check its file format and whether it is open in Compatibility Mode. Saving a copy in the modern .xlsx format can give the workbook the newer grid, but it does not automatically restore records that were cut off or never saved. Before converting a file, confirm that its data is intact. Excel’s compatibility checker can also flag features or content that may not be preserved when saving to an older format.
Does the limit apply to a workbook or a worksheet?
The 1,048,576-row maximum is per worksheet. A workbook can have several sheets, and each has its own grid. In principle, splitting records among multiple sheets lets a workbook contain more rows in total than one sheet can hold. The practical number of sheets and total data a workbook can handle depend on available memory and other system resources, rather than on a simple fixed workbook-wide row ceiling.
Splitting a dataset is not always a good solution. Data spread across tabs can be harder to sort, filter, summarize, validate, and refresh consistently. It also does not remove the limits of tools that expect one complete table. If the split is needed only to get past the worksheet ceiling, consider whether a database or a data model would be a better fit.
How to check how many rows your data uses
Worksheet row numbers appear along the left edge of the grid. To go directly to the last row Excel permits, use the Name Box to the left of the formula bar: type A1048576 and press Enter. This takes you to the bottom row in column A. It does not tell you how many records are populated, because blank rows may occur between records or below them.
For a quick visual check of a contiguous block, click a cell in the data and inspect the row numbers, or use the Name Box to jump to a location near the end. Navigation shortcuts can move to the edge of a filled region, but they may stop at a blank cell or jump over a block, so they are not a reliable count of records in messy data. For an accurate total, define what counts as a record and count the populated key field, while accounting for headers and blank entries.
For example, a list with one header row and records in rows 2 through 1,048,576 contains 1,048,575 data rows. If some of those rows are blank, the number of actual records is lower. Conversely, a worksheet can report or display a large used area because formatting or past edits extended far below the visible data; that does not mean every row contains a record.
What happens when data reaches the maximum
Excel cannot add a 1,048,577th worksheet row. If an operation would push cells beyond the grid—for example, inserting a row where existing content would have to shift off the bottom—Excel prevents the operation and displays a message. Pasting, importing, or loading more records than fit into a worksheet likewise cannot place all of them on that sheet.
At that point, do not assume that a partial result is complete. Check any warning from the import or paste operation, compare the number of source records with the number loaded, and keep an untouched copy of the source. If records are missing, use a method that can hold and query the full dataset rather than repeatedly trying to paste it into the same worksheet.
Excel tables do not bypass the grid limit. A table is a structured way to organize worksheet data, with features such as filter buttons and automatically expanding formulas. It still occupies worksheet cells, so its rows cannot extend past row 1,048,576. A table that starts below row 1 has fewer available rows than one that starts at the top.
Imports, Power Query, and the Data Model
Text files such as CSV do not share Excel’s worksheet grid. A CSV can contain more lines than Excel can display on one sheet, but opening it in Excel does not make the worksheet bigger. If the source exceeds the grid, loading everything to a worksheet is not possible; depending on the import route, Excel may stop at the worksheet limit or report that the data cannot all be loaded there.
Power Query can connect to sources, clean and combine data, and load results either to a worksheet or to the Excel Data Model. Loading to a worksheet remains subject to the 1,048,576-row cap. The Data Model is a different storage and analysis destination and is not limited by the worksheet’s row count in the same way, but it is not unlimited: capacity depends on memory, data shape, compression, and the Excel environment. A very large model can still be slow or exceed available resources.
For a large import in Microsoft 365 for Windows, the general route is Data > Get Data, choose the relevant source, and use the load options to select where the result goes. The exact choices depend on the connector and query. If the data is too large for a sheet, load it to the Data Model when that option is available, then analyze it with tools designed to work with the model, such as PivotTables. Excel for Mac and Excel for the web do not have identical Power Query capabilities or menus, so check the options available in your edition before planning a workflow.
Row capacity is not the same as workbook performance
A worksheet may allow more than a million rows, but that does not mean every workbook can comfortably use the full grid. Performance depends on factors such as the amount and type of data, formulas, formatting, volatile calculations, external connections, and the computer’s available memory. A large sheet filled with complex formulas can recalculate slowly well before it reaches the last row.
Excel is available in both 32-bit and 64-bit editions. A 64-bit installation can use more memory than a 32-bit installation, which may help with large workbooks, but it does not increase the worksheet row limit. The file format and the structure of the data matter as well. For example, storing repeated text in many cells can use more space than a compact, well-designed model, while unnecessary formatting across entire columns can make a workbook harder to manage.
- Keep only the columns needed for analysis, and remove duplicate or irrelevant records when appropriate.
- Avoid copying complex formulas into hundreds of thousands of rows unless the calculation is genuinely needed for each record.
- Use a table or a controlled data range so formulas and filters apply to the intended records rather than an unnecessarily large area.
- For recurring large imports, use a repeatable query or database workflow instead of manual copy-and-paste.
Choosing what to do with more than a million records
The right option depends on what you need to do with the records, not just their count. If you need to inspect a manageable subset, filter the source before loading it into a sheet. If you need summaries across the whole dataset, Power Query with the Data Model may be suitable, provided the workbook and computer can handle the model. If several people need reliable access to a growing dataset or you need complex queries and updates, a database is often more appropriate than dividing the data among many worksheet tabs.
Consider these questions before choosing:
- Do you need every individual record visible, or only totals and grouped summaries?
- Will the data grow beyond the worksheet ceiling, and how often will it be refreshed?
- Do users need to edit the records in Excel, or mainly analyze information held elsewhere?
- Can the data be filtered to a smaller, relevant period or category without losing the analysis you need?
Power BI, a database, or another data-analysis tool may be more practical for recurring analysis at a scale beyond a worksheet. These alternatives have their own capacity, licensing, and technical considerations; moving data out of a sheet does not make storage or processing limits disappear. For a one-time task, a filtered extract or a summary can be simpler than setting up a new system.
Frequently Asked Questions
How many rows can Excel have? A worksheet in current Excel can have 1,048,576 rows, numbered 1 to 1,048,576. That is the limit for each sheet, not a promise that a workbook can process that many complex records quickly.
Does Excel have one million rows or 1,048,576? The exact limit is 1,048,576 rows. People often describe it as “about one million” for convenience, but the precise grid limit is 1,048,576.
How many data rows fit if I have a header? With one header row in row 1, there are 1,048,575 remaining rows for data, from row 2 through row 1,048,576. Additional title or notes rows reduce that space further.
Can I increase Excel’s row limit? No. You cannot change the maximum number of rows in a worksheet. To work with more records, use multiple sheets cautiously, load data to the Data Model where suitable, filter the data, or use a database or another tool.
Why does my Excel file stop at 65,536 rows? It may be an older .xls workbook or open in Compatibility Mode. The .xls format uses the Excel 97–2003 grid limit of 65,536 rows. Check the file type and convert a verified copy to .xlsx if you need the modern worksheet size.
Do hidden rows count toward Excel’s limit? Yes. Hiding or filtering rows changes what you see, not the number of rows available in the worksheet. To make room in a full sheet, records must be removed or stored elsewhere; hiding them does not help.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
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 Manually Archive Emails in Outlook 365To manually archive an email in Outlook 365, select it and choose Archive on the toolbar. Outlook moves…
- How to Insert an Image in an Excel Cell and Keep It in PlaceTo put a picture directly inside an Excel cell in Microsoft 365 for Windows, select the cell and…
- Are Excel Worksheets Free? Options, Limits, and CostsYes— you can create and edit Excel worksheets for free in Excel for the web with a Microsoft…
- Make a Brochure in Microsoft Word Online and DesktopYes. You can make a brochure online in Word for the web by starting with a brochure template…
- Excel Max Rows per Sheet: Limits and WorkaroundsIn Microsoft Excel for Microsoft 365, a worksheet can contain a maximum of 1,048,576 rows and 16,384 columns.…
- How to Count Dropdown Values in Google SheetsTo count a particular dropdown choice in Google Sheets, use COUNTIF: if the dropdown answers are in B2:B100,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.