I have spent most of today throwing data around in MS Excel.
One of the things that I have had to do is to convert percentages into real numbers.
In excel percentages are stored as fractions, for example 50% is stored as 0.5, 75% as 0.75 etc.
I am trying to chart these figures in Business Objects Xcelsius but the y-axis labels are just showing 0 to 0 hence why i need to convert my numbers.
I have just found a really easy way to do this.
- In the target cells, enter the value 100 in each cell.
- Copy the source cells
- Select “Paste Special” using the options “Values” and “Multiply”
This takes the source value 0.75 multiplies it by 100 and stores the result 75 !
I have used pase special – values often but I have never seen the need for the multiply option until now !
You may want to check out some of my other Excel related posts here.
19 replies on “MS Excel – Convert Percentage To Number”
You helped me a lot with this post. I tried to figure out the conversion from numbers to percent without getting my numbers multiplied with 100. Thanks a lot!
Thank you so much! You’ve helped me a lot!
Ross, thanks so much for providing this clean solution!
Good tip, sure saved me some time
A great help! and quick way to work with
Thanks so much! You really saved my life — or, at least, a ton of time!
Thanks, I forgot this and finally found on ur blog…. 🙂
very useful, thanks!!!
if it wasn’t b’se you saved me i would not make it, thanks
wonderful, something I’ve been looking for. Currently I have to calculate many percent. I had been looking on this website http://www.excel-aid.com/excel-percentages-calculating-percentages.html but I was looking for something shorter like your post. Thank you!
but when it comes to pivot table, if the original value is having decimals, the converted value still shows decimal, cannot be classified.
Thank you! Just what I needed to know!
Thanks, So easy and helpful 🙂
This is an amazingly great tip! I didn’t even know all these additional paste special options were available so I’ve learned several things all at once.
I just used this to convert two columns & >500 cells of non-percentage numbers & percentages expressed as text (i.e. “85.96%”) to correct percentage expressions without getting goofy percentages like 8596.00%!
However, I used it in reverse: I copied & pasted the 0.01 into my columns of data using the “Special” & “Multiply” functionality – it was the easier approach for me.
Thanks! Will definitely use this a lot in the future!
1. Select the cells with percentage (%) formats.
2. Press Ctrl+F and in find dialogue box enter % sign in Find what text box and then click Replace All.
3. Now you have relevant number values in those selected cells and format the cells into Numbers
4, Thats all.
Thanks Ross – what a life saver this was for me!!
I had a column of percentage numbers from a third party spreadsheet with each entry looking like #.##%, for example, 0.72%. Could not do addition of the cells in this column – sums always came up as 0. I tried formatting the cells as number and as percentage and neither caused an immediate change. I needed to change this column to true numbers in percentage format. So I used a modified version of your tip: I made a cell containing the number 1.00 and then formatted that cell as percentage, so the 1 looked like 100%. I copied this cell, then selected my problem cells and did the paste special multiply over them. This immediately converted everything in the problem cells to the correct numeric value expressed with the percentage sign. The additions then worked correctly.
Thanks for the tip! I had never used that option in the “paste special” feature and it comes in handy. Never thought about using it to fix stubborn formatting issues.
An alternative is to select the problem cells and do a search/replace of “%” with nothing. This converted to numbers. Then I had to divide the column by 100 to get the correct values and reformat as percentage. This was more work.
I am using the 2013 version of Excel, other versions may behave differently.
Thank you so much Ross, this saved me a lot of time & frustration!