Column & Row Size Bug

Question

I create several forms using Excel. In Excel 2011 I would start by adjusting the columns and rows to .13" wide/tall. It creates a nice grid to then custom build forms by merging cells. Works rather well. Anyhow, that's the background and not looking to change this process, but I do believe I've found a bug with Excel 2016.

Excel 2016 document headings disappear when I adjust rows/columns to .13" height/width. However, I can use Excel 2011 to make these changes, save out the document, open in Excel 2016 and they appear fine as .13"x.13" cells!

I also noticed in Excel that you can't add inch specifications in Excel 2016. An error comes up. You could in Excel 2011. 

I also noticed in Excel 2011 that when you manually move the cell resizer in the headings it would give 2 different values an unknown and inches. In Excel 2016 it is the unknown and pixels. Maybe Excel 2016's units are measuring in pixels rather than inches now? I do have my ruler preference to be inches though. 2011 would allow you to enter different units of measurement and it would convert to the measurement unit being used. What happen to this conversion? I could enter 55mm in 2011 and it would work, in 2016 I get the "Alert, Your entry cannot be used. An integer or decimal number may be required". 

Here's a few screengrabs:

Answer 1

Most of your observations seem to be accurate - IMO Microsoft has gummed up the works in terms of setting those values. They've implemented 2 separate methodologies determined by the View being used.

When in Normal View or Page Break Preview the "unknown" value you're referring to is an archaic throwback based on the Windows version. It is the column width in characters, with the default being 10 characters wide. [For Row Height I believe it's Points.]

When you enter a column width of .55 Excel 2016 interprets that as 55/100 of a character & you cannot enter anything other than numbers with or without a decimal point. Pardon my candor, but I believe that's one of the dumbest moves ever made :-) I say archaic because it was adopted at a time when only fixed width characters were used on personal computers, so it was a constant strictly based on the design of the font being used. However, virtually nobody any longer uses fixed width fonts. With proportional fonts there is no set character width so that measurement is worthless to most users.

However, if you switch to Page Layout View things work differently. The measurement is done in Inches (or whatever you have the Ruler Units st to): the dialog will interpret a value of .55 as 55/100 of an inch rather than as 55/100 of a character... you don't even have to include " or in. In fact, the tip box when you drag to adjust will display inches & pixels rather than characters & pixels while in that View.

If you agree that having to switch view in order to adjust those values is ridiculous, please use the ☺︎ at the right end of the Excel Ribbon to submit your comments. Also, you can post your opinion or vote for an existing suggestion atExcel for Mac

Answer 2

If you have measurement set MM or CM  leave the "mm" off.

__________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone and do not reflect upon my position as a Community Moderator.

Answer 3

Hi Christopher Wrasse,

Have you referred on Phillip's response? Do you need further assistance on this?

Thanks,

Jerry

Answer 4

Thanks for the information Phillip, I did confirm that my "Ruler Units" in preferences is set to Inches. But it appears there are inconsistencies between the two applications. And, in addition to the inconsistencies, why wouldn't the measurement set in the preferences not show in the Column/Row Width adjustment box? It did in 2011, you can clearly see the inch markers so you knew what measurement it was using. AND, if you wanted to use cm or mm, in 2011, you could put 22mm and it would convert it to inches.

2011 appears to have a MUCH BETTER measurement and conversion system. Try to do ANY of those things in 2016 and you get many alerts about integers and what not. There are so many issues with 2016 but I'm trying to put in posts for EACH one. I use Excel EVERY day and have a large user base that we are trying to move to 2016 but all the inconsistencies and issues between the two are astronomical, users do NOT want to give up 2011!

Anyhow, sorry for the ranting, here's a screenshot of a sample test. I opened a document in 2011 and adjusted column C to .5". I then opened the same dialog and took a screen shot. And also displayed the preferences to show it's on inches. Next, I opened a 2016 document and did the same routine. Now, aligning them top to bottom and using my magenta rules, you notice how they aren't the same? Both at 100% scale, both on "inches" but not the same. And, after going back into my 2016 inches dialog, it set to .55, not the .5 I originally entered! Why no measurement indicators in 2016? If I have it set to inches in preferences, why no " marks? 

Answer 5

Most of your observations seem to be accurate - IMO Microsoft has gummed up the works in terms of setting those values. They've implemented 2 separate methodologies determined by the View being used.

When in Normal View or Page Break Preview the "unknown" value you're referring to is an archaic throwback based on the Windows version. It is the column width in characters, with the default being 10 characters wide. [For Row Height I believe it's Points.]

When you enter a column width of .55 Excel 2016 interprets that as 55/100 of a character & you cannot enter anything other than numbers with or without a decimal point. Pardon my candor, but I believe that's one of the dumbest moves ever made :-) I say archaic because it was adopted at a time when only fixed width characters were used on personal computers, so it was a constant strictly based on the design of the font being used. However, virtually nobody any longer uses fixed width fonts. With proportional fonts there is no set character width so that measurement is worthless to most users.

However, if you switch to Page Layout View things work differently. The measurement is done in Inches (or whatever you have the Ruler Units st to): the dialog will interpret a value of .55 as 55/100 of an inch rather than as 55/100 of a character... you don't even have to include " or in. In fact, the tip box when you drag to adjust will display inches & pixels rather than characters & pixels while in that View.

If you agree that having to switch view in order to adjust those values is ridiculous, please use the ☺︎ at the right end of the Excel Ribbon to submit your comments. Also, you can post your opinion or vote for an existing suggestion atExcel for Mac  

Bob,

VERY INSIGHTFUL reply! Thank you for the background on the measurement unit used in Excel. While I have been in the Desktop Publishing industry long enough to know fixed width characters, I did not know that Excel or other Office software adopted that method for their measurement units. Very interesting and ridiculous at the same time, well I mean for our time in age (as you also stated).

I almost feel that Microsoft at times will port over a new application and play catch up on development. I mean this is clearly evident in these forums and the "suggestion" site where most are asking for features back from previous versions! If you want to laugh, go from Lync to Skype for Business. Well, they have played a lot of catch up on this app so if you did you wouldn't see what I'm referring to but it was bad. It was so bad I wrote a 7-8 page documenting serious issues that prevented our company moving to that application for business communications. That document got forwarded to the Microsoft Skype for Business engineers directly. Microsoft flew engineers out to our offices in Detroit where they sat with me going over my entire document. While some issues were addressed, there are still many issues.

I guess I am conforming with Microsoft's issue/suggestion process and posting as many issues I can to eventually be heard. It's a shame that I have to waste my time putting in each of these but I must for sanity purposes (ha-ha). This issue is one of those times.

Again, thank you for taking your time to explain to me what's going on. While I see where the issue lies, I would still like to see a resolution here. I don't think it will be an easy task to change the entire measurement system in this program but hopefully this post will create movement. I may go to the suggestions page, write up something about changing the measurement system to the 21st century units of measurement and post the link here in the event other stumble on this post.