0

I am inputting numbers into a cell that is formatted to display the number as percentage.

I desire to enter 10%, so I type “10” into the cell and hit Enter. The cell now displays 10% (great).

I desire to enter 0.2%, so I type “0.2” into the cell and hit Enter. However, now the cell displays 20%!

This behavior seems inconsistent. Is there are way to ensure it always behaves as the former?

If I type “0.2%” and hit Enter I get what I desire, but this is slightly tedious because I can't use the numpad for fast input. Also, there is a good chance I'll forget to add the “%” manually and input data incorrectly.

Scott - Слава Україні
  • 22,532
beebeetatter
  • 1

2 Answers2

2

Please specify what version of Excel you are using.

  1. When I do what you describe in Excel 2013, I get the desired result; i.e., if I enter “0.2”, I get “0.20%”.  If I enter “.2”, without the leading zero, I get “20.00%”.  So, if you made an error when typing your question, and you typed “.2” when you got “20.00%”, then the answer is to always type the leading zero when entering a value less than 1%.  This allows you to keep your hand on the numeric keypad.

    But still has the drawback that you have to remember to do it every time, and if you forget to do it, Excel will happily, quietly (with no warning) give you the wrong answer.

  2. Another approach is not to format the cells as Percentage until after you’ve done the data entry.  Leave them with the default format (“General”), or format them as Number, if you prefer.  Enter the numbers you want, e.g., “20”, “17”, “10”, “7.2”, “2”, “.2”, etc.  Then, when you’re done, divide them all by 100 and format them as Percentage.

    This should solve your problem if your workflow calls for a data entry phase, followed by some sort of analysis / display phase in which the percentages are unchanged.  If your workflow requires frequent updating of the percentages, this might not be helpful.

    How do you do the same arithmetic operation (e.g., divide by 100) to a range of cells?  Here’s a couple of ways.  (I’ll assume that the numbers that you want to manipulate are in cells A2:A100.)

    • Pick a column that you aren’t using; say, Q. Enter =A2/100 in cell Q2 and drag/fill down to Q100.  Then copy cells Q2:Q100 and paste values into A2:A100.
    • Pick a cell that you aren’t using; say, Q17. Enter 100 in cell Q17 and “Copy” it.  Then select cells A2:A100 and do “Paste” → “Paste Special” → “Divide”.

    In either case you will probably need to press Esc to dismiss the selection.  You might then also want to clear the cell(s) you used temporarily.

    Both of these approaches have the issue that blank cells will be turned into zeroes.  Ways of addressing this issue are discussed in Display Blank when Referencing Blank Cell in Excel.

  3. Yet another approach is to maintain two columns in perpetuity.  Pick a column that you aren’t using.  For example, if your percentage numbers are in Column P, then you might choose Column Q (or AP).  Enter =Q2/100 in cell P2, format as Percentage, and drag/fill down.  Then enter your raw data (i.e., the numbers ranging from 0 to 100) into Column Q (which you will have left as “General”, or formatted as “Number”).  Do all your edits in Column Q.  Hide the extra column when you want to print.

Scott - Слава Україні
  • 22,532
0

Excel has a setting for 'smart percentages' - if this is on, it tries to think for you.

If you enter 10, it thinks 'you probably don't mean 1000%, so it should be 10%'; if you enter .2, it thinks 'you probably don't mean 0.2%, so it should be 20%'.

Switch the setting to off, and the behavior is consistent for all entries (it will always multiply by 100 to show percentages, so 10 will give 1000%)

Aganju
  • 10,161