0

I am trying to format the following column in Microsoft Excel to add the percentage sign after the second decimal:

5.8394
3.2922
3.2
4.461
1

I highlight the column and choose Format cells > Percentage > Decimal places: 2 and get the following result:

583.94%
329.22%
320.00%
446.10%
100.00%

If I choose Decimal places: 0, I get the following:

 584%
 329%
 320%
 446%
 100%

How to I get Excel to show the original numbers as percentages without changing the values?

4 Answers 4

4

Found this answer on a different site:

Right Click, Format Cells, Custom. Manually type in "0.0\%" without the quotes.

If you want more decimal places to show, just add in zeroes. "0.000\%"

For example, if your cell contents are '4.35' then with the above custom format it would appear as '4.350%'.

Problem solved!

2
  • FYI, this also works in LibreOffice Calc.
    – fixer1234
    Jul 25, 2015 at 20:56
  • 3
    It might display what you want, but you will need to divide by 100 if you want to use it in calculations. Which is why Excel defaults to interpreting 4.35 as 435%.
    – LDC3
    Jul 26, 2015 at 0:15
3

1.0=100%, so it makes sense that if you set decimal place to 2 that it will show 100.00%.

If you would like them to be a percentage, try dividing them all by 100 first. Then choosing the percent formatting.

For example you will have 3.2/100=0.032, then when you change the cell to percent with 2 decimal places it will be 3.20%.

Hope that helps!

1
  • I understand what Excel is doing, I just think it's silly. So the only way to do this is to run a divide operation every time I paste a new number into this column? Is there really no way to simply tack on a percentage sign at the end?
    – Matthew S
    Jul 25, 2015 at 20:12
0

It is important to note:

If your cells are already populated with a number, lets say "5.8", and you apply the percentage formatting afterwards, the cell will display "580%."

If you apply the percentage formatting to blank cells, then type "5.8", the cell will display "5.8%."

Kind of weird, but try it out, and you will see

0

Another method without needing the custom formatting (for those that aren't familiar with the codes) is to simply include the % sign when entering the numbers (which I think you queried in a comment?).

When pasting or typing these figures into Excel, it will then interpret the correct percentage amount and format. In the toolbar you can then just set this column to show 2 decimal places (in which the numbers are Rounded to fit).

I guess it depends on how you are getting those figures into the sheet and if you can add the percentage% sign in at the beginning.

4
  • This wouldn't work in many use cases, because the numbers are being pasted from another spreadsheet.
    – Matthew S
    Jul 25, 2015 at 23:59
  • Unfortunately, "how" you were working with the numbers wasn't fully explained in original post. If you mean, you are manually switching to Sheet 2 and "copying", then going to Sheet 1 and "pasting" the numbers, then simply enter the percentage sign on Sheet 2 first (at the point of data entry). If you mean the numbers on Sheet 1 are being formula derived from Sheet 2 numbers, then add the percentage sign onto the formula. If the original numbers on Sheet 2 are being generated by some other source, then see above two points.
    – Kram
    Jul 26, 2015 at 0:36
  • Again this doesn't cover all cases. If the column already has data and you are simply adding to it (especially if you're doing it on a regular basis), then you're in trouble because you'll continue to need to format existing data. The solution I posted works perfectly.
    – Matthew S
    Jul 26, 2015 at 1:39
  • I agree that your solution works in more cases.
    – Kram
    Jul 26, 2015 at 2:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .