Why does formatting a specific range (vs entire columns) result in such a big file size difference?

[d0rian]

I've been trying to troubleshoot why my file has crept up in size to 23MB. One of my worksheets I regularly fill with several thousand rows of data (never more than 6,000 rows), and so I formatted each column of cells down to Row 5999. E.g. Range A4 : A5,999 is red font, number to 2-decimals, range B4 : T5,999 is blue, formatted as percentages, etc etc all the way out to column IQ.

I found that when I applied my desired column formatting in this fashion (each time from rows 4 through 5,999), it resulted in nearly a 5MB larger file size compared to when I just apply the desired formatting to the entire column. Why is that? I did some reading, and if anything sources seemed to imply the opposite: that you should only apply formatting to the portion of a range you intend to use. Since my data never fills more than 6,000 rows, I thought I was applying best practices by restricting formatting to that range. I'm clearly missing something about how Excel treats blank cells, entire column ranges, etc....?

 

[rollis13]

I'm not absolutly sure but I think your sheet is partially corrupted. If you have only data (no formulas) I suggest taht the best thing to do is create a new file and paste the data copied from the old file only as Value and then apply your formatting to this new sheet.

 

[d0rian]

The sheet contains plenty of formulas / VBA, etc...

 

[rollis13]

Well, you probably need to Debug your macros.

 

[Michael M]

Do a CTRL + End on each sheet and see what cell the cursor goes to !!
If it goes Waay down each sheet try running this code.
This can also happen if your data is imported from an outside source.

Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

 

[Peter_SSs]

I found that when I applied my desired column formatting in this fashion (each time from rows 4 through 5,999), it resulted in nearly a 5MB larger file size compared to when I just apply the desired formatting to the entire column. Why is that?

According to TechRepublic ...

Formats are probably the biggest culprit for the average user. For the most part, you just have to live with that. You can help avoid bloat by deleting formats when you delete data (admittedly, that’s difficult to do). Also, use column and row formats on large areas instead of individually formatted cells. If you format 500 cells, Excel saves 500 formats. If you format a column, Excel saves just one format.

 

[jolivanes]

Re: If you format 500 cells, Excel saves 500 formats. If you format a column, Excel saves just one format.
This jives with what the OP mentions in his first post.
This is darn good to know Peter. I've always, if needed at all, just formatted the "Used Range"

 

[Jon Peltier]

If you format 500 cells, Excel saves 500 formats. If you format a column, Excel saves just one format.

Correct, but be careful. If you format a column, then change the format in one cell of the column, that's like formatting each cell differently.

It might be better to define a cell style, and apply it to the relevant cells.

 

[rollis13]

Is this thread a follow-up of this other ? LINK

 

[jolivanes]

Thanks Jon. Back to the way I did I guess.
@ Peter & Jon. Have a good weekend and keep on bringing out these tidbits to learn from.