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

d0rian

Active Member
Joined
May 30, 2015
Messages
263
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

Active Member
Joined
May 30, 2015
Messages
263
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...
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,643
Members
416,124
Latest member
DeMoNloK

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
Top