Software

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?

About

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.

3 comments
v_balaj
v_balaj

When you have percentage values in excel already present as numbers, but you want to convert as percentage then use of the method to go to Format Cells: 1. Excel Version before 2007 Format -> Cells 2. Excel Version 2007 or later Home -> Format -> Format Cells 3. Keyboard shortcut Control + 1 Under Number tab choose 'Custom' then in 'Type:' type as 0"."00% and then press OK. Now the numbers, say 10, will be converted to percentage with same number, as 10%. Note: After choosing this format if you re-enter values then the values will be divided by 100. For example, if a cell has the above said format, when you key in value 100 will be shown as 1%.

joan.edington
joan.edington

Enter 100 in an empty cell and copy it. Select the column values to be converted. Edit/paste special/divide. While still selected, format/cells/percentage. Clear the cell with 100.

allan.pettie
allan.pettie

Add a column to the right of the incorrectly formatted data, format as %, enter a formula to divide by100, fill down. Copy the column of answers, Paste Special/Values back onto the original column.

Editor's Picks