Select the first blank row (or column).To shrink the used range to the cells you’re using, simply delete the extra blank rows or columns: (Note: to get this result, I simply entered a value in cell K20 then deleted it.) How to Fix the “Used Range” in Excel I’ve got data that goes until column F and row 11, so I should expect that the used range goes until cell F11 (even if nothing is in that specific cell).īut when I press Ctrl + End, this happens: If that shortcut takes you many rows (or columns) past the end of your data, it means that all of those cells are increasing the file size for no reason. If you press Ctrl + End on any sheet in your Excel file, you can see what the “last used cell” is. But as you work on a file, it will increase to represent the rightmost column and furthest row that you’ve edited or formatted.Įspecially in older files, even if cells are blank and have no formatting, Excel may be treating them inside the used range, leading to a larger file size for no reason. The larger this is, the bigger the file size becomes.įor new & blank Excel files, the used range is only cell A1. This is definitely the most common source of large Excel file sizes, and fortunately one of the easiest to fix.Įxcel has a “used range” for every sheet in your workbook. If you don’t want to alter the contents of the workbook, try these options first. In this section are the easy and straightforward options to cut down on file size. Bonus option: changing the file extension.

Sort your data when using formulas that look up values Use a Watch Window to always check on specific cells

5. Double check Conditional Formatting ranges.

Check for and delete unnecessary hidden sheets

Excel 2016 very slow to respond