I am building from external data a table where columns can have one or two decimal numbers.
For one decimal number I want 1.0 instead of 1 and for two decimal numbers I want 1.40 instead of 1.4.
The problem is that the number of decimal positions can change for the different columns and over time. I don't want to reformat column by column at any update.
Is there a way to force Excel to show 1.0 and 1.40 at the same time if that is what I have in the original source?
In newer versions of Excel it is possible to use conditional formatting for the number of decimal places.
Create conditional formatting rules that use a formula to inspect a helper cell where you manually enter the desired number of decimal places for the column. In the following screenshot, these helper cells are in row 1.
Simply change the number in row 1 to display 1 or 2 decimals. The values in row 1 can also be the result of a formula.
Yes.
The following format will do as you wish (precisely as stated... if the precise examples are not perfect, this will not be either):
#.#"0"
The "#" to the right of the decimal place will show any first decimal digit ("tenths") that exist (so nothing past the decimal for "1" and the "4" for the "1.4").
Then the added text "0" will give you the ending "0" you desire on all those numbers.
So "#" takes care of deciding what the last digit to display (ones or tenths) is and the text "0" polishes it off.
This is display ONLY so you can still use the displayed values as numbers. Or, put another way, you HAVE to still use the displayed values as numbers. The fact that a text element is added to the end is immaterial as it is display only. Nothing has actually been changed to text the way a formula that added a text "0" to the end of a number would do.
