How to reduce a huge excel file

问题:

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

回答1:

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.

回答2:

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

回答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.

回答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

回答5:

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

回答6:

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)

回答7:

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?

回答8:

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