Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Someone send me an Excel file that's 10MB, and thus causes us email problems. When .zip'ed it goes to under 1MB, so presumably there's something wrong with the Excel sheet but is there any way to compress this somehow?

I have a range of versions of Excel available to me by the way.

share|improve this question
I'm confused. " When .zip'ed it..." with "... is there any way to compress this somehow?". It seems that you have answered your own question. – EBGreen Apr 25 '12 at 14:58
You're right, I could have been clearer sorry, I mean 'actually make the .xls file smaller' sorry - as opposed to just using winzip/7zip 'externally' – Chopper3 Apr 25 '12 at 15:02 far as I know there is no built in compression. I am prepared to be wrong however. – EBGreen Apr 25 '12 at 15:06
Is there really that much data in the workbook, or is this a case of the million empty rows problem? – Excellll Apr 25 '12 at 15:34
@Excellll very much the latter I believe, it's not my excel sheet and its 'owner' is a bit clueless. – Chopper3 Apr 25 '12 at 15:36
up vote 6 down vote accepted

If there is space that can be 'reclaimed', there are several different things you can try:

  1. Reset all the Used Ranges in each workbook. This can be done using a VBA method like this:

    Public Sub ResetAllLastCells()

    Dim wks As Worksheet
    Dim i As Long
    For Each wks In ActiveWorkbook.Worksheets
        i = wks.UsedRange.Rows.Count
    Next wks

    End Sub

  2. Remove any unwanted custom styles. I use a VBA method as below to remove all custom styles:

    Public Sub StyleKill()

    Dim s As Style
    For Each s In ActiveWorkbook.Styles
        If Not s.BuiltIn Then
        End If
    Next s

    End Sub

  3. If there is VBA code within the workbook, try saving it decomiled. You can do this by opening the workbook, open the VBE, make a small change (add white space or something) and then, without compiling the project, save the workbook. This can dramatically reduce the file size.

I'm sure there are other ways too but these are my tried and tested ways. In one workbook I have this consistently reduces the file size from ~15mb to 10mb.

share|improve this answer
Thank you very much indeed, I'll give it a try. – Chopper3 Apr 25 '12 at 15:51
By the way, this did help a little - think we might have to do the whole 'transfer everything to a new file' thing I guess, cheers anyway. – Chopper3 Apr 26 '12 at 10:48

In my experience, the most common reason for this is blank lines that are within the used range within excel. Delete any blank rows below the range you have actually used (right click on the left and delete the whole rows, do not just clear contents), if that fails to reduce the size, use a macro like the following:

Sub DeleteBlankRows()

Dim i As Long

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False

    For i = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
        End If
    Next i

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.