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....?

 

[d0rian]

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

Not specifically, but in trying to troubleshoot that file-size issue, I went down a formatting / file-size rabbit hole, and it led to Q's like this.

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.

I wondered this exact same thing upon reading that TechRepublic excerpt, but...I'm not sure this quite adds up for me. Recall I started with the observation that formatting 6,000 cells seemed to massively bloat my file size compared to just formatting the entire column, right?

So I wondered "well, what would happen if I formatted entire column A : A in red font, but then changed cell A2 to blue font?" Because if that would result in Excel treating it as though I had literally formatted > 1,000,000 cells individually....well that one simple action would in and of itself result in an unusably huge file size (given that a mere 6,000 individually-formatted cells added 5MB to the file size)...but clearly Excel is 'smarter' than that, because that's something millions of people do every day.

So...I'm still not entirely sure. It's a file that I created 5+ years ago with plenty of VBA, frequent sorting, etc, and one I've progressively revised over the years so possible that was some corruption and what I've fingered as formatting issues in this post and others are actually a product of accumulated corruption. I dunno...