excel file huge


Board Regular
Mar 3, 2004
I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open? There is 3 tabs and very little data. How can it be so huge??

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
There could be many reasons for the size of the file.

Are there a lot of formulae/formatting?

Is there any VBA code?

Have you tried copying the data into a new workbook?
Upvote 0
This is a very basic excel book that has no formulas. I did notice in one tab in print preview that it showed 1200 pages all of which were blank. How can I fix that?
Upvote 0
i've had the exact same problem before.....

possible solutions:

-make sure you go through every sheet.....the author has most likely colored all of the sheets white so that the user would not see un-used cells....that takes massive amount of memory.....undo that by making them "no color" instead of white

another thing that you can do once you try that, would be to copy your sheets into a fresh Excel file. make sure to do it in the exact same order, and be sure to take your macros with you.

i had a file that went from 12 MB to 200 KB within a matter of minutes after i did the above two.

Best of luck,

Upvote 0
those blank sheets ARE your problem.
they have been colored White....if there is no data, delete those sheets, and if there is data, un-color them :)
Upvote 0
You can run this, courtesy of DJR, and see f it slims down that wb.
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ExcelDiet()
    <SPAN style="color:#00007F">Dim</SPAN> x           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastCol     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> Sheets.Count
        LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
        Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
    <SPAN style="color:#00007F">Next</SPAN> x
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Upvote 0
that is an impressive piece of code you posted there!

it reduced my file size from almost 7 MB to 2! :)
it made everything faster! thank you!

could you by any chance explain what it does?
Upvote 0
The code lookp thru each worksheet, finding the boundaries of used ranges, and then deletes everything outside that.

It might sound stupid to delete already empty rows/columns, as they just come back, but the thing of it is that Excel sometimes remembers that you once formated a whole row or column and considers that as an active par of your book, to be saved.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back