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

Look at posts like: http://www.officearticles.com/excel/clean_up_your_worksheet_in_microsoft_excel.htm or http://www.contextures.on.ca/xlfaqApp.html#Unused

Basically: try Googling?

Answer 2

I had an excel file 24MB in Size, thanks to over a 100 images within. I reduced the size to less than 5MB by the following steps:

1. Selected each Picture, cut it (CTRL X) and pasted it in special mode by ALT E S Bitmap option
2. To find which Bitmap was still large, One has to select one of the files per sheet, then do CTRL A. This will select all Images.
3. Double Click on any one image and the RESET Picture option appears on top.
4. Click on reset picture and all Images that are still large show up.
5. Do a CTRL Z (UNDO) and now again paste these balance images in BITMAP (*.BMP) like step 1.

It took me 2 days to figure this out as this wasnt listed in any help forum. Hope this response helps someone

BR Gautam Dalal (India)

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

I stumbled upon an interesting reason for a gigantic .xlsx file. Original workbook had 20 sheets or so, was 20 MB I made a new workbook with 1 of the sheets, so it would be more manageable: still 11.5 MB Imagine my surprise to find that the single sheet in the new workbook had 1,041,776 (count 'em!) blank rows. Now it's 13.5 KB