Greater control over 'used range'
Too often does excel blow up spreadsheets' size by extending the used range of the sheet. Sometimes deleting rows/columns and then saving doesn't always work and you are then left with a file that is too big and too ungainly. A simple option to manually override the default used range would quickly resolve this annoying, but common, problem.
Ryan MacGregor commented
Until this is addressed in Excel, you'll need a third party solution to reset the Used Range. There are several tools that do this. Some are free (see https://support.microsoft.com/en-us/kb/244435) and some are part of larger, premium solutions (see https://macabacus.com/docs/excel/file-operations#clean-used-ranges). Since this has been an issue since at least Excel 2003, I wouldn't hold my breath for a solution from MSFT.
Patrice F. commented
The thing is that CTRL + End will never find formatted columns (e.g. conditional formated cells) and cells with drop down lists (data validation) and any other heavy format. This is one of the reasons why files get huge sizes increase.
I would like to have some assistant to help sort such problems
The file is huge because the used range is huge. The used range is basically the chunk of your sheet that has any data or formatting. You can press Ctrl + End to find the end of your used range.
Patrice F. commented
I had Excel files going from 300KB to 35MB without knowing where the problem was. I was eventually able to fix the problems by using the CLEAR ALL option, but it was not straight forward.
We also shouldn't have to save to reset the used range. It should be dynamic.
XLT and XLS are bad for growing when the used range grows. Just make sure that the other users are using the newer formats (XLTX, XLTM, XLSX, XLSM, XLSB) and you should be fine.
Several times I have created a template spreadsheet of minimal file size which, when populated by other users, becomes enormous (one example jumped from 5MB to 104MB, making it virtually impossible to work with).
On investigation this appears to be caused by user formatting - for example where someone highlights an entire row rather than just the few columns containing data, or where they don't know about the gridlines tickbox and so have applied borders to every cell in a worksheet.
I don't know about programming, but intuitively there is no reason why it takes more space to store "highlight row 14 yellow" than "highlight cell C14 yellow".
[We are using Excel 2010. Apologies if this issue has already been solved in a later version - a quick Google search suggests not.]
Google Sheets has a somewhat good approach to this. All of the cells that you see are your used range. However, the pain is that you need to add more rows and columns manually.
This isn't just an issue with filesize, either.
As excel files are used as data sources, the "last cell" of the file can trick many other software packages into thinking there are 1.04M records (Including SQL Server!)