Column Width changes below 1 / Scaling Column Width

Question

Writing this here for anyone who may come across a similar situation.

I'm working on an Excel Sheet in Normal View, with default font of Calibri 11pt at 100% Zoom.

Manually adjusting the width, and noting the pixels, a column width of 1 corresponds to 18 pixels.
That is, 1 pixel = 1/18th of a column width.

However, this changes above a column width of 1 - instead, 1 pixel = 1/11th of a column width.

Hence for 130 pixels in width, you would think this would correspond to a column width of 130/18 = 7.22 width, but no, 130 pixels instead corresponds to (130-18)/11 + 1 = 11.18 width.

As such, given that column width is calculated in this bizarre way, make sure that you first calculate the width in pixels using PixelWidth = 18 + Round(11*(ColumnWidth-1)) before scaling.

Another thing to note also is that the width in Pixels in the Normal View doesn't quite match the width in Pixels in the Page Layout view. Why? No clue whatsoever. However, I found at least for the first few that the Page Layout pixels jump by 1 at the following pixel values in the Normal view:
20, 59, 99, 138, 177, 217, or approximately every 39.5 pixels, starting at the 20 pixel mark.

Finally, its worth noting that in the Page Layout view, each pixel corresponds to 0.5 pt or 1/144 of an inch, and that these increments are constant - it's just the Normal View layout that is quirky in how it scales the pixel values.
Data below for anyone interested:

	Normal View		Equivalent Page Layout View
	Width (units)	Width (pixels)	Width (cm)	Width (pixels)	Width (Points)	Point Width (cm)	Point Width Rounded (cm)
	0	0	0		0	0	0
	0.06	1	0.02		0.5	0.01763889	0.02
	0.11	2	0.04		1	0.03527778	0.04
	0.17	3	0.05		1.5	0.05291667	0.05
	0.22	4	0.07		2	0.07055556	0.07
	0.28	5	0.09		2.5	0.08819444	0.09
	0.33	6	0.11		3	0.10583333	0.11
	0.39	7	0.12		3.5	0.12347222	0.12
	0.44	8	0.14		4	0.14111111	0.14
	0.5	9	0.16		4.5	0.15875	0.16
	0.56	10	0.18		5	0.17638889	0.18
	0.61	11	0.19		5.5	0.19402778	0.19
	0.67	12	0.21		6	0.21166667	0.21
	0.72	13	0.23		6.5	0.22930556	0.23
	0.78	14	0.25		7	0.24694444	0.25
	0.83	15	0.26		7.5	0.26458333	0.26
	0.89	16	0.28		8	0.28222222	0.28
	0.94	17	0.3		8.5	0.29986111	0.3
	1	18	0.32	18	9	0.3175	0.32
	1.09	19	0.34	19	9.5	0.33513889	0.34
	1.18	20	0.37	21	10.5	0.37041667	0.37
	1.27	21	0.39		11	0.38805556	0.39
	1.36	22	0.41		11.5	0.40569444	0.41
	1.45	23	0.42		12	0.42333333	0.42
	1.55	24	0.44		12.5	0.44097222	0.44
	1.64	25	0.46		13	0.45861111	0.46
	1.73	26	0.48		13.5	0.47625	0.48
	1.82	27	0.49		14	0.49388889	0.49
	1.91	28	0.51		14.5	0.51152778	0.51
	2	29	0.53		15	0.52916667	0.53
	2.09	30	0.55		15.5	0.54680556	0.55
	2.18	31	0.56		16	0.56444444	0.56
	3	40	0.72	41	20.5	0.72319444	0.72
	4	51	0.92	52	26	0.91722222	0.92
	4.09	52	0.93	53	26.5	0.93486111	0.93
	4.18	53		54	27	0.9525	0.95
	4.27	54		55	27.5	0.97013889	0.97
	4.36	55		56	28	0.98777778	0.99
	4.45	56		57	28.5	1.00541667	1.01
	4.55	57		58	29	1.02305556	1.02
	4.64	58	1.04	59	29.5	1.04069444	1.04
	4.73	59	1.08	61	30.5	1.07597222	1.08
	5	62	1.13		32	1.12888889	1.13
	6	73	1.32	75	37.5	1.32291667	1.32
	7	84	1.52	86	43	1.51694444	1.52
	8	95	1.71		48.5	1.71097222	1.71
Default	8.09	96	1.73	98	49	1.72861111	1.73
	8.18	97	1.75	99	49.5	1.74625	1.75
	8.27	98	1.76	100	50	1.76388889	1.76
	8.36	99	1.8	102	51	1.79916667	1.8
	8.45	100	1.82	103	51.5	1.81680556	1.82
	8.55	101	1.83	104	52	1.83444444	1.83
	9	106	1.92	109	54.5	1.92263889	1.92
	10	117	2.12	120	60	2.11666667	2.12
	20	227	4.11	233	116.5	4.10986111	4.11

sergeibaklan · Answer

StephenCapon&nbsp;
Bit more is here
Convert Excel column width between characters unit and pixels (points) - Stack Overflow
How column widths are determined in Excel - Office | Microsoft Learn

Forum Discussion

Column Width changes below 1 / Scaling Column Width

Share

Resources