Excel file size mysteriously large

Mzzh

New Member
Joined
Mar 10, 2023
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hi XL Gurus

I’m having a size issue with an excel file lately. The file is about ~120MB with lots of data so I’ve been trying to find ways on reducing its size. Most recently I’ve decided to do a test by extracting a random worksheet (let’s call it “ws” into a new file. I then proceeded to clear all cell contents and formats, delete all rows and columns etc. The blank file size however remains around ~200kb. There are no external links, connections/queries, vba codes, defined name, hidden/very hidden worksheets or anything like that. I even added a new worksheet and deleted ws then re-saved the file but still it remained at around 200ish kb in size. Does anyone have an idea what’s going on??
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You are probably doing vlookups on a large number of row (or multiple vlookups) or using other volatile functions like COUNTIFs, INDIRECT, RANDBETWEEN etc. These recalculate everytime you try and do something.

Lots of conditional formatting can slow your sheet too, stuff like highlighting duplicates, especially over a large number of rows

Maybe copy and paste any formulas as values if you can and that should speed it up and reduce file size
 
Upvote 0
You are probably doing vlookups on a large number of row (or multiple vlookups) or using other volatile functions like COUNTIFs, INDIRECT, RANDBETWEEN etc. These recalculate everytime you try and do something.

Lots of conditional formatting can slow your sheet too, stuff like highlighting duplicates, especially over a large number of rows

Maybe copy and paste any formulas as values if you can and that should speed it up and reduce file size
Hi Denzo

Yes the original file has a lot of indirect formulas and over 250 worksheets but that shouldn’t affect the single worksheet that is moved out into a new workbook? The sheet that was being moved out was even subsequently deleted from that new empty workbook and re-saved but alas the workbook file size remained unusually large. It seemed like that exported worksheet somehow “infected” an entirely workbook that has absolutely nothing. No queries/connections/conditional formatting/named ranges whatsoever. How is it possible??
 
Upvote 0
Are there any Hidden Workbooks that open with XLSTART?
 
Upvote 0
I'll throw in a few possibilities: a Corrupt file; Hidden used range on sheets. XL has a "memory" of all those cells that were once used and now forgotten which can make for strange memory things; Named ranges. Transfering sheets with named ranges seem to create trouble. You may be able to fix the problem by using a collection to transfer the sheet to a new wb. Finally, you could trial adding the sheet to a new file and then Zip and UnZip the file. HTH. Dave
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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
Back
Top