What to do if a data set is too large for the Excel grid (2024)

If you’ve opened a file with a large data set in Excel, such as a delimited text (.txt) or comma separated (.csv) file, you might have seen the warning message, "This data set is too large for the Excel grid. If you save this workbook, you'll lose data that wasn't loaded."This means the dataset exceeds the number of rows or columns that's available in Excel, sosome data wasn’t loaded.

It's important to take extra precautions to avoid losing any data:

• Open the file in Excel for PC using Get Data- If you have the Excel app for PC, you can use Power Query to load the complete data set and analyze it with PivotTables.

• Don't save the file in Excel - If you save over the original file, you'll lose any data that wasn't loaded. Remember that this is also an incomplete data set.

• Save a truncated copy - If you need to save the file, go to File > Save a Copy. Then enter a different name that's clear that this is a truncated copy of the original file.

How to open a data set that exceeds Excel's grid limits

Using Excel for PC means you can import the file using Get Data to load all the data. While the data still won't display more than the number of rows and columns in Excel, the complete data set isthere and you cananalyze it without losing data.

1. Open a blank workbook in Excel.

2. On the Data tab selectFrom Text/CSVin the Get & Transform Data group

3. Find the file and select Import.

4. In the preview dialog box, select the Load Toand choosePivotTable Report.
   If you don't see the Load To button, select the down arrow by the Load button first.

5. Once loaded, Usethe Field List to arrange fields in a PivotTable. The PivotTable will work with your entire data set to summarize your data.

   See Also

   How can you analyze 1M+ rows in Excel? - Earn & ExcelAre there data limits in a CSV file?How can I store more than 1 million rows in excel sheet.one million rows in a google sheet.

6. You can also Sort data in a PivotTableor Filter data in a PivotTable.

More about the limits of Excel file formats

When using Excel, it’s important to note which file format you’re using. The .xls file format has a limit of 65,536 rows in each sheet, while the .xlsx file format has a limit of 1,048,576 rows per sheet. For more info, see File formats that are supported in Excel and Excel specifications and limits.

To help prevent reaching an Excel limit, make sure you’re using the .xlsx format instead of the .xls format to take advantage of the much larger limit. If you know your data set exceeds the .xlsx limit, use alternative workarounds to open and view all data.

Tip:Be sure to cross-check that all data was imported when you open a data set in Excel. You can check the number of rows or columns in the source file and then confirm it matches in Excel. Do this by selecting an entire row or column and viewing the count in the status bar at the bottom of Excel.

Related articles

Save a workbook to text or CSV

Import or export text (.txt or .csv) files

Import data from external data sources (Power Query)