Excel allows up to 1,048,576 rows per worksheet. CSV has no fixed row limit in its format, so a CSV file can contain more records—but Excel cannot display more than 1,048,576 rows on a single sheet, and other software or your computer may impose practical limits.
Excel’s worksheet row limit
In Microsoft Excel for Microsoft 365 on Windows, a worksheet is a grid of 1,048,576 rows and 16,384 columns. The last column is XFD. These are limits on each worksheet, not on the number of worksheets in a workbook. Adding another sheet gives you another grid, but it does not turn one sheet into a larger one.
The row ceiling applies whether the cells contain values, formulas, or a mixture. A formatted Excel table also cannot extend beyond the worksheet grid. If your data has a header row, that header takes up one of the available rows, leaving room for at most 1,048,575 data rows on that sheet. If you need to preserve more records, the extra records must remain in another file, be placed on another sheet, or be handled outside the worksheet grid.
Older articles sometimes quote a limit of 65,536 rows. That was the worksheet limit in Excel 97–2003, not in current Microsoft 365 Excel. The older limit is still relevant when working with legacy .xls files or software that supports only that older format.
Why CSV has no universal row limit
CSV stands for comma-separated values. It is a plain-text way to represent tabular data: each record is written as a line of text, and separators divide the fields. Despite the name, files may use other delimiters, such as semicolons or tabs, depending on the program and regional settings. CSV does not define an Excel-style maximum number of records. A file can therefore contain more than 1,048,576 records.
That does not mean every CSV file can grow without end in practice. Its usable size depends on the application that creates, stores, transfers, or reads it, as well as available disk space and memory. A text editor may struggle with a file that a database tool can process, while a program with a small import limit may stop long before the file itself reaches a storage limit. There is no single row count that applies to all CSV readers.
Also, a physical line is not always the same as a CSV record. A quoted field can contain a line break, so one record may span multiple lines in the text file. When counting data, count parsed records rather than simply counting newline characters. CSV dialects and malformed quoting can also affect how different programs interpret the same file.
What happens when Excel opens a large CSV
When you open a CSV in Excel, Excel must place the imported data into its worksheet grid. If the file contains more records than the grid can hold, a worksheet cannot show them all. Do not assume that opening the file proves every record was loaded: check the row count, and keep an untouched copy of the original CSV. A file may contain millions of records even though the sheet shows only a portion.
Opening a CSV directly is convenient for smaller, familiar files, but Excel has to interpret the text using its import assumptions. If the delimiter, character encoding, or data types are not what Excel expects, columns can be combined or values changed. For more control in Microsoft 365 for Windows, use Data > From Text/CSV. The import experience lets you inspect a preview and adjust parsing options before loading. The preview is not necessarily a count of every record in the file.
Loading the result to a worksheet still means the worksheet’s row limit applies. Power Query can also load data to the Data Model rather than directly to a sheet, which is useful for analyzing a dataset too large to display as worksheet rows. The Data Model is not unlimited: practical capacity depends on factors such as available memory, Excel’s architecture, and the data’s size and shape. Mac and web versions can have different import options and feature availability, so check the commands available in your version before following Windows-specific steps.
A concrete example of the difference
Suppose a sales export contains 1,200,000 records plus a header. As a CSV, it can represent all 1,200,001 lines if the file is correctly formed and the software handling it can manage that size. A single Excel worksheet has room for only 1,048,576 rows total, including the header. That leaves 1,048,575 data rows available, so 151,425 data records do not fit on that one sheet.
The calculation is simple: 1,200,000 records minus 1,048,575 available data rows equals 151,425 records over the worksheet capacity. The difference is not a special CSV feature or a setting that can be switched on in Excel; it is a difference between a text-file format with no standardized row ceiling and a spreadsheet grid with a defined size.
If the file is divided into multiple sheets, remember that splitting it is not always convenient. Formulas, sorting, filtering, and reporting across separate sheets may become more complicated, and each sheet still has its own maximum. For a dataset that is routinely larger than a worksheet, a data model or database is often a better fit than repeatedly dividing the file.
What limits a large CSV in practice
Even if CSV itself has no set maximum, several bottlenecks can determine whether a large file is usable:
- Memory and processing time: A program may need substantial memory to parse, sort, or display a large dataset. Slow operations do not necessarily mean the CSV is corrupt.
- File size and storage: Long text fields, many columns, and large numbers of records increase the file’s size. Disk space and transfer limits can matter before a row-count limit does.
- Import-tool constraints: A viewer, reporting tool, or data pipeline may impose its own limits, independent of the CSV format.
- Record structure: Unescaped delimiters, inconsistent field counts, or line breaks inside quoted fields can cause readers to interpret records incorrectly.
- Data types: Identifiers, dates, and long numbers can be misread or reformatted when a program guesses their types.
For large files, a 64-bit application and sufficient memory can help with some workloads, but neither removes Excel’s worksheet row ceiling. A more capable computer may improve processing; it cannot expand the grid beyond its documented dimensions.
Ways to work with more records than one sheet holds
Choose a method based on what you need to do with the records, not just on the file’s row count. Common options include:
- Use Power Query and the Data Model: Import and transform the CSV, then load it to the Data Model for analysis without putting every record on a worksheet. Capacity still depends on system and model constraints.
- Use a database: A database is designed to store and query large tables. It can filter or aggregate records before you bring a manageable result into Excel.
- Process the CSV with a suitable data tool: A command-line utility, programming environment, or analytics application may be able to filter, split, or summarize the file without displaying every row in a spreadsheet.
- Split the file deliberately: For a one-off task, divide records into smaller files or sheets, retaining the header and a clear method for tracking which records are in each part. Avoid splitting in the middle of a quoted CSV record.
For analysis, it is often better to aggregate first. If the question is total sales by month, you may need a table of monthly totals rather than every transaction visible at once. Keep the full source CSV unchanged so you can repeat or audit the transformation later.
Common misconceptions about the limit
Changing the file extension does not increase capacity. Renaming a CSV to .xlsx does not convert it into a larger worksheet. It changes only the name unless the data is actually imported and saved in a different format—and the worksheet grid remains the same size.
More sheets do not make one sheet larger. A workbook can distribute information across worksheets, but each sheet has the same row limit. Some Excel analysis features can work with data stored outside the visible grid, but that is different from placing unlimited rows on a worksheet.
CSV is not a workbook format. It does not preserve multiple sheets, cell formatting, charts, or Excel formulas as formulas. When Excel saves a sheet as CSV, the export is a text representation of the sheet’s values, commonly the calculated results in formula cells. Keep an .xlsx copy if you need the workbook’s formulas and formatting.
A CSV with a million rows is not automatically too large for Excel in every way. It cannot all fit on one worksheet, but some workflows can query or model data without displaying all records in the grid. The right answer depends on whether you need to inspect every row, transform it, or calculate summaries.
How to choose between CSV and an Excel workbook
CSV is useful when you need a portable, plain-text table that different systems can exchange. It is also easy to process with many data tools. An Excel workbook is more appropriate when you need multiple sheets, formulas, formatting, charts, or workbook features. The file format choice does not change the worksheet’s maximum row count.
Before opening or converting a large export, ask what outcome you need:
- For a small file you want to inspect or edit, opening it in Excel may be simplest.
- For a large file you need to filter or reshape, use an import or data-processing workflow and confirm how many records it loaded.
- For repeated analysis of a dataset above the sheet limit, consider a database or Data Model rather than manual splitting.
- For long-term preservation, retain the original CSV and save a separate workbook for any Excel-based analysis.
Frequently Asked Questions
How many rows can Excel have?
Current Excel worksheets support 1,048,576 rows each. If the first row is a header, that leaves 1,048,575 rows for data on that sheet.
Can a CSV file have more than 1 million rows?
Yes. CSV has no universal format-defined row limit. Whether a particular program can create, open, or process a file of that size depends on the program and available system resources.
Does Excel truncate a CSV that has too many rows?
A worksheet cannot display records beyond its row capacity. If a file exceeds that limit, verify how many records were imported instead of assuming the sheet contains everything, and preserve the original CSV.
Can I increase Excel’s row limit?
No. The worksheet grid has a fixed maximum of 1,048,576 rows. Use another sheet, the Data Model, a database, or another tool when your workflow needs to handle more records than one sheet can hold.
Is a CSV better than Excel for large datasets?
CSV can store more records than a single Excel worksheet, but it has no built-in analysis features and no universal guarantee that a particular tool can process a very large file. For large-scale querying, a database or a suitable data model is often more practical than either opening the entire file as a sheet or relying on CSV alone.
Related
- Excel Shortcut to Select an Entire Column
- Excel Shortcut to Select to the End of a Column Explained
- Excel Shortcut to Refresh a PivotTable and All Data
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 Make a Weekly Schedule in Google Sheets Step by StepTo make a weekly schedule in Google Sheets, create a grid with time slots down the first column…
- Excel Table Maker: Create and Format Tables EasilyExcel has a built-in table maker: select your data, press Ctrl+T on Windows, confirm whether it has headers,…
- Excel MAX Between Two Dates: Find the Highest ValueTo find the largest value in B2:B100 for records dated between the start date in D2 and the…
- Google Sheets Apps Script If Statements: How They WorkIn Google Sheets Apps Script, an if statement runs code only when a condition is true; use else…
- How to Add a Header to Each Page in Microsoft WordTo add a header to every page in Word, choose Insert > Header, select a layout, type your…
- Excel Slicer Font Size: What You Can and Can’t ChangeIn Excel for Microsoft 365 on Windows, a slicer has no ordinary Font Size box on the ribbon.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.