In the article, 10 Excel formatting decisions that can have unfortunate repercussions, I describe a formatting decision that might cause trouble. In short, if you enter integer values and try to format them as percentages after the fact, you probably won’t get the results you expect.
When you format an empty cell and enter a value, Excel converts the value equal to or greater than 1 to percentages but multiples values smaller than 1 by 100. In other words, if you enter 1, Excel converts the value to 1%. If you enter .3, Excel multiples that value by 100 and displays 30%.
When you apply the percentage format to an existing value, Excel multiples that value by 100 and displays the results with the percentage sign. For example, if you enter 1, Excel evaluates the expression 1*100 and displays 100%. If you enter 100, Excel returns 10000%. Most likely, that won’t be a correct representation.
Automatically sign up for our Microsoft Office newsletter!
As you can see, input values are important when dealing with percentages.
Now, let’s suppose a user inherits an old spreadsheet and percentages have been entered as integers. They are formatted as numbers, but not percentages. Applying the percentage format won’t produce the desired results. Can you help this user avoid re-entering all those values?
