Making column and row width the same

[test_man2]

Greetings! How can I make exactly square cells? If I set a column width to 80 pixels (the number in the parentheses), the Width is 8.11; but if I set the row to that same number of pixels, the Height is 48. Why aren't they the same size since the pixel count is the same?

 

[jasonb75]

They use different measurements, these articles explain what you're asking in detail but the simple version is that height is measured in points (1 point = 1/72 inches) whilst width is characters per inch for a standard font.

How column widths are determined in Excel - Office

Provides information about the factors that determine the column widths in Excel.

docs.microsoft.com

Point (typography) - Wikipedia

en.wikipedia.org

 

[test_man2]

Thanks for the resources, Jason, but they don't exactly answer my first question - how do I make exactly square cells. Right after I posted this question, I decided to do a little experiment on my own to see if I could come up with an acceptable answer and I think I did. Aaand it was probably the long way too, but I got an answer!
I inserted a rectangle shape and held the shift key down while sizing it - that keeps all sides the same (making a square instead of a rectangle!). I then resized a cell's column and row sides to the sides of the square. What I noticed in the size differences is the Row size is six times the Column size. If I set the Column size to six, I can set the Row size to 36, and the cell is virtually a square (the Row is one pixel more than the Column). Being a tester that I am, I reset the Column size to 10 and the Row size to 60 and got the same results - a (virtual) square! This time the Row's pixel count was three pixels more than the Column. But you get the gist.

 

[jasonb75]

If I set width and height to 80 pixels (as per post 1) then I do get an exact square, hence the reason why I thought that you were asking why the numbers not in parentheses were different and provided links that could explain the reasons. Not sure why yours would be any different.

After reading your reply above, my first thought was that it could be an incorrect system setting and that you were perhaps had a screen resolution set that was not compatible with your physical display, however if that was the case then I would have expected the cell aligned with the fixed shape should have still been the correct size but just visually distorted.

I have tried the same experimental method as yourself and found it difficult to manually align the shape and cell borders precisely, also the ratio is actually something like 5.92 not 6 so I would expect a small variation with rounding and alignment errors taken into consideration.

If you go to the 'View' tab on your excel ribbon, then click on 'Page Layout'. The view format will change and you will be able to see the size of the cells in pixels and in mm (80 pixels = 16.93mm in both width and height, or at least on my display). Perhaps that might help you figure it out a little more?

I'll be going offline in about 30 minutes and I'm not likely to be back on the forum for the next 24 hours or so but will check back if and when I can to see how things are progressing.

 

[EXCEL MAX]

The cells have two type of the size.
One type is in pixels and it's depends of screen resolution.
Second type is approximate number of characters expected to fill one line in the cell (when you looking to a cell width),
and approximate font height expected to be filled in the cell (when you looking to a cell height).
It's a hard to make size settings using this messurement.
If you want to use this messure, you need to convert this messure to the pixels and after that convert pixceles to another messure.
To do that, you also need to know relation between different messures.
It's logical that computer can calculate with many decimal places,
but screen can not divide pixcels to a parts and that is reason why you can't make precisly size settings.

 

[johnnyL]

I have tested Excel 2013 & 2016. The row height to column width ratio is 7.2 to 1.

So, for example, if you set the column width to 10 & the row height to 72 you would have a square cell.
Another example would be column width of 5 & row height of 36 ... that would result in a smaller square cell.

I can't vouch for 2019 or 365 because I have no way of testing those.

 

[test_man2]

I appreciate all of your input, and it is showing that this question is more complicated than I initially thought! Jasonb75 says that the column/row ratio is 5.92 (which is more precise than 6), and johnnyL says it is 7.2 (at least in Excel 2013 and 2016). jasonb75 subtly suggested that it might be related to the display that I'm viewing the spreadsheet on (when I set it on my laptop and it looks like a square, it might not look like one when I view it on an external display). My conclusion is that there isn't one answer that will fit every situation! Is that a valid assumption?

 

[EXCEL MAX]

Yes, you are right.
The problem is column width of the cell.
To check that you can set cell width to a 100px and divide with column width of that cell.
That will give you one relation.
But if you increase cell width to 500px and now divade with current column width the relation is different.
This is not case if you try to do that with row hight. Here is relation always 3/4.

 

[jasonb75]

@EXCEL MAX, @test_man2
The width to height ratio may not necessarily vary with excel version as @johnnyL suggested, I don't have my laptop with me to check but from reading the articles that i provided links to earlier it appears that it would be set by the default font used in excel which can be changed somewhere in the options. If this is correct then the ratio will be user specific rather than version specific.

The conclusion that there isn't one answer to fit every situation is a very valid assumption, not just for this question, but for nearly everything in excel.