none
Margin padding in cell (Excel) RRS feed

  • 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

Answers

  • 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
    Moderator

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
    Moderator
  • 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
    Moderator
  • 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.

     

    Please let me know if this answers your question.


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

    Tuesday, August 5, 2014 4:44 PM
    Moderator
  • 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
    Moderator
  • 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
    Moderator
  • 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
    Moderator