Excel file size too large

I have an Excel file I inherited from someone else. Its size is strangely large, with only two tabs
of few data and an empty tab. I deleted the two tabs with contents and only keep the empty
tab, then saved it. It is still 66MB in size. I checked the VBA code, and even deleted all the VBA
code. Still 66MB. How can it be?

Anyone has any idea?

Thanks.

Jennifer
jfz2004Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Seth_zinCommented:
Can you try and copy it to a new excel and see if you have the same issue?
0
Rob HensonFinance AnalystCommented:
On the empty tab press "Ctrl + End" or "End then Home". Where does the cursor go?

If the tab is truly empty the cursor will stay at A1 otherwise it will go elsewhere. I suspect it will go way off to the right and down some. If so the tab has at some point had data, possibly lots of, and has been deleted but the Used Range has not reset itself. To reset the Used Range, use Shift and the above key combinations to select the Current Used Range and then Delete Rows and Columns. Save the file and then repeat the key combination and cursor should stay at the top left.

If that's not the case, then right click on the one tab and see if the Unhide option is greyed out. If not greyed out that means there are other tabs but they are hidden. Unhide these and see if there is lots of data on them.

Next option, press Alt + F11 to show the Visual Basic Editor. In the left hand pane there should be a list of open files. Find the file and expand the structure if not already expanded. This will list all of the sheets in the file; if there is more than just the one sheet listed and you weren't able to Unhide using previous option, click on the sheet and in the pane below there is a list of properties. At the bottom there is Visibility, one is Option in the list is Very Hidden; with this option, the sheet won't show in the Unhide list.

Thanks
Rob H
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
There could be hidden tabs.

Try right-hand clicking on the bottom-left hand corner, on the arrows next to your empty tab name.

If you get the option "Unhide", then they are there.

Go to File - Info to see if the workbook is protected.

Alternatively, try saving it as a different format. e.g. xlsb instead of xlsx or vice versa.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jfz2004Author Commented:
Thanks, Rob,

 On the empty tab, I tried "Ctrl + End" and "End then Home". The cursor went to A1.

Thanks Seth,

I tried and copied it to a new excel and I got the same issue.

Thanks, Phillip,

I tried to save it as xlsb file, the size is now 55MB, 11Mb less than as .xlsm.
0
jfz2004Author Commented:
I also tried press Alt + F11 to show the Visual Basic Editor. In the left hand pane there is only one workbook and sheet1.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What about hidden tabs?

Can you go to VBA (press Ctrl+F11) and the immediate windows (Ctrl+G), and enter

for each sht in sheets():debug.print sht.name:next
0
jfz2004Author Commented:
Afteralt+F11 and Ctrl+G, I went to immediate and got this:

for each sht in sheets():debug.Print sht.name:next
Macro1
Sheet1
0
Rob HensonFinance AnalystCommented:
So, no excess firmatting and hidden or very hidden sheets.

Are all rows and columns visible?

To check, click the margin cell above the row numbers and left of column headers. Then use the Format button to ensure everything is visible.

Then on  that sheet press F5 and click the Special... Button at the bottom. Select Objects and click OK. If there are no objects, error message will come up saying so. If there are objects they will be selected and can be deleted.
0
jfz2004Author Commented:
I did the F5 and Select Objects and click OK. There are no objects. It is so puzzling and frustrating.... Hmmm....
0
jfz2004Author Commented:
I added a new tab and deleted the old sheet1. Then saved it (so slow in saving). It is still 61.6MB as before.
What can it be?! I am pulling my hairs.
0
jfz2004Author Commented:
I just created an Excel and it is only 25KB. Wonder what is going on with that huge file.
0
Glenn RayExcel VBA DeveloperCommented:
Hi,

Look at the cell styles (Menu: Home, Styles, Cell Styles) and see if there are lots of unusually-named styles (more than just "Normal").  Also look in the range names (Menu: Formulas, Name Manager) and see if there are many range names, especially those with #REF! errors.

If either of the above is true, it's symptomatic of layers of older Excel files/data being pasted into this document and not getting cleaned up.  If the file is Excel 2007 or higher, this can be resolved either by editing the XML data directly or by using a third-party application like XLStylesTool.

You may also want to check to see if there are any "null" drawing objects such as empty text boxes or perhaps resized and embedded picture objects (Menu: Home, Editing, Find & Select, Selection Pane).

Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seth_zinCommented:
Can you check to see if there are a bunch of formuals and/or rules?
0
jfz2004Author Commented:
No. I deleted all the tabs but the newest one which has no data.

I just found the original version of the file and its size is only 40KB with exactly
the same infomation.

But how strange that its size is now 61MB after I put it on a shared network drive over a month ago, it becomes so huge!
0
Rob HensonFinance AnalystCommented:
That last comment has lit a light, possibly!

As well as being on s Shared network drive, does the file have the Shared Workbook feature enabled?

Shared workbooks are notoriously unreliable and can become corrupted, one symptom of which can be file size bloat.
0
Glenn RayExcel VBA DeveloperCommented:
I agree that a shared file might be an issue, but in the "Request for Attention" post (which really shouldn't have been made), the author states that the file is not in shared.  This is all different than being on a shared, network drive.  That should have no effect on the file size.

So, which is it?
0
jfz2004Author Commented:
Before Jan 20th, it was on C:\ drive; then I made a copy of it and moved it on shared drive with a new name; yesterday I copied it on C:\ drive. I am sorry it is going back and forth.

The original copy was only 37KB. But I discovered that the one on shared drive and copied back is 66MB. They have the same contents.

But I check the new one and found that the shared workbook feature is not turned on.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Have you tried this add-in that performs intelligent reduction (not compression) of Office files?

http://www.neuxpower.com/
0
jfz2004Author Commented:
I will try it. Thank you!
0
Glenn RayExcel VBA DeveloperCommented:
Before you purchase a third-party application (which appears to focus more on image/pdf file optimization), have you noticed a similar effect with any other Excel files saved on the shared drive?  For example, take a copy of any other Excel file you have, note its size, and save that copy on the shared drive.  Then, copy it back to your computer (C:) and note the size.

If the new file doesn't change sizes, then there is an issue with the first file only.  If the new file does change sizes, I'd recommend that a virus scan be run on on the file server.

-Glenn
0
jfz2004Author Commented:
Thanks a lot, Glenn.

At this stage, since I have the original Excel file in small size (40KB). I am writing VBA code based on it. I still don't know why the one that went through the network is 61MB and it puzzles me deeply. I tried everything described above to no avail.

I will still poke at it everyday whenever I can get a moment at it, just to satisfy my curiosity.

Thank you to everyone who helped!

Jennifer
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.