Yes—a CSV file can be too large for Excel to display in a worksheet, even though the file itself may be valid. Excel for Microsoft 365 worksheets are limited to 1,048,576 rows and 16,384 columns; if your CSV exceeds either limit, use Power Query, a database, or another tool that can process the full data set.
Excel’s worksheet limits
Excel’s row and column limits apply to a worksheet, not to the CSV format in general. A CSV is a plain-text file containing values separated by delimiters, usually commas. It does not have Excel’s worksheet structure until a spreadsheet program reads it and arranges the values into rows and columns.
In Excel for Microsoft 365 on Windows, one worksheet can contain at most 1,048,576 rows and 16,384 columns. The last column is XFD. If the first row contains headers, a sheet can hold at most 1,048,575 rows of data beneath them. A CSV with more records cannot all fit in one worksheet, regardless of how much memory the computer has.
There are other worksheet limits worth knowing about. A single cell can contain up to 32,767 characters, and a cell can contain at most 253 line-feed characters. Excel also stores only 15 significant digits of numeric precision. These limits can matter when a CSV has exceptionally long text fields or long identifiers, such as account numbers that should be preserved exactly.
Excel does not have one simple file-size threshold that explains every large-CSV problem. How much it can open or process depends on the file, the computer’s available memory and resources, and the way the data is imported. A 200 MB file with short, regular records may behave differently from one of the same size containing very long fields or complicated quoted text.
Why a CSV and an Excel workbook behave differently
A CSV file stores text values and separators. It does not store multiple worksheets, cell formatting, charts, formulas as workbook formulas, or many of the other features found in an Excel workbook. When you open a CSV in Excel, Excel parses the text and displays the result as a grid. That display is still constrained by worksheet limits.
Saving the file as an Excel workbook does not remove those limits. An .xlsx file can keep several worksheets, but no individual worksheet can hold more than 1,048,576 rows. Splitting a data set across sheets may make it viewable in Excel, but it does not automatically make analysis across all those sheets convenient or safe.
CSV also does not reliably tell Excel what type each field is meant to be. Excel may interpret text as a date, convert a long digit string to scientific notation, or remove leading zeros from a code. For example, an identifier such as 001284 can become 1284 if Excel treats it as a number. This is a data-import issue rather than a file-size limit, but it often appears when handling large CSVs.
How to tell whether the file exceeds a limit
First identify whether the problem is the number of records, the number of fields per record, the file’s size, or a particular value. Excel may show a warning, open only part of the data, or become unresponsive while trying to parse a large file. A file that opens but ends abruptly may have more rows than the sheet can display; a file that opens with shifted columns may instead have a delimiter or quoting problem.
- Check the CSV’s source system or export settings for its expected record count and field count.
- Use a database, a command-line tool, or Power Query to count parsed records. Avoid relying only on a basic line count: CSV fields can contain quoted line breaks, so one record may span more than one text line.
- Check whether any record has more than 16,384 fields, or whether a text value may exceed Excel’s per-cell character limit.
- Keep the original file unchanged and compare a few records near the beginning and end with the imported data.
File size alone is not a dependable way to estimate the number of worksheet rows. A CSV with a few columns of short values can contain many records in a relatively small file. A file with long descriptions or encoded text may be large while containing far fewer records.
Import a large CSV with Power Query
For a CSV that fits within a worksheet but is awkward to open directly, importing it through Power Query gives you more control over parsing and data types. In Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From Text/CSV, choose the file, and inspect the preview. You can choose the delimiter and use Transform Data to adjust columns before loading.
- In Power Query, check that the delimiter and text encoding are correct. Incorrect choices can make fields appear in the wrong columns or display accented characters incorrectly.
- Set sensitive fields such as postal codes, product codes, or long identifiers to the Text data type before loading. This helps prevent unwanted number or date conversions.
- Remove columns or rows you do not need, filter the data, or perform other transformations before it reaches a worksheet.
- Choose Close & Load to put the result on a sheet, or use the load options to load it to the Data Model when worksheet display is not required.
Power Query does not make an unlimited data set fit on a worksheet. If you load more than the sheet’s row limit to a worksheet, that destination still cannot show every row. Loading to the Data Model can support analysis without placing every record in a sheet, but capacity depends on available memory and system resources. It is useful for supported PivotTable analysis, not as a way to browse every record in an ordinary grid.
Excel for Mac has Power Query capabilities in Microsoft 365, but available connectors and menu labels can differ by release. If you do not see the same Text/CSV command, check the Data tab and your Excel version’s available Get Data options. Excel for the web also has different import and data-source capabilities from desktop Excel, so use the Windows desktop steps when the exact Power Query options described here are needed.
What happens when the CSV exceeds the row limit
If you open a CSV containing more than 1,048,576 records in Excel, the worksheet cannot display them all. Depending on the opening or import method, Excel may warn that the data exceeds the sheet size or show only the portion that fits. Do not assume that the visible rows represent the entire file. Calculations, filters, or charts made from a partial import will also omit records that were not loaded.
Excel’s Data Model is one option when the goal is to summarize a larger file rather than inspect every row. Import the data with Power Query and choose the option to add it to the Data Model instead of loading it to a worksheet. You can then build certain PivotTables and summaries from the model. The model has practical limits tied to memory, data shape, and system resources; it is not a promise that any file will load successfully.
If you need to view, edit, or export every record individually, use a data tool designed for larger tables. A database can store and query records without requiring them all to appear in a single sheet. For repeatable analysis, a script or a specialized data-processing application may be a better fit than splitting the CSV manually.
Common problems that look like file-size limits
A slow or failed opening does not always mean the CSV is over Excel’s row limit. Some large files are simply expensive to parse, while some smaller files are malformed or use unexpected text encoding. Check the structure before choosing a workaround.
- Wrong delimiter: A file exported with semicolons or tabs may not split into columns as expected when Excel assumes commas. Select the correct delimiter in the import preview.
- Quoted commas or line breaks: A properly quoted field can contain commas and even line breaks. A simplistic tool that splits at every comma or counts every line may misread the records.
- Automatic type conversion: Dates, scientific notation, and dropped leading zeros can change how values appear. Import important identifiers as text.
- Encoding problems: If characters appear garbled, choose the appropriate file origin or encoding in the import process rather than editing the original data blindly.
- Long values: A single cell that exceeds Excel’s character limit can be cut off or otherwise fail to fit, even when the overall file has few rows.
Also consider whether the source system exported a complete file. If the CSV is unexpectedly small or ends mid-record, the export may have been interrupted before Excel ever received it. Compare the export’s stated record count or regenerate it before concluding that Excel discarded data.
Choose the right way to handle the data
The best solution depends on what you need to do, not just how large the file is. For a file that fits in a worksheet, Power Query is often a good choice when you need reliable column types, filtering, or repeatable cleanup. For a file larger than a worksheet, decide whether you need summaries or access to every raw record.
- Need a quick look at a small section? Import only the required columns and filter to the records relevant to the question.
- Need totals or grouped summaries across many records? Try Power Query with the Data Model, provided the computer can handle the data and the required analysis is supported.
- Need to search and update a very large data set repeatedly? Consider a database, which is designed for querying and managing structured records.
- Need to send a manageable extract to someone else? Filter or aggregate the source data in an appropriate tool, then export a smaller CSV with a clear description of what it includes.
- Need all rows in Excel sheets? Splitting the file can work only if each part stays within worksheet limits and you carefully preserve headers, data types, and record boundaries. It can make later analysis more complicated.
Avoid splitting a CSV with a basic text editor if fields might contain quoted line breaks. A split at an arbitrary line can cut a record in half. Use a CSV-aware tool that understands quoting, and keep the original file as a reference.
Frequently Asked Questions
Can Excel open a CSV with more than one million rows?
It cannot display more than 1,048,576 rows in one worksheet. You may be able to import or analyze a larger data set through Power Query and the Data Model, subject to available resources, but that does not put every row into a worksheet.
Does converting a CSV to XLSX increase the row limit?
No. XLSX is an Excel workbook format, but each worksheet still has the same row and column limits. Converting the file can add workbook features; it cannot fit extra rows into one sheet.
Why did Excel remove the zeros from my CSV?
Excel likely interpreted a code as a number. Import the relevant column as Text in Power Query rather than relying on automatic type detection. The CSV itself may still contain the zeros, so check the original file before making corrections.
Can I open a large CSV without loading all its rows?
Yes. Use Power Query to filter rows or remove unneeded columns before loading the result to a worksheet. For a one-off task, another data tool can also extract a smaller subset. Keep the unfiltered original if you may need the full data later.
Is a large CSV file unsafe to open?
Large size alone does not mean a file is unsafe, but treat files from unknown sources cautiously. Spreadsheet software may interpret formula-like values in CSV content, so do not casually open or enable content in an untrusted export. For sensitive data, import it deliberately and verify the source and contents.
Related
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
- Conditional Formatting Excel Dates Before a Date Rule
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 Insert a Specific Number of Rows in Excel QuicklyTo insert a specific number of rows in Excel for Microsoft 365 on Windows, select the same number…
- How to Make a Budget Tracker in Google SheetsMake a monthly budget tracker in Google Sheets by listing expense categories, entering planned and actual amounts, and…
- Excel Insert X Rows: Fast Methods for Any WorksheetTo insert X rows in Excel, select X row headers where you want the new rows, then choose…
- Change Who Can View an Outlook CalendarTo change who can view an Outlook calendar, open its sharing or permissions settings, select the person or…
- Data Validation Checklist for Excel: What to CheckA practical data validation checklist starts by defining what each field may contain, then applying the right Excel…
- Company Invoice Template in Excel: Setup and TipsYes. You can make a reusable company invoice template in Excel by laying out your business and customer…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.