How to Open a CSV File Too Large for Excel Safely

How-to basics · Updated November 20, 2026

To open a CSV that is too large for an Excel worksheet, import it with Power Query instead of double-clicking the file. Filter or reduce the data before loading it, or create a connection and analyze it through Excel’s Data Model. A worksheet can hold 1,048,576 rows and 16,384 columns; if your CSV exceeds those limits, Excel cannot display every row or column on a sheet. Keep the original file unchanged, and use a database or another data tool if you need to work with more data than your computer can process.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, with enough memory and disk space for the file and any temporary data created during import.
  • The CSV file’s location, plus a rough idea of its size and row count. If you do not know the count, check the source system or use a text or data tool that can count records without loading the whole file into Excel.
  • A clear goal: view a sample, filter the records, summarize all of them, or edit the full dataset. The right approach depends on what you need to do.
  • If you need to process data beyond Excel’s worksheet or computer-memory limits, access to a database, Power BI, or another tool designed for larger datasets.

Step-by-Step

  1. Protect and assess the source. Make a working copy if you plan to change or split the file, and leave the original untouched. Check the file size in File Explorer, then consider whether it is large because it has many rows, unusually long text fields, or many columns. A CSV is plain text, so a large file may take much more memory when Excel parses it than its size on disk suggests. Confirm that you have free disk space as well as memory; temporary files can require additional space.
  2. Do not open the CSV by double-clicking it. Excel may try to load it directly into a worksheet. If the CSV has more than 1,048,576 rows, the worksheet cannot contain all of them, and rows beyond the limit will not appear. Opening it directly can also convert values automatically, such as removing leading zeros from identifiers or interpreting date-like text as dates. Start Excel first so you can choose import settings and avoid relying on automatic opening behavior.
  3. Start a Power Query import. In Excel for Microsoft 365 on Windows, open a blank workbook and select Data > Get Data > From File > From Text/CSV. Browse to the CSV and select Import. Excel displays a preview. For very large files, the preview may take time or show only a sample; that does not mean the full file has been imported. Check the detected delimiter and encoding before continuing.
  4. Verify how the file is being read. Check that the columns line up correctly. Commas are not always the delimiter: some files use semicolons or tabs, and text fields can contain commas inside quotation marks. If the preview is wrong, select the correct delimiter in the import window. Check the file origin or encoding too, especially if accented letters or symbols look damaged. UTF-8 is common, but use the encoding specified by the system that created the file when available.
  5. Choose Transform Data for a large import. Select Transform Data to open the Power Query Editor before loading anything onto a sheet. Review the column names and data types. Power Query may assign types automatically, but identifiers, postal codes, product codes, and account numbers are often better kept as text so leading zeros survive. You can select a column and choose an appropriate type from the data type control. Do not change a column to a number if its digits are labels rather than values for arithmetic.
  6. Reduce the data if you do not need every field or record. In Power Query, remove unneeded columns, filter rows, or keep only a relevant date range. These operations reduce what Excel has to load and can make later analysis quicker. You can also remove completely blank rows if they are genuinely not records. Check the query preview after each change and make sure filters have not excluded valid records. Power Query applies steps to the source when the query runs; it does not alter the original CSV.
  7. Choose a destination that fits the dataset. If the cleaned result is below the worksheet row limit and you need to inspect or edit it in cells, choose Home > Close & Load > Close & Load To… in the Power Query Editor and select Table. If it exceeds the worksheet limit, do not load it as a sheet table. In the same dialog, choose Only Create Connection; if you need PivotTable analysis and the option is available, also select Add this data to the Data Model. The Data Model can hold more data than a worksheet, but it is still constrained by available memory and system resources.
  8. Analyze the imported data and verify the result. For a query added to the Data Model, use Insert > PivotTable and select the option to use this workbook’s Data Model when it appears. Build a summary rather than trying to display every record at once. Refreshing a query rereads the source, so use Data > Refresh All only when you are ready for Excel to process the file again. Compare row counts or totals with the source system where possible, and save the workbook as an Excel workbook so the query and its settings are retained.

If the file has more than 1,048,576 rows and you need to see each row in a grid, Excel cannot show the complete file in one worksheet. You can split the CSV into smaller files only if the split is performed by a CSV-aware tool that respects quoted fields and record boundaries. Each resulting file needs its own header row if you intend to import the pieces separately. For analysis across all records, a database or Power BI is usually a better choice than dividing the data just to fit Excel.

Common Mistakes

Assuming a CSV has no size limits because it is not an .xlsx file. CSV is a text format, but when Excel opens it, the records still need to fit within Excel’s worksheet limits if they are loaded onto a sheet. The file extension does not remove those limits.

Confusing a preview with a complete import. Power Query previews a portion of the data to help you inspect it. A preview that looks correct is not proof that all records were loaded. Confirm the load destination and check a record count or a known total after the query finishes.

Letting automatic type detection alter important values. A code such as 004271 may become 4271 if Excel treats it as a number. Dates and long digit strings can also be interpreted unexpectedly. Set identifiers to text during import and compare a few known values with the source.

Using a basic line-splitting tool on a CSV. A quoted field can contain a line break, so splitting after a fixed number of text lines may cut a record in half. Use a program that understands CSV quoting, or avoid splitting and query the complete source with a suitable data tool.

Loading a huge result to a worksheet just to create a summary. A worksheet table is not required for every analysis. A connection and Data Model can be more suitable, provided the computer has enough memory. If the import repeatedly fails, use a database rather than trying the same load again.

Tips

Start by importing only the columns and date range you actually need. Reducing the data before loading is usually more effective than buying time by closing a few other windows. If the CSV comes from a reporting system, see whether it can export a filtered range or a summarized report instead of its entire history.

Keep a note of the source file name, export date, delimiter, and any filters applied in Power Query. That makes it easier to repeat the analysis and understand why a refreshed result may differ. Avoid renaming or moving the source file after setting up the query unless you are prepared to update its source path.

For a large file that is still within the row limit, import it as a query and load to a worksheet only if you need the individual records there. For a file that exceeds the row limit, use the Data Model for summaries if it works within your computer’s resources. If you need to edit, search, or join millions of individual records, use a database or a specialist data tool rather than treating an Excel sheet as a complete data store.

On Excel for Mac, Power Query availability and the names or availability of import options can vary by Microsoft 365 release. Look for the text/CSV import option on the Data tab and use the available query editor and load choices. Excel for the web is not a dependable substitute for desktop Power Query when importing very large files. The worksheet row limit is the same in Excel for Windows and Mac.

Troubleshooting

The import stops, hangs, or reports that there are not enough resources: Close other large workbooks and applications, restart Excel, and retry with fewer columns or a narrower filter. Check free disk space, since Excel may need room for temporary data. If the same file still fails, its working size may exceed what the computer can handle; import it into a database or another tool designed for larger data instead.

The last rows are missing: Check whether the result was loaded to a worksheet. A worksheet cannot show more than 1,048,576 rows, including the header row. Change the destination to a connection or Data Model if suitable, or use a database. Do not assume that scrolling to the bottom of the sheet proves the CSV ended there.

Columns appear merged or shifted: Reopen the import through Data > Get Data > From File > From Text/CSV and choose the correct delimiter. Check quotation handling and encoding. If the source uses a regional separator such as a semicolon, selecting comma will produce a misleading preview.

Characters are garbled: Reimport and select the correct file origin or encoding in the import preview. Ask the source system which encoding it exports if you are unsure. Avoid opening and resaving the file in a basic text editor as a quick fix, because that can change the encoding or formatting.

The imported values differ from the CSV: Inspect the Power Query data types and look for leading zeros, long numbers, dates, and decimal separators. Set identifier columns to text and compare representative records against the source. If the source uses a different decimal or date convention, account for that during import rather than correcting values by eye after loading.

The query refreshes slowly or fails after the file moves: Remove unnecessary query steps or columns, then refresh again. If the source was moved or renamed, edit the source path in the query’s source step. Keep the file in a stable folder if the workbook will be refreshed regularly.

Frequently Asked Questions

What is Excel’s maximum number of rows?
One worksheet can contain 1,048,576 rows and 16,384 columns. A header row uses one of the available rows, so a sheet table can hold at most 1,048,575 data rows when it has one header.

Can Power Query open a CSV with more than a million rows?
Power Query can import and transform large files without placing the complete result in a worksheet, but it is still limited by computer resources and the destination you choose. A connection or Data Model may work for analysis; neither guarantees that a very large file will fit in memory.

Will Excel delete the extra rows if I open the file directly?
Excel cannot display rows beyond the worksheet limit. Do not treat a directly opened CSV as a safe way to preserve or view an oversized source. Keep the original file and import it through a query or use a tool that supports the full dataset.

Can I split the CSV into several smaller files?
Yes, if a CSV-aware tool splits at complete record boundaries and handles quotation marks and embedded line breaks correctly. Add the header to each part if needed for separate imports, and check that the parts together contain the expected records. Avoid splitting by arbitrary byte size or text-line count.

Can Excel for the web open a very large CSV?
It may be able to open some CSV files, but it is not the recommended route for a file that is already too large for Excel. Use Excel desktop Power Query for controlled imports, or use a database or another data platform when the data exceeds Excel’s limits.

Should I save the imported file as CSV or XLSX?
Keep the original CSV as the source. Save the workbook containing the query, Data Model, or worksheet results as an Excel workbook, such as .xlsx, so Excel can preserve workbook features. A CSV cannot store a Power Query connection, PivotTable, multiple sheets, or Excel formatting.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.