Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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 reduce the excel file in size without zipping it?

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
    
Aah...as 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
            s.Delete
        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()

Cells.Select
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
            Selection.Rows(i).EntireRow.Delete
        End If
    Next i

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

The other answers are correct, but use VB scripts. If you don't want to use scripts or if its just for a one off situation, you can also delete the unused space manually:

  • Select any cell inside your content of your worksheet
  • Hit Ctrl+Home on the keyboard to select the last used cell in the bottom right side
  • Scroll back to your "real" last cell on the bottom right of your content
  • Hold down Shift and click the cell below or right of it, depending on your unused layout
  • Right-click inside the marked area and choose Delete Rows or Delete Columns, again depending on the layout

You may have to repeat those steps if you want to delete rows and columns simultaneously.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .