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, so some 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 is there and you can analyze it without losing data.
-
Open a blank workbook in Excel.
-
On the Data tab select From Text/CSV in the Get & Transform Data group
-
Find the file and select Import.
-
In the preview dialog box, select the Load To and choose PivotTable Report.
If you don't see the Load To button, select the down arrow by the Load button first. -
Once loaded, Use the Field List to arrange fields in a PivotTable. The PivotTable will work with your entire data set to summarize your data.
-
You can also Sort data in a PivotTable or 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
