# Margin padding in cell (Excel)

• ### Question

• Hi all.

In 18.3.1.13, the attribute width says:

Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, …, 9 as rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.

This is true for the standard font Calibri 11. But I put Franklin Gothic Book in Excel 2013 as default theme font and margin padding became equal 6 (three on each side). This has led to what has changed translation from pixels to character width (in formula in 18.3.1.13)

Which leads to the question - how depends margin padding on the normal style's font.

And one more question: how to calculate translate from pixels to character width, when column width in pixels < 5 px.

thanks - Alex

Tuesday, July 22, 2014 10:35 AM

• Hi Alex, I've been working with the Excel PG and here is how the column width is calculated. My own calculations have given consistent results with what I have observed in Excel with various fonts and sizes.

1. The Pixel Padding portion is not simply 2 + 2 + 1 = 5. This works out only in cases where the MaxDigitWidth is between 4 -8. You would probably never have a default font that was 3 pixels or less in width, but anything 9 or greater uses a different pixel padding value. In actuality it's ((MaxDigitWidth / 4 rounded up, or 2, whichever is greater) x 2) + 1). So, if the font you using has a MaxDigitWidth of 10 pixels the pixel padding is going to be 3 + 3 + 1 = 7.

1. The portion of the calculation that is [{Number of Characters} * {Maximum Digit Width} + {5 pixel

padding}] is also rounded up to the nearest multiple of 8 and then the MaxDigitWidth is subtracted from the total.

The complete algorithm looks something like this omitting the total pixel padding calculation from item 1 above.

width = Truncate( ( ( ( NoC * MDW + PP ) ) + ( ( NoC * MDW + PP ) MOD 8 ) ) - PP ) / MDW ) * 256 ) / 256

As an example, here is how it works out when using Century Gothic 16 point, which has a MaxDigitWidth of 12 pixels.

width = Truncate( ( ( ( ( 8 * 12 + 7 ) + ( ( 8 * 12 + 7 ) MOD 8 ) ) - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( ( ( 103 + ( 103 MOD 8 ) ) - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( ( ( 103 + 1 ) - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( ( 104 - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( 97 / 12 ) * 256 ) / 256

width = Truncate( (8.0833…) * 256 ) / 256

width = Truncate( 2069.333… ) / 256

width = 2069 / 256

width = 8.0820 (8.08)

I have filed a request to have this information added to the MS-OI29500 documentation. Please let me know if you have any other questions.

Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

Thursday, August 21, 2014 8:51 PM

### All replies

• Hi Alex,

Thank you for this inquiry. One of our team members will review this and follow-up.

Thanks,

Edgar

Tuesday, July 22, 2014 2:47 PM
• Hi Alex, I am the engineer who will be working with you on this issue. I am currently researching the problem and will provide you with an update soon. Thank you for your patience.

Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

Thursday, July 24, 2014 6:56 PM
• Hi Alex, I did several experiments with this and I believe that the description of the calculation is incorrect. Instead of using a padding value of 5 (2 + 2 + 1) I believe that it's only taking the 2 pixels on a single side into account.

In each case I used a graphics application to determine what the Maximum Digit Width was including verifying that Calibri 11pt has a max digit width of 7 pixels. I also filled a cell with 8 digits that were the max width and let Excel auto size the column to find out what the expected result should be. Using a padding value of 5 in each case gave unexpected results, using 2 gave exactly what was expecting in all but 1 case. However, in the last case example, I think the rounding difference could be attributed to a number of things, but is still close enough to show that this is most likely the correct formula to use.

 Font and size used: Calibri 11pt Max Digit Width: 7 pixels Expected width: 8.29 Formula used Truncate( ( 8 * 7 + 2 ) / 7 * 256 ) / 256 Result: 8.2852 (8.29)

 Font and size used: Franklin Gothic Book 11pt Max Digit Width: 9 pixels Expected width: 8.22 Formula used Truncate( ( 8 * 9 + 2 ) / 9 * 256 ) / 256 Result: 8.2188 (8.22)

 Font and size used: Century Gothic 16pt Max Digit Width: 12 pixels Expected width: 8.17 Formula used Truncate( ( 8 * 12 + 2 ) / 12 * 256 ) / 256 Result: 8.1641 (8.16)

I will consult with the Office group on this to determine if this is correct. If it is, I will also file a request to have a note about it added to the MS-OI29500 document as a variation.

Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

Tuesday, August 5, 2014 4:44 PM
• Are you consult with the Office group ?

Monday, August 11, 2014 10:58 AM
• Hi Alex, I have filed a request to verify how to correctly calculate the column width. I will let you know when I hear back with any new information. Your patience is greatly appreciated.

Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

Monday, August 11, 2014 5:29 PM
• Hi Alex, I've been working with the Excel PG and here is how the column width is calculated. My own calculations have given consistent results with what I have observed in Excel with various fonts and sizes.

1. The Pixel Padding portion is not simply 2 + 2 + 1 = 5. This works out only in cases where the MaxDigitWidth is between 4 -8. You would probably never have a default font that was 3 pixels or less in width, but anything 9 or greater uses a different pixel padding value. In actuality it's ((MaxDigitWidth / 4 rounded up, or 2, whichever is greater) x 2) + 1). So, if the font you using has a MaxDigitWidth of 10 pixels the pixel padding is going to be 3 + 3 + 1 = 7.

1. The portion of the calculation that is [{Number of Characters} * {Maximum Digit Width} + {5 pixel

padding}] is also rounded up to the nearest multiple of 8 and then the MaxDigitWidth is subtracted from the total.

The complete algorithm looks something like this omitting the total pixel padding calculation from item 1 above.

width = Truncate( ( ( ( NoC * MDW + PP ) ) + ( ( NoC * MDW + PP ) MOD 8 ) ) - PP ) / MDW ) * 256 ) / 256

As an example, here is how it works out when using Century Gothic 16 point, which has a MaxDigitWidth of 12 pixels.

width = Truncate( ( ( ( ( 8 * 12 + 7 ) + ( ( 8 * 12 + 7 ) MOD 8 ) ) - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( ( ( 103 + ( 103 MOD 8 ) ) - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( ( ( 103 + 1 ) - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( ( 104 - 7 ) / 12 ) * 256 ) / 256

width = Truncate( ( 97 / 12 ) * 256 ) / 256

width = Truncate( (8.0833…) * 256 ) / 256

width = Truncate( 2069.333… ) / 256

width = 2069 / 256

width = 8.0820 (8.08)

I have filed a request to have this information added to the MS-OI29500 documentation. Please let me know if you have any other questions.

Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

Thursday, August 21, 2014 8:51 PM
• Thanks, that's what you need!!!

And I noticed that when the column width is less than 1 character, it is not changed by this formula, and in proportion. Is that so?

Tuesday, August 26, 2014 1:31 PM
• Hello JCurry, I am sorry to bother you about this old post, but I run into a problem might related to this.

I am using a 4k monitor, which is 3840*2160 resolution, the Excel default  column width is 9.09(96 Pixcels,) according to Microsoft's calculation formula:

width = Truncate ([{Number of Characters}*{Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

the thing is that maximum digit width will be a large, unrealistic number. Does this mean there is a problem with formula? and why/how?

Thanks,

Frost

Thursday, November 30, 2017 4:12 PM
• Also, related to the reply I made, do you know, by any chance, how to calculate the maximum digit width?
Thursday, November 30, 2017 4:29 PM
• Hi Frost,

Thank you for the question. We have noted you reposted the question in a new post, so we will address it on that thread instead of here.

[Excel column width]How to calculate maximum digit width?(I am a C++ programmer)

https://social.msdn.microsoft.com/Forums/en-US/d02ef91f-e89e-41c5-9c18-b9564abd9f3f/excel-column-widthhow-to-calculate-maximum-digit-widthi-am-a-c-programmer?forum=os_binaryfile

Thanks,

Jeff McCashland | Microsoft Protocols Open Specifications Team

Thursday, November 30, 2017 7:12 PM