# Excel - Change number to percentage



## dw1256

A problem I experience quite often is when I have a range of cells, all in number format, and I need to change them to percentages. Here is the problem:

Let's say the number in a cell is 75, but I want to show it as 75%. If I use format cells and change the format to percent, the value changes to 7,500%. On a small scale, this is not a problem. However, I export a large amount of data from a program into excel. The program I export from doesn't give me any options, and opens a spreadsheet on its own and fills in the numbers.

Basically, I need to change 75 to 75%, but for hundreds if not thousands of numbers.

Is there any quick and effective way to achieve this result?

Thank you.


----------



## Axiom911

that's because when you enter a number in, it is a whole number

in other words as a fraction it looks like this: 75/1

whenever you change a number to a percentage it always adds two places, by multiplying it by 100.

75x100=7500...

to do it correctly, you would need to write a number as a fraction/decimal

example--- turn 75 to .75 ---- aka 75/100

.75x100 = 75%

see if you can change the decimal places..

if not, u just gotta type it all in.

*another alternative:*

change all cells to "Text" format, and just type in the % might be a lot of work i dont know how long your list is. but suck it up and get to work LOL


----------



## dw1256

The list is just too long. Plus I need to do it every quarter with updated data.

Thanks for the ideas though.


----------



## slurpee55

A fairly easy way to do it is to type .01 (as a number) in a blank cell on the page, copy it, highlight the cells you want converted, go to Paste Special and click the operation Multiply. This will convert them to the numbers .75 etc. Then Custom format the cells (either type in or select 0%). That makes them into 75%.
There are probably faster and easier ways to do this, but, if you do this as a macro, it will be easy enough.


----------



## slurpee55

Ah, just read Axiom's note - I work with very large lists a lot (10,000 names or more) - I had to learn to do this a long time ago, but to format ZIP codes!


----------



## Axiom911

maybe in the header row where you might have it labeled put an asterix ( * ) dictating a note in a key, then in the header or footer put something like " * - notates numbers percentages "

or just label the row Percent / Percentages / something of that sort?.. simple fix?

or is it something that you are someone wants all the numbers to have the % just to look uniform?


----------



## dw1256

They represt the returns for mutual funds performance, and that is how the boss wants it to look. Not a lot of options there.


----------



## dw1256

I forgot to add this to my last post.

As for creating the macro to do that for you (.01 and multiply), how do you do that? Do I need to create a new thread somewhere else, or do you know?

Thanks.


----------



## Axiom911

reading what slurpee wrote,

you could take 1 cell and type .01 - lets say Z1

then create a formula in a new column ( lets say insert column so it is labeled B ) saying SUM=A1*Z1 then format the new cell ( B1 ) to be a percentage.

For example, the value of A1 being 75, multiplied by Z1 being .01.. equals to be .75 in a new colum. then u just click the cell and drag it down and it should automatically change the SUM formula to each corresponding cell. change the new colums format to %, and it is 75%.

A1 being the 1st original percentage cell in the series.

now, u can HIDE the original cell which u cant change the formatting of, and the factor cell being the .01 cell. and all u see is the new formulated cells showing the correct %'s

i MIGHT have thrown you off, just let me know and i can try to walk you thru it step by step if you have msn or AIM or anything. sounds like a lot of work, but really isnt.


----------



## Axiom911

just tested out my idea, and it does work.. with 1 minor change.

instead of only have one cell with a .01 figure in it. you need to make an entire column. so that corresponding formulas roll over to the correct cell.

and the correct formula would be =SUM(A1*B1) which will in turn give C1 it's value.

A1 = 75

B1 = .01

C1 = .75 converted to % = 75%

---------------

when u drag the formula it would go up in series

ie: the next cell would be =SUM(A2*B2)

A2 = 75

B2 would need to equal .01 as well.. all the way down

understand?


----------



## Axiom911

slurpee55 said:


> Ah, just read Axiom's note - I work with very large lists a lot (10,000 names or more) - I had to learn to do this a long time ago, but to format ZIP codes!


btw, zip codes suck if you dont know what to do with them !! haha i remember a corporate database i had to rebuild cuz a dumb little data entry worker deleted 1 cell at a time ( being the company names ) instead of the entire row of the company's data. thus, jogging ALLLL the information up becuz we didnt know how many company names she had deleted.

and i couldnt figure out how to format zip codes heh. this was a while back =D


----------



## dw1256

Yup i get it.

Thanks a lot.


----------



## Axiom911

dw1256 said:


> Yup i get it.
> 
> Thanks a lot.


anytime,

should look like this just to be sure










that'll be $350/hr please plus an initial consultant fee of $5200 so i can pay off my truck ;p


----------



## slurpee55

As Axiom says, you could insert a blank column next to your data (say that you have the numbers in column A) then in B1 you enter =(A1/100) and format the cells as percentages. However, they would still be formulas, so you would have to copy and paste special, values, to make them numbers, in case you want to use them in some way as numbers (although you can do calculations on formulas - e.g. getting an average).
As for a Macro, to record one just go to Tools, Macros, record new Macro. Select Store in Personal Macro Workbook, so it will be accessible from other workbooks in the future.Then do the steps to convert them and when you are done go back to Tools, Macros, Stop Recording. Note, however, that the macro will only work on the same column(s) that you create it using - that is, if you convert column A for the macro, it will always convert column A (even if it is column C you want to do it to).
Probably someone can write some code to do this easier and wherever you select - this is all I can do.


----------



## slurpee55

see the pic


----------



## Axiom911

true, 2 solutions, same outcome


----------



## dw1256

That helped a lot guys.

Thanks.


----------



## Aj_old

If U just want the numbers to have the percent sign added then use this:
Select the row that contain your numbers, chose Format cells/Number/Custom and in the field "Type" type: General"%" if you want the general format to be used or 0.00"%" if you want to be shown just 2 digits after comma!


Hope it will be usefully


----------



## dw1256

That is very useful.

Thanks a lot.


----------

