Office challenge: Can you avoid re-entering integer values that are supposed to be percentages?

In this week's challenge, test your Excel skills by helping a user avoid re-entering integers that should be formatted as percentages.

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.

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?


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks