# This page is a development documentation

Row has its height expressed in points, width of column is expressed in number of characters and fonts are rendered in pixels. This page describes a relationship and possible conversions between them.

Although there is some documentation, it is mostly wrong or at least misleading.

• Specification ECMA-376 18.3.1.13 described a width of a row and some conversions, but is incorrect. Numbers don't work.
• [MS-OI29500]: Office Implementation Information for ISO/IEC 29500 Standards Support gives a different formulas than the ECMA-376, but that formula is also incorrect.
• Several forum/group posts that just don't work, at least not in recent versions of Excel.

The info is a best conjecture about how it works developed through a lot of experimentation.

• MDW - Maximum Digit Width. A maximum width of a digit 0-9 in pixels. Whole number.
• PP - Pixel Padding. An extra pixels at the left and right border of the cell.
• NoC - Number of characters. A number displayed by the excel as a width of a column during column resizing in a tooltip. Can be a fraction.
• Width - A width of a column stored in the xlsx file. Expressed in MDW. Can be a fraction
• Pixels - A width of a column in pixels, as displayed by an excel in a dialog during column resizing in a tooltip.

MDW is a whole number that expresses a width of a digit with the largest advance. In most fonts,, digits have same size, but it takes the widest anyway.

MDW can be determined either by its outline metrics or directly from rendered bitmap. Bitmap has a preference, if exists for the font size. This preference for bitmap metrics is the reason, why Calibri has MDW 7 at 11pts - because it's bitmap metric says so. Without bitmap, it should have MDW of 8, because advance of its metrics is 1038 for digits for 2048 unitsPerEm (ROUND(1038/2048 * ROUND(11/72*96)) = 8).

For outline fonts (or font sizes that don't have embedded bitmaps) is the MDW calculated as folows:

MDW = ROUND((advanceWidthInFUnits/unitsPerEmInFUnits) * Round(fontSizeInPts/72*DpiX))

advanceWidthInFUnits is taken from hmtx table of the font, unitsPerEmInFUnits is taken from head table.

Example: 96 DPI, 11 pt font has UnitsPerEm 2048 FUnits and maximum digit advance is 1161 FUints. Its MDW is 9 ( font size is 15 per ROUND(11 / 72 * 96), and thus the result is ROUND((1161 / 2048) * 15) = ROUND(8.50341796875) = 9).

96 DPI, 11 pt font has UnitsPerEm 2048 FUnits and maximum digit advance is 1160 FUints. Its MDW is 8 (ROUND((1160 / 2048) * 15) = ROUND(8.49609375) = 8).

96 DPI, 10 pt font, 2048 UnitsPerEm, maximum digit advance is 1161 FUints. Its MDW is 7 (ROUND(1161/2048 * ROUND(10/72*96)) = ROUND(1161/2048 * ROUND(13.3333333)) = ROUND(7.36962890625) = 7)

MDW for bitmap sizes of fonts or pure bitmap fonts is taken directly from metrics of the font in the EBDT table (SmallGlyphMetrics or BigGlyphMetrics, field advance).

- Even through font rasterizer doesn't use bitmaps stored in the font file for rendering (ClearType is rendered from outline), the advance is still taken  from the EBDT table, even though bitmap data are not used.

Pixel padding is an extra space on the left and right border. PP is expressed in pixels and it is a whole number. It is used mostly by GUI for calculating NoC. It is calculated as

PP = 2 * CEILING(MD/4) + 1

On each side of a text is a small border and there is an extra pixel for gridlines. This is the reason why the ECMA-376 has number 5 in its formulas. Calibri 11pt (default font for Excel) has MDW 7, so its PP is 2*CEILING(7/4)+1 = 5.

Source: A post on a forum by JCurry, MSFT https://social.msdn.microsoft.com/Forums/en-US/9a6a9785-66ad-4b6b-bb9f-74429381bd72/margin-padding-in-cell-excel?forum=os_binaryfile

Number of characters is a number that is used by Excel to express a with of a column. User also inputs this number to modify column width (Home ribbon tab, Format, Column Width). NoC is mostly for GUI, it is not saved to the file.

pixels >= (MDW + PP) -> NoC = (px - PP) / MDW pixels < (MDW + PP) -> NoC = pixels / (MDW + PP)

There are two equations that calculate the NoC. The breaking point is the NoC = 1.0 (=column can fit one digit and extra pixel padding). Thanks to this break, the NoC is non-linear.

Width is a number that represents a width of a column in the file. It is not displayed in the GUI, just stored in the file.

width = Math.Truncate((x.Px) / (decimal)MDW * 256m) / 256m,

As far as I can see and test, pixels are just converted to the MDW directly with some rounding. That is far simpler.

To verify the equations, I created a Workbook with columns that had width from 0-26, 126-129, 254-257, 500 and saved the file with three different fonts, so I had MDW 7, 8 and 9. I then took the values from the xml directly and compared it to the formula above. Numbers matched exactly (link to gist with numbers).

Adjust to content resizes a cell to it's content. The guesstimated logic of how Excel does it (so we can mimic it) is following:

We need

• UnitsPerEm
• WinDescent
• Font size
• DPI

fontSizeInPx = fontSize / 72 * DpiX

Height of the adjusted cell consists of a central band where bottom of the band is aligned with the baseline and a space below and above the central band.

cellHeight = ((UnitsPerEm - WinDescent) + 2 * Descent) / UnitsPerEm * fontSizeInPx

The (UnitsPerEm - Descent) is a height of the central band in FUnits. and the 2*Descent is for upper and lower part.

Note that we don's use ascent. That is intentional, e.g. for Calibri, ascent is 1950 FUnits and would be different than 1498 FUnits that is actually used. Ascent from font includes inner leading. Usage of WinDescent rather than typographical descent is also important. WinDescent is stored in OS/2 font table and can differ from topographical descent (e.g. for Calibri, topographical descent is 512 while WinDescent is 550).

Width is calculated from following formula:

=textWidthInPx*1.75% + 2 + maxDigitWidthInFontSize/4

maxDigitWidthInFontSize is a MDW of a font size that is being rendered, normal MDW is from normal font of the workbook. The 2 pixels is probably from PP.

1.75% is estimated from experimentation with a font that has MDW 0 (all advances for digits are set to 0).

Let's take Calibri and scale it up to 100pt font at 96 DPI. Calibri has unitsPerEm 2048, WinDescent 550. Text is centered.

Letters J and L are used because their outline basically touches it's borders.

Per our calculation, the height should be ((2048-550) + (2 * 550)) / 2048 * 100 / 72 * 96 = 169.140625 px. Descent is 35.80729166666667 px.

The Excel auto-sizes the cell to width of 608px and height of 170px. 170px is basically 169.1px from our calculation (not sure how rounding works).

The width is 553 * 1.75% + 2 + 67/4 = 28.4275. The

Note:

• Changing horizontal/vertical alignment doesn't change adjusted dimensions

Rationale:

• Set a custom width of a column and modify MDW through style change. The custom column widths are unchanged in pixels, the columns with default width are have different width.
• Set a custom width to a column. Save file. Modify the width that is slighly, less than 1px, minimum delta 1/256. Load and save again without any changes. The resaved version will have its width set to the value calculated from pixel value that was in the original file.

1. Open FontForge
2. Modify font, in most cases metrics of digits 0-9 or it's bitmap
3. Rename font to something sensible
4. Generate ttf font
5. Install generated font file to Windows
6. Start excel
7. Change font of the Normal style to installed font
8. Experiment and observe
9. Close Excel and start over... and over...

Optionally just modify font file in hexa editor and open +rename+generate it through FontForge as first stage.

Excel renderer doesn't use kerning (check Times New Roman AV kering sequence in Excel vs elsewhere). Not sure if it is also ignored in text measurements

Excel renderer doesn't use ligurates. But ligurates are in most cases a part of GSUB and it does use them for arabic...

This is a composite arabic substituted character (Browser displays it correctly, but you can copy it to Excel and it won't):

اﻟﻠﮧ

that uses GSUB of uni0627(ا) uniFEDF(ﻟ) uniFEE0(ﻠ) uniFBA7(ﮧ) to uniFDF2(ﷲ). Excel displays it as a separate glyphs, instead of using GSUB.

It roundes the advance width of a glyph. It doesn't ceil or floor it. Proof: Write 25 A in a line and do ajust in Excel. A in Times New Roman (1479 at 2048 em per fu) at 72pt is rounded down (69.328125 px) to 25*69 (+padding), while Calibri A (1185 at 2048 em per fu) is rounded up 55.546875 to 56 (+padding).

I think Excel rounds position of each character individually, at least there seems to be cumulative error when comparing result of Excelw the GDI DrawText

The width of a glyph is adjusted by true type instructions, e.g. Times New Roman letter e would have advance width 14.79px (909 fu over 2048 em, 25pt font size, 96dpi). But thanks to instructions, it is squashed to 14 pixels. When instructions are removed, it returns to 15 pixels.

SixLabors.Fonts has an interpreter for hinting, but that is only used during rendering of a glyph (see ApplyTrueTypeHinting method in the internals).

Font tools provide a CLI to strip font file of unneeded features.

pyftsubset font.ttf --unicodes="U+0020-0025" --layout-features='' --no-glyph-names --symbol-cmap --notdef-glyph --notdef-outline --recommended-glyphs --name-IDs='' --name-legacy --name-languages='*'

One of the key problems is to get advance in pixels that is correct. For ASCII/latin letters, Excel just advances by pixels, without any kerning or GSUB or anything like that.

The problem is that SixLabors.Fonts doesn't provide correct pixel values, only "ideal" unhinted/unrounded values that can't just be rounded. Rounding/ceiling values doesn't provide accurate values.

This code here looks to provide correct values for Calibri 11pt 96DPI.

// Width of letters from Calibri 11pr at 96 dpi, calculated by hand.
var e = new Dictionary<string, int> {
	{"A", 9},
{"B", 8},
{"C", 8},
{"D", 9},
{"E", 7},
{"F", 7},
{"G", 9},
{"H", 9},
{"I", 4},
{"J", 5},
{"K", 8},
{"L", 6},
{"M", 12},
{"N", 10},
{ "O", 10},
{ "P",  8},
{ "Q", 10},
{ "R",  8},
{ "S",  7},
{ "T",  7},
{ "U",  9},
{ "V",  9},
{ "W", 13},
{ "X",  8},
{ "Y",  7},
{ "Z",  7},
{ "a",  7},
{ "b",  8},
{ "c",  6},
{ "d",  8},
{ "e",  8},
{ "f",  5},
{ "g",  7},
{ "h",  8},
{ "i",  4},
{ "j",  4},
{ "k",  7},
{ "l",  4},
{ "m", 12},
{ "n",  8},
{ "o",  8},
{ "p",  8},
{ "q",  8},
{ "r",  5},
{ "s",  6},
{ "t",  5},
{ "u",  8},
{ "v",  7},
{ "w", 11},
{ "x",  7},
{ "y",  7},
{ "z",  6},

};

// Load Font
using var st = File.OpenRead(@"c:\Temp\calibri.ttf");
using SKManagedStream fontStream = new SKManagedStream(st);
using SKTypeface typeface = SKTypeface.FromStream(fontStream);
using var font = new SKFont(typeface);
// Super important that it uses font and not a typeface. That 
using SKPaint paint = new SKPaint(font);

// Set Font Size and DPI, 72 pt per inch, 96px per inch 
paint.TextSize = 11.0f * 96f / 72f;
foreach (var c in e.Keys)
{
	//float width = paint.MeasureText(text);
	float height = paint.TextSize;
	float glyphWidth = paint.GetGlyphWidths(c).Single();
	var expected = e[c];
	var diff = (glyphWidth - expected) != 0;
	Console.WriteLine($"{c} ({glyphWidth}) {e[c.ToString()]}   {(diff ? ("DIFF " + (glyphWidth - expected)) : string.Empty)}");
}