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

Solution 1

I wrote a VBA file to add a tool cleaning these abnormally biggest file. This script clear all columns and rows after the last cells realy used to reset the last cells ( [Ctrl]+[End] ), and it also provides enable images compression.

I dev an AddIns with auto install (just run it with macro enabled) to include in context menu many new buttons:

1. Optimize
2. Optimize and Save
3. Disable Optimizer

This is based on KB of Microsoft office 2003 and answer of PP. with personals improvement :

1. add compression of images
2. fix bug for Columns
3. feat compatibility with excel 2007 - 2010 - ... (more than 255 columns)

SOLUTION > you can download my *.xlam file ToolsKit

the main code is

Sub ClearExcessRowsAndColumns()
    Dim ar As Range, r As Double, c As Double, tr As Double, tc As Double
    Dim wksWks As Worksheet, ur As Range, arCount As Integer, i As Integer
    Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean
    Dim shp As Shape
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each wksWks In ActiveWorkbook.Worksheets
      Err.Clear
      'Store worksheet protection settings and unprotect if protected.
      blProtCont = wksWks.ProtectContents
      blProtDO = wksWks.ProtectDrawingObjects
      blProtScen = wksWks.ProtectScenarios
      wksWks.Unprotect ""
      If Err.Number = 1004 Then
         Err.Clear
         MsgBox "'" & wksWks.Name & "' is protected with a password and cannot be checked.", vbInformation
      Else
         Application.StatusBar = "Checking " & wksWks.Name & ", Please Wait..."
         r = 0
         c = 0

         'Determine if the sheet contains both formulas and constants
         Set ur = Union(wksWks.UsedRange.SpecialCells(xlCellTypeConstants), wksWks.UsedRange.SpecialCells(xlCellTypeFormulas))
         'If both fails, try constants only
         If Err.Number = 1004 Then
            Err.Clear
            Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeConstants)
         End If
         'If constants fails then set it to formulas
         If Err.Number = 1004 Then
            Err.Clear
            Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeFormulas)
         End If
         'If there is still an error then the worksheet is empty
         If Err.Number <> 0 Then
            Err.Clear
            If wksWks.UsedRange.Address <> "$A$1" Then
               ur.EntireRow.Delete
            Else
               Set ur = Nothing
            End If
         End If
         'On Error GoTo 0
         If Not ur Is Nothing Then
            arCount = ur.Areas.Count
            'determine the last column and row that contains data or formula
            For Each ar In ur.Areas
               i = i + 1
               tr = ar.Range("A1").Row + ar.Rows.Count - 1
               tc = ar.Range("A1").Column + ar.Columns.Count - 1
               If tc > c Then c = tc
               If tr > r Then r = tr
            Next
            'Determine the area covered by shapes
            'so we don't remove shading behind shapes
            For Each shp In wksWks.Shapes
               tr = shp.BottomRightCell.Row
               tc = shp.BottomRightCell.Column
               If tc > c Then c = tc
               If tr > r Then r = tr
            Next
            Application.StatusBar = "Clearing Excess Cells in " & wksWks.Name & ", Please Wait..."
            Set ur = wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count)
                'Reset row height which can also cause the lastcell to be innacurate
                ur.EntireRow.RowHeight = wksWks.StandardHeight
                ur.Clear

            Set ur = wksWks.Columns(ColLetter(c + 1) & ":" & ColLetter(wksWks.Columns.Count))
                'Reset column width which can also cause the lastcell to be innacurate
                ur.EntireColumn.ColumnWidth = wksWks.StandardWidth
                ur.Clear
         End If
      End If
      'Reset protection.
      wksWks.Protect "", blProtDO, blProtCont, blProtScen
      Err.Clear
    Next
    Application.StatusBar = False
    ' prepare les combinaison de touches pour la validation automatique de la fenetre
    ' Application.SendKeys "%(oe)~{TAB}~"

    ' ouvre la fenetre de compression des images
    Application.CommandBars.ExecuteMso "PicturesCompress"
    Application.ScreenUpdating = True
End Sub


Function ColLetter(ColNumber As Integer) As String
    ColLetter = Left(Cells(1, ColNumber).Address(False, False), Len(Cells(1, ColNumber).Address(False, False)) - 1)
End Function

Solution 2

I save files in .XLSB format to cut size. The XLSB also allows for VBA and macros to stay with the file. I've seen 50 meg files down to less than 10 with the Binary formatting.

Solution 3

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.

Solution 4

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

Solution 5

i Change the format of file to *.XLSX this change compress my file and reduce file size of 15%

View more solutions