MS Excel – Convert Percentage To Number

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.

17 thoughts on “MS Excel – Convert Percentage To Number”

  1. Hy, Dude!

    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!

  2. but when it comes to pivot table, if the original value is having decimals, the converted value still shows decimal, cannot be classified.

  3. 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!

  4. Simple –
    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.

Leave a Reply