How to reduce a huge excel file

Question

I have a small and simple file in *.XLS with only one sheet, on this sheet just many cells with small text on number. (file size 24Kb)

But I made a lot of changes, copy and paste, extend formula, save... afterwards I deleted most of these changes and make 4 duplicates of this sheet with few data.

Now my new file is VERY huge : 2.5Mb !

Where is the hidden data and how can I delete it ?

I have the same problem on real file with 300 sheets and 1 picture on each sheet : file size 280Mb

Answer 1

If your file is just text, the best solution is to save each worksheet as .csv and then reimport it into excel - it takes a bit more work, but I reduced a 20MB file to 43KB.

Answer 2

I have worked extensively in Excel and have found the following 3 points very useful

Find if there are cells which apparently do not hold any data but Excel considers them to have data

You can find this by using the following property on a sheet

ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count

If this range is more than the cells on which you have data, delete the rest of the rows/columns

You will be surprised to see the amount of space it can free

Convert files to .xlsb format

XLSM format is to make Excel compliant with Open XML, but there are very few instances when we actually use the XML format of Excel. This reduces size by almost 50% if not more

Optimum way of storing information

For example if you have to save the stock price for around 10 years, and you need to save Open, High, Low, Close for a stock, this would result in (252*10) * (4) cells being used

Instead, of using separate columns for Open,High,Low,Close save them in a single column with a field separator Open:High:Low:Close

You can easily write a function to extract info from the single column whenever you want to, but it will free up almost 2/3rd space that you are currently taking up