How to reduce excel file size with large data sheet

[questforexcel]

Hi All,

I have my main excel file which has about 7-10MB of raw data. When I draw pivots and add formulas to the cells, my file size increases to 22MB.

This makes the file bulky. Is there any way I could reduce the file size as much as possible.

I have tried saving the file type as binary version. That did not help.

THanks,

 

[jkpieterse]

You could set the pivots to not save their source data with the file.

 

[Logit]

Run this on a COPY OF YOUR EXISTING WORKBOOK. That way if something goes awry you won't lose anything.

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

 

[questforexcel]

Run this on a COPY OF YOUR EXISTING WORKBOOK. That way if something goes awry you won't lose anything.

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

Thanks for sharing. I tried doing this, it only reduced the file size by 100kb. I am missing something? Thanks

 

[Logit]

No ... nothing missing. The macro has done the best it could with the existing data. Something else is creating the bloat but I don't have anything else to offer
you. Sorry.

 

[jkpieterse]

Try my suggestion, it will certainly save on the size

 

[questforexcel]

You could set the pivots to not save their source data with the file.

Try my suggestion, it will certainly save on the size

Thanks I was trying your suggestion. Just wasnt sure how to apply it. Could you guide me through it.

I have multiple pivots on different sheets of my file. How should I go about not saving their source data. While just want to note that source data is also on the same file.

Thanks,

 

[questforexcel]

No ... nothing missing. The macro has done the best it could with the existing data. Something else is creating the bloat but I don't have anything else to offer
you. Sorry.

No worries at all, appreciate you trying to help

 

[jkpieterse]

Select each pivot table in turn and click options. On one of the tabs they're is a checkbox that sets whether the pivot data is saved