Excel File Size Question - Why is my file 36Mb with minimal Data in it? - Excel 2016

[Johnny Thunder]

Hello All,

I am so lost right now. Here is my dilemma, I have a file (saved as .xlsm), it has 6 spreadsheets in it.

1 - "Master Data" Sheet has all the data from the file, 44 rows of data, 15 columns wide.
1 - "Slot Analysis" Sheet that has 4 small grids of data that interact with the "Master Data" that fill in some logistics as to what kind of data resides in the Master Data via SumIFS, CountIFS.
4 - Category sheets that break down the Master Data into individual Sheets, my VBA creates two Columns x 33 rows of pickable details that can be dragged and dropped into a grid - These sheets seem to be the source of why the file is so large, for each one of these sheets added I get 9MB added to the file, I have 4 hence the 36MB.

The sheets themselves don't do much, they get 33 "Tile" Like Cells filled in, the user can drag the tile from Range J:L to Ranges N:Q and thats pretty much it. When the "Tiles" are dragged and dropped the Master Data Sheet has 2 Columns of Lookups that define where the user is dropping the "Tiles".

The sheet is formatted in All black, but I only have the Black from Cells A1:AA60 the rest of the non-visible sheet is left unformatted. The "Tiles" are formatted in a Light Grey with Black Text.

I call the Cells with Data "Tiles" because they are formatted like this;

TitleName | S1
Genre: GenreName
Available Date: Date
Y/N

All in one cell, I am using the Paragraph feature to make the text wrap in one cell and then I just increase the row height to 60. I am so confused with why this is causing such a huge MB strain on the file?

I have calculations turned off, changes nothing. I removed the Black background, changes nothing. I delete one of these formatted sheets and then the file size reduces by 9MB.

Any insight or help would be appreciated. Thanks!

 

[mole999]

ASAP utilities (freeware unless you want to buy it) will aim to recover over selected and stored ranges, you can get rid of unused styles, and you can save a copy as Xlsb. the binary file is smaller

 

[mumps]

Something else you can consider ...…. Do the following: on each sheet, place the cursor in cell A1 then hold down the CTRL key and press the END key. This will take you to the last recognized cell in the sheet. If there are empty rows above and empty columns to the left of that last cell, you can delete those unnecessary rows and columns from that sheet. Another possible cause may be that you have formulas in many rows after the end of your data which are not returning any values and can be deleted. If you delete any rows and columns, make sure you save the file immediately after the deletion. Close the file and re-open it and those extra rows and columns should be gone making the file smaller.

 

[Johnny Thunder]

I may be reading your post incorrectly but it sounds like you are proposing two different solutions?

1. The ASAP Utility will clean up the file or sheet?
2. Resaving the file and changing the format to .xlsb should decrease the file size? - Is there any limitations to doing this as opposed to the .xslm format? Never used it before.

ASAP utilities (freeware unless you want to buy it) will aim to recover over selected and stored ranges, you can get rid of unused styles, and you can save a copy as Xlsb. the binary file is smaller

 

[Johnny Thunder]

So, this was step one a few days ago. I confirmed that there are no rogue cells with any text or formatting outside of my used range.

Also, there is no formulas on the entire sheet. Just the cells (Plain text) that are created VBA. I am so confused why the sheets are so large in size.

Something else you can consider ...…. Do the following: on each sheet, place the cursor in cell A1 then hold down the CTRL key and press the END key. This will take you to the last recognized cell in the sheet. If there are empty rows above and empty columns to the left of that last cell, you can delete those unnecessary rows and columns from that sheet. Another possible cause may be that you have formulas in many rows after the end of your data which are not returning any values and can be deleted. If you delete any rows and columns, make sure you save the file immediately after the deletion. Close the file and re-open it and those extra rows and columns should be gone making the file smaller.

 

[Twollaston]

Sorry I'm a noob, but once I made a macro that kept making my file too big.
The reason was it was copying everything to the last row of the sheet somewhere, instead of a defined range.

One thing I always do to workbooks that are large in file size:
I go to the last column of data -> select the blank column to the right of it -> Ctrl + Shift + Right Arrow Key -> And delete all of them
and then I do the same thing for rows by deleting all the empty rows at the end of the sheet.

After that you can check the size of your sheet and if it's a lot smaller then look through the code for things that might accidentally grab the entire sheet range or row/column range and adjusted it

Sorry if that doesn't help you