Excel row height logic calculation

Fabio Almeida 1

Using C# and OpenXML, given a font name and its height, I need to calculate the exact row height in pixels in order to draw an Excel spreadsheet in a CAD software.
What I have done so far: I have been able to accurately calculate a column width given the font in the "Normal" style. Column width is given in number of characters and can be translated to pixels according to OpenXML documentation. That's OK.
My only problem is how to calculate row height, which is given in points. My issue so far is to find a precise way, or the logic, behind its calculation. Given a font and its height, how much should be added as margins or paddings so I can calculate the exact how height? Is it based on font line-spacing, height or any other parameter? How much should be added? Is there any factor that should be multiplied? What is the formula behind it?
This answer is no where. I would much appreciate to know the logic behind row height calculation so I can get a precise value in pixels. Thanks!

Jeff McCashland 476 Reputation points Microsoft Employee

2021-02-03T22:53:15.93+00:00

Hello FabioAlmeida-8283,

Thank you for your question. One of our engineers will respond soon.

Best Regards,
Jeff McCashland
Microsoft Open Specifications
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-02-04T18:50:26.403+00:00

Hi Fabio

I will do a research and try to find an answer for you.

HungChun Yu
Microsoft Open Specifications
Paula Rucker 0 Reputation points

2023-10-09T13:13:14.6566667+00:00

I am having an issue since I last updated Office. The row height for Calibri 11 was always 15. Now it is 14.2. This is across the app - even files I created prior to the update have a row height of 14.2. This is causing the sheet to look cramped. How can I restore the row height to 15 by default? If I change the row height for the sheet, then wrapped text will not automatically make the row height increase to view all text. (I'm using version 2307 [Build 16626.20208]) Please help!
KristianSmith-MSFT 31 Reputation points Microsoft Employee

2023-10-09T15:56:34.37+00:00

Hi Paula,

Your question does not appear to be related to the topic of this thread or Microsoft Open Specifications. I recommend creating a new thread with the [Excel] tag to get assistance for the issue you've described.

Regards,
Kristian S
Microsoft Open Specifications

15 answers

Fabio Almeida 1 Reputation point

2021-02-22T17:53:28.023+00:00

As we need to draw the table in a CAD application, given a font height in points, we need to calculate the row height in points properly, in order mimic exactly as it is seen in Excel.

That's why we need the algorithm that transform a given font height in points, into a row height in points.

In summary, given a font name and height in points, what is the algorithm that calculates row height in points?

Example: Calibri 11 height into 15 row height.

At first, we are tempted to deduce that there are 3 points as margins + 1 point. But this factor is not linear as we change font height, and worse, it changes depending on the font, for the same height.

We appreciate you patience in helping us. We are looking forward for this algorithm. My deadline is so close!
Please sign in to rate this answer.
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-02-22T23:12:57.693+00:00

Hi Fabio

“The reason row height is specified in point size is so that given device, font and other characteristics, applications can render in a way that is visually appealing or meets the requirements of their application. This does not include mimicking the design choices of another application. Rendering is left for the consuming application as a choice and not prescribed by the standard.”

Thinking outside of the box, here an Excel Marco that would calculate the RowHeight for a given range and store the value in the 10th Column.

Sub RowHeight()
'Select to AutoFit Entire sheet
ActiveSheet.UsedRange.EntireRow.AutoFit

Dim MyRow, Range1 As Range

'Assume there are only 20 rows from the sheet from Row 1 to 20
Set Range1 = Range("A1:A20")

'Loop through each Row, figured out the RowHeight and insert the value in Column 10 of each row
For Each MyRow In Range1
Cells(MyRow.Row, 10).Value = MyRow.RowHeight
Next
End Sub
Sign in to comment
Fabio Almeida 1 Reputation point

2021-02-23T00:00:35.777+00:00

Thank you again, but that is not the answer for my previous question.

We simply need the algorithm, or the logic, or the explanation.

Let us focus on the "Why". It may help us.

Why Calibri 11 points height is 15 points row height?
Please sign in to rate this answer.
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-02-23T17:47:41.307+00:00

Hi Fabio

Excel does this for readability but the formula is not something that should be documented in the standard or implementation notes.”

Excel adding 1 pixel below and maybe 2-3 above

守黎韦 0 Reputation points

2023-04-10T08:12:34.0433333+00:00

Hung-Chun Yu
Hi Huang-Chun
the image is dead...

Hung-Chun Yu 976 Reputation points Microsoft Employee

2023-04-10T19:20:47.0766667+00:00

Thank you for bring this to my attention. Image readded.
Sign in to comment
Fabio Almeida 1 Reputation point

2021-02-23T18:37:39.74+00:00

We are getting close!!! Thank you!

If you double-click the row header (not column header) the row height is automatically adjusted to a standard height, according to the font height plus some undocumented factor or formula.

We need that formula.
Please sign in to rate this answer.
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-02-23T19:16:44.107+00:00

Hi Fabio

Just like I mentioned before "the formula is not something that should be documented in the standard or implementation notes." Closest you will get just make sure to enable auto-fit, and retrieved the row height from saved document.
Sign in to comment
Fabio Almeida 1 Reputation point

2021-03-01T13:12:47.067+00:00

Thank you again for your patience and willing to help!

As mentioned, we need to draw the table in a CAD platform. As we need to replicate exactly what we see in Excel, we came to an imprecise formula: (font line-spacing * 1.05) + 1 pixel.

It is evident that there is some margin or padding above and below the text. That value varies according to font size and font name, and it is not linear.

So we are looking for a precise algorithm. Is there a way to setup a meeting with you or an Excel software engineer, so I could present my need and get this answer?

Thank you again. Looking forward to your answer.
Please sign in to rate this answer.
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-03-01T17:48:12.203+00:00

Hi Fabio

That is the official word from Excel team

“The reason row height is specified in point size is so that given device, font and other characteristics, applications can render in a way that is visually appealing or meets the requirements of their application. This does not include mimicking the design choices of another application. Rendering is left for the consuming application as a choice and not prescribed by the standard.”

Excel Marco/VBA do provide the function that returned the RowHeight that you are looking for

Sub RowHeight()
'Select to AutoFit Entire sheet
ActiveSheet.UsedRange.EntireRow.AutoFit

Dim MyRow, Range1 As Range

'Assume there are only 20 rows from the sheet from Row 1 to 20
Set Range1 = Range("A1:A20")

'Loop through each Row, figured out the RowHeight and insert the value in Column 10 of each row
For Each MyRow In Range1
Cells(MyRow.Row, 10).Value = MyRow.RowHeight
Next
End Sub
Sign in to comment
Fabio Almeida 1 Reputation point

2021-03-01T18:07:52.877+00:00

That is not my question. We are not using Excel. We are developing an Excel compatible application, so we need to understand the algorithm to calculate row height, given a font name and its size.

See the picture. You will notice that there is a certain amount of space above the text.
Could someone tell us, based on font metrics, what parameter is involved and what is the equation?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Excel row height logic calculation

15 answers