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

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
415
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

rollis13

Active Member
Joined
Jul 30, 2012
Messages
415
Office Version
  1. 2016
Platform
  1. Windows
Well, you probably need to Debug your macros.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,050
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,709
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Jul 30, 2012
Messages
415
Office Version
  1. 2016
Platform
  1. Windows
Is this thread a follow-up of this other ? LINK
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,709
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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