how do I change format to percentage without multiplying by 100

  • Thread starter Thread starter Fdn Girl
  • Start date Start date
F

Fdn Girl

I have imported ROI data for securities to Excel. However, when the ROI
comes over, it is formatted as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and I get 130%.
 
Fdn Girl said:
I have imported ROI data for securities to Excel.
However, when the ROI comes over, it is formatted
as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and
I get 130%.
Click to expand...

You could use the Custom format `0.0\%` without quotes.

But I think that is a poor idea. Note that if you ever use that cell
reference in an arithmetic expression, you must remember to divide by 100
each time, e.g. =A1*A2/100, where A2 contains the number 1.3 formatted as
`0.0\%`.

I think it would be better to convert the imported number. You can do this
by putting 100 into an unused cell and copy it. Then select the ROI cells,
right-click, and click Paste Special > Divide > OK, and format the ROI cells
as Percentage. You can delete the 100.
 
1.3 equals 130%, it's not a matter of formatting. You need to divide those
values by 100, then format as %.

To do that, type 100 in a cell somewhere, then highlight it and go Copy,
then highlight all the 1.3 etc cells, right click, select Paste Special, and
check Divide.

Now format them % (change decimal place to 1) and there you go.
 
1.3 is 130%. The same way 0.5 is 50%. You are best off to divide your numbers
by 100 and formatting those. You could do a custom format but to use the
value in a calculation you will have to divide it by 100 anyway.
 
I said:
I think it would be better to convert the imported number.
You can do this by putting 100 into an unused cell and
copy it. Then select the ROI cells, right-click, and click
Paste Special > Divide > OK, and format the ROI cells
as Percentage. You can delete the 100.
Click to expand...

If that seems too tedious to do every time you import data, we could show
you how to put the operations into a macro and even create a "button" to
execute the macro each time.


----- original message -----
 
You could use a macro to divide all by 100 before formatting.

Sub NumToPercent()
For Each c In Selection
a = c.Value
If IsNumeric(a) Then
If a <> 0 Then a = a / 100
c.Value = a
c.NumberFormat = "0.0%"
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
Back
Top