Excel for Microsoft 365Excel for Microsoft 365 for MacExcel for the webExcel 2021Excel 2019Excel 2016 More...Less
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.
Open a blank workbook in Excel.
On the Data tab selectFrom Text/CSVin the Get & Transform Data group
Find the file and select Import.
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.
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.
One of the best ways to work with large datasets in Excel is to convert them into tables, which are dynamic and structured ranges of data that offer many benefits, such as easy formatting, sorting, filtering, and referencing.
That's where filters come in. Filters allow you to pare down your data to only look at certain rows at one time. In Excel, a filter can be added to each column in your data — and from there, you can then choose which cells you want to view at once.
Select View > More.In the Ruler & Grid box, select Fine, Normal, or Coarse for Grid spacing Horizontal and Grid spacing Vertical. Note: Fine is the smallest grid spacing, and Coarse is the largest. Select OK.
Reduction of Dataset Size. There are two methods for reducing dataset size: packing and compression. By packing we mean altering the data in a way that reduces its precision. By compression we mean techniques that store the data more efficiently and result in no precision loss.
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible. Click the Select All button. To select the entire worksheet, you can also press CTRL+A.
If you have worksheets with data that you're not using—and that don't contain any formulas you're using—delete them from the spreadsheet. The more data points you have in your workbook, the larger your file size will be. Removing unused data will reduce your file size.
Hobby: Web surfing, Skiing, role-playing games, Sketching, Polo, Sewing, Genealogy
Introduction: My name is Maia Crooks Jr, I am a homely, joyous, shiny, successful, hilarious, thoughtful, joyous person who loves writing and wants to share my knowledge and understanding with you.
We notice you're using an ad blocker
Without advertising income, we can't keep making this site awesome for you.
