S ometimes this doesn’t fix the problem, and you still find yourself well below your data. When you’ve done this, then push + again and see where you end up – hopefully at the bottom right corner of your data. In fact, this is often why the used range is wrong…it still reflects some data that used to be in the sheet, but that the user subsequently deleted using the keyboard. Pushing that Delete key does not reset the used range. Note that you’ve got to use the Right-Click>DELETE option, NOT the Delete key on the keyboard. To do this, select the entire row immediately below your data, then press + to extend the selection right to the bottom of the sheet, then right click and select Delete: Often you can reset the Used Range simply by selecting all the the empty rows under your data, and then deleting them. After we reset the used range, the filesize plummeted from 35MB to around 2MB. This is exactly what had happened in the case of the spreadsheet concerned. If the used range includes millions of cells that aren’t even used, then the information that Excel saves regarding these cells can really blow out the file size. Why? Because when Excel saves a file, it includes information about things such as what type of Cell Formatting is used within the used range. Maybe all the way to the very bottom of the grid:
Hopefully it will take you to the bottom-most, right-most cell that you’ve actually used in the sheet:īut occasionally, you’ll see that it might take you far, far below that cell.
You can find out what this is for each spreadsheet by pushing +, and seeing what cell this takes you to. The Conf used rangeįirst, there was a problem with the Used Range – the area within a worksheet that Excel thinks contains all your workings and data. It turns out there were two problems with her files that were easy to resolve. This analyst had a file with only 6000 rows of data in it, but the file size was something like 35MB, and after each and every change she had to wait at least a minute for the file to recalculate before she could do something else. This prompted one of the participants to come to me for advise regarding restructuring a spreadsheet with that very problem. I recently gave a presentation on Excel efficiency to a bunch of analysts, in which – among other things – I’d pointed out that if you ever find yourself having to switch calculation to Manual, there’s probably something wrong with your spreadsheet.