How to Fix Excel “File Not Loaded Completely” Error

Opening large data exports, server logs, or transaction archives in Microsoft Excel can halt abruptly with a stark warning dialogue stating: “File not loaded completely.” In other versions of Excel, this appears as: “The maximum number of rows has been exceeded. Excess rows will not be loaded.” When you click OK, Excel opens the file, but any records beyond row 1,048,576 are irrevocably cut off and missing from your worksheet. For data analysts, accountants, and marketing specialists working with big datasets, losing millions of critical data rows is unacceptable. Fortunately, your source file has not been ruined; this warning occurs because Excel enforces a hard architectural boundary of 1,048,576 rows per worksheet. Resolving this constraint requires leveraging Excel’s modern data modelling engines or splitting data pipelines.

Understand Excel’s Hard Grid Limits

Since the introduction of the .xlsx XML format in Excel 2007, every individual worksheet has an unchangeable maximum limit of:

  • 1,048,576 rows by 16,384 columns.

Regardless of how powerful your computer processor is or how much RAM you have installed, double-clicking a CSV or TXT file that contains 1.5 million or 2 million rows will always trigger the “File not loaded completely” error in standard worksheet grids. To inspect and analyse datasets that exceed 1,048,576 rows, you must use Power Query and the Excel Data Model.

Import the Complete Dataset via Power Query (Bypass the Row Limit)

Excel’s built-in Power Query engine does not suffer from the 1,048,576 row worksheet limitation. Power Query can ingest, clean, and model datasets containing tens of millions of rows by loading them into the in-memory Data Model (Power Pivot) rather than spilling onto a standard worksheet grid.

  1. Open a fresh, blank workbook in Microsoft Excel.
  2. Click on the Data tab on the Excel ribbon.
  3. In the Get & Transform Data group on the far left, click From Text/CSV (or choose Get Data > From File > From Text/CSV).
  4. Select your large CSV or text file and click Import.
  5. A data preview window will appear. At the bottom-right corner, do not click Load. Instead, click the drop-down arrow next to Load and choose Load To…
  6. In the Import Data dialogue box that appears:
    • Select the radio button for Only Create Connection.
    • Tick the vital checkbox labelled Add this data to the Data Model.
  7. Click OK.

Power Query will load every single row from your multi-million-row file into Excel’s high-speed internal database without truncating a single record.

Analyse Massive Datasets Using Pivot Tables

Once your complete dataset is safely loaded into the Data Model via Power Query, you can summarise, filter, and analyse all millions of records seamlessly.

  1. Click on the Insert tab on the Excel ribbon.
  2. Click on PivotTable, then select From Data Model.
  3. Choose where you want the Pivot Table placed (e.g., New Worksheet) and click OK.
  4. In the PivotTable Fields pane on the right, drag your fields into Rows, Columns, and Values.

Excel’s Data Model evaluates aggregate sums, counts, and averages across the entire dataset instantaneously, allowing complete data exploration without worksheet row limits.

Split the Oversized File into Multiple Sheets

If you genuinely require the raw data rows visible on regular worksheets for manual auditing, you can use Power Query to split the file across two separate sheets.

  1. In the Power Query preview window, click Transform Data.
  2. Use the Keep Rows / Keep Range of Rows command to keep rows 1 to 1,000,000 for Query 1.
  3. Duplicate the query, and set the second query to keep rows 1,000,001 to 2,000,000.
  4. Load Query 1 onto Sheet 1, and Load Query 2 onto Sheet 2.

Segmenting records across multiple sheets allows full tabular visibility while respecting Excel’s worksheet grid limits.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.