In modern Microsoft Excel, one worksheet can hold a maximum of 1,048,576 rows, numbered 1 through 1,048,576. That limit applies to each worksheet, not to the entire workbook; the number of worksheets you can use depends on available memory and other system resources.
What the row limit means
A row is one horizontal line of cells, and Excel numbers worksheet rows down the left edge. The maximum row number is 1,048,576, so a worksheet cannot contain a row 1,048,577. This is a fixed worksheet boundary, not a recommended amount of data and not a setting you can increase.
If your data has a heading row, that heading uses one of the available rows. For example, a list with column names in row 1 can contain up to 1,048,575 data records beneath them, ending at row 1,048,576. If every row is a record and there is no header, the sheet can use all 1,048,576 rows for records.
Excel worksheets also have a column limit: 16,384 columns, labeled A through XFD. A worksheet is therefore a grid with a maximum of 1,048,576 rows by 16,384 columns. You do not need to fill the whole grid for the row limit to apply; the boundary is the same for a small table and a very wide one.
How the worksheet limit works
The row and column limits define the space in which cells can exist. A workbook can contain multiple worksheets, and each has its own grid with the same maximum dimensions. Adding a second sheet does not extend the first sheet past row 1,048,576; it gives you another separate grid.
Excel tables, filters, formulas, and ordinary cell ranges all operate within that worksheet grid. Converting a range to a table can make sorting and filtering easier, but it does not create extra rows. Similarly, hiding rows or changing the zoom level only changes what is visible; it does not change how many rows the sheet supports.
The row limit is also separate from the amount of data a workbook can comfortably process. A sheet may technically have room for a million rows while calculations or editing become slow much earlier. Conversely, a workbook can contain data in an Excel Data Model without placing every underlying record into worksheet cells.
Excel versions and file formats
The 1,048,576-row limit applies to current Excel for Microsoft 365 on Windows and to modern Excel versions beginning with Excel 2007. Excel for the web also uses this worksheet limit. The exact experience of editing or importing data can vary by version and available resources, but the standard worksheet boundary remains the same.
Older Excel versions had a much smaller grid. Excel 97 through Excel 2003 supported 65,536 rows and 256 columns per worksheet. The older .xls file format retains those compatibility limits. If a workbook is saved in .xls format or opened in compatibility mode, data beyond the older boundary cannot be represented in that format as ordinary worksheet cells.
Saving an old .xls file as .xlsx does not recreate records that were already omitted or kept outside the old grid. If you receive a legacy workbook, check the source data before converting it. When a file must remain compatible with Excel 97–2003, plan around the 65,536-row limit rather than the modern maximum.
Maximum rows versus practical performance
Being below the limit does not guarantee that a workbook will feel fast. Performance depends on the computer, Excel version, workbook design, and what Excel must calculate or display. A sheet with many simple values may be easier to handle than a smaller sheet with extensive formulas, formatting, charts, and linked data.
Common sources of slowdown include formulas repeated across large ranges, conditional formatting applied to entire columns, complex array calculations, numerous external links, and large numbers of shapes or charts. Formatting thousands of otherwise empty rows can also increase workbook size and make navigation less convenient. A 64-bit edition of Excel can use more memory than a 32-bit edition on a suitable system, but it does not remove the worksheet row limit or guarantee faster calculations.
When a large file is slow, useful first checks include:
- Remove formatting and formulas from rows or columns that are not part of the actual data.
- Replace unnecessarily broad formula references with ranges that match the data.
- Review conditional formatting rules and external links for rules that cover far more cells than needed.
- Consider whether the data needs to be displayed on a sheet or only stored and summarized.
These steps can improve manageability, but they cannot make a worksheet exceed its fixed maximum. If the data itself is larger than the grid, it needs a different storage or analysis approach.
What happens when data exceeds the limit?
Excel cannot put a value into a cell below row 1,048,576. If a source file or query contains more records than fit on a sheet, an attempt to load all of them as worksheet cells cannot produce a complete one-sheet result. The exact warning or import behavior depends on the method and Excel version, so do not assume that a visible worksheet contains every source record.
For example, a CSV file with 1,100,000 data records is not the same thing as an Excel worksheet. The text file may contain that many lines, but opening or loading it into a single worksheet cannot display all 1,100,000 records as rows. Check the record count in the source or import process and verify what was loaded before using the sheet for reporting.
Splitting records across worksheets can keep them inside a workbook, but it changes how they are handled. A filter or sort on one sheet will not automatically include the others, and a standard table cannot span multiple sheets. Splitting may be reasonable for separate periods or categories, but it is often awkward when users need one unified dataset.
Ways to work with more than one million records
If the full dataset is larger than a worksheet, choose a method based on what you need to do with it. Excel’s Power Query tools can connect to and transform data, and a query can be loaded to the Data Model rather than loaded as worksheet rows. The Data Model can hold more records than a single worksheet, with its actual capacity depending on memory and the data involved.
In Excel for Microsoft 365 on Windows, use the Data tab to access Get & Transform Data and import or connect to a source. When setting the query’s destination, choose the option to add the data to the Data Model instead of loading the entire result to a worksheet. You can then create a PivotTable based on the workbook’s Data Model to summarize the larger dataset. A PivotTable report still displays cells on a worksheet, so its visible output must fit within worksheet boundaries.
Other options include keeping the records in a database, data warehouse, or another system designed for large datasets, then bringing only the needed summary or filtered results into Excel. A database is usually a better home when many people need consistent access, updates, or queries over a large collection. Excel can remain useful as a reporting and analysis tool without serving as the only storage location.
- Use a worksheet when the complete dataset fits and people need to edit individual records directly.
- Use the Data Model when you need Excel-based analysis of a dataset too large to load into one sheet.
- Use a database or data platform when the data needs to grow, be shared, or be managed as a persistent central source.
Choosing the right approach for your data
Start by distinguishing the total source records from the rows that need to appear in a worksheet. A million-row source may be useful as the basis for a handful of totals, trends, or grouped summaries. In that case, importing everything into visible cells may add effort without helping the reader. If users need to inspect or change each record, worksheet capacity and performance become more important.
Also consider how the data will be refreshed. A one-time export can sometimes be split or reduced manually, but a regularly refreshed dataset is safer when its transformation steps are repeatable. Power Query can preserve import and transformation steps for supported sources, while a database can manage the underlying records and provide filtered results. The appropriate choice depends on the source, the task, and the skills of the people maintaining it.
Before choosing, ask:
- How many records are in the source today, and how quickly is that number growing?
- Do users need to edit every record, or only review summaries and selected details?
- Will the data need to be sorted, filtered, or refreshed as one connected dataset?
- Can the required analysis be done in the Data Model or in a database, with a smaller result returned to a sheet?
There is no benefit in filling a worksheet simply because it can hold a million rows. Keeping only relevant detail in the sheet can make a workbook easier to share, understand, and maintain.
Frequently Asked Questions
Can I increase Excel’s maximum number of rows?
No. The worksheet limit is built into Excel and cannot be raised through a setting, formula, or table option. To handle more records, use the Data Model or a database, or divide the information into separate sheets if the separate sections make sense for your work.
Does Excel have one million rows or 1,048,576 rows?
The precise limit is 1,048,576 rows. People often describe it as roughly one million, but the exact number matters when checking capacity. With a header in the first row, the maximum number of data records on that sheet is 1,048,575.
How many rows can an Excel table have?
An Excel table is made of worksheet cells, so it cannot extend past row 1,048,576. Its header and any total row also occupy worksheet rows. A table does not have a separate, larger row allowance.
Can Excel open a CSV with more than 1,048,576 lines?
A CSV is a text file, not an Excel worksheet, so its contents are not defined by Excel’s worksheet grid. However, loading all its records into one sheet is constrained by the worksheet limit. Use an appropriate import or database workflow and verify how many records were brought in.
Can multiple worksheets hold more than one million rows in total?
Yes. Each worksheet has its own limit, so several sheets can collectively contain more than 1,048,576 rows. The number of sheets is limited by available memory and system resources, and splitting one dataset across sheets may make unified filtering, sorting, and analysis less convenient.
Related
- Excel Not Letting You Merge Cells? Fixes That Work
- Excel Not Inserting Columns? Common Causes and Fixes
- Excel Not Highlighting Cells When Selected? Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Duplicate: Copy a File, Tab, or DataYes—Google Sheets can duplicate an entire spreadsheet, a single sheet tab, or a selected range of cells. Choose…
- DAX Measure: Multiply Two Columns CorrectlyYes. In Power BI, use an iterator such as SUMX to multiply two columns row by row and…
- Add a Shared Mailbox in Legacy Outlook for MacTo add a shared mailbox in legacy Outlook for Mac, open Tools > Accounts, select your Exchange account,…
- Excel Power Query: Compare Two Columns Step by StepIn Excel Power Query, compare values on the same row by opening Add Column > Custom Column and…
- Flash Fill vs AutoFill in Excel: What’s Different?Flash Fill recognizes a pattern in example data and fills in similar text, while AutoFill copies cells or…
- How to Add a Header in Microsoft Word: Step-by-StepTo add a header in Microsoft Word, open the document, select Insert > Header, and choose a built-in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.