Allow an easy way to shrink the sheet to the area with values
Doing various actions in excel - for example, using the bar on top to paste a formula - will often increase the size of the sheet to way beyond what was intended. There should be a button that would shrink the sheet to the largest area with values (or formulas that refer to values) - or alternatively, specify an area to be preserved with the rest eliminated entirely, so that the space on disk is reasonable.
What people do in practice is to copy the sheet area to a new sheet. This is a bit of a kludge.
Thank you for this.
Oh... that all sounds like a lot and complicated, but it's REALLY easy, take you literally 30 seconds if you can easily move to the end of what you want to keep.
You CAN do it by going to the bottom of where you wish the spreadsheet to end, selecting the cell in the NEXT row that is in column A, then pressing Ctrl-Shift-End to highlight (select) the cells from there down to whatever Excel regards as the current end, then pressing Delete. The go to the similar cell in row 1 that is just after the column you wish to end with and doing the same thing. Immediately Save the file, close it, and re-open.
It will now have nothing below or to the right of the cell you desired as its lower right corner. This is what one usually wants with this idea. If you want more, you can do two similar selection and deletions of material to the left of what you want (maybe you want what is now in column C to be column A in the trimmed down spreadsheet) or undesired rows above the material. Just press Ctrl-Shift-Home instead when selecting what to delete.
One can bump the selections up to entire rows or columns when selecting if wished which makes the deletion more straightforward.
However, one thougth to remember: when highlighting what could turn out to be 10,000 rows and 200 columns, what if there is something there? Usually, there's nothing as a common reason for the problem to occur is deletion of no longer needed rows as one works with data, so what's highlighted is usually empty, but... what if?
You can fiogure the regions you want to check by making a range using the cell you selected to start highlighting in as the start of the range to check, pressing Ctrl-End to find what Excel thinks is the end and noting that cell address, making it the end of the range you're looking to check. I'll call it "range" but you'd have like A2343:BS15174. Use the following formula and that range to see if there are any cells with material in them:
If you don't get 0, you'll have to decide how you wish to proceed.
Do that for the two ranges that I first mentioned, the bottommost cells and the righmost cells. Then at least you'll have an easy mind. Or just delete them and take your chances!
In any case, you have to Save, Close, Open the spreadsheet. Save, then go back to work and it might not work completely like you'd like.
To some extent, Excel DOES actually clean some of this up on its own nowadays, unlike 2008, say.
@Wilson - Are you suggesting Excel allow this, or we do this? Because, to my knowledge, there is no way to.
But yes, that would be an excellent solution.
A.C. WILSON commented
Or, delete all empty columns to the right and all empty rows to t the bottom, and then save file. This workaround also reduces the file size.