Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Decimal Number To Percentage Formula (newbie Question)

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I've got a column full of numbers like 0.5, 1.0, etc. I need them to look
like 0.500%. I figured out how to format the cells so that 0.5 looks like
0.500, but when I change the cell format to percentage, it makes 0.500 into

So I figured out how to make a formula that divides 0.500 by 100
(=R1C6/100), resulting in 0.005, which I can format to a percentage and get

Where I'm stumped is in applying that formula to the entire column. Instead
of =R1C6/100, I want my formula to say =all rowsC6/100. How do I tell it

Thanks for putting up with a newbie
Ken Benson

View Answers     

Similar Excel Tutorials

Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...
Get the Decimal Part of a Number in Excel
How to get the decimal part of a number without any rounding in Excel. This tip allows you to simply remove the who ...
Format Cells as a Percentage in Excel Number Formatting
This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number f ...
Round to a Specific Decimal for Any Number in Excel
Round each number to a specific decimal number. This works for numbers that don't already have a decimal and ones t ...

Helpful Excel Macros

Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u

Similar Topics


I've got some numbers on my excel spreadsheet and a cell to calculate the percentage.

For example, I've got cells called:
(C2) Target = 47
(C3) Achieved so far = 46
(C4) Percentage = 97.87%

I've formatted the percentage cell to 2 decimal places.

However, later on in the spreadsheet, I have a formula in C10 saying:

="Percentage of work achieved so far is " & C4

The cell returns "Percentage of work achieved so far is 0.978723404255319

How do I get it to just say 97.87%? Can I format the number within the formula? Or how can I get the string formula in C10 to return just the displayed contents from C4?

Thanks in advance!

I am having a problem with validating a decimal point for my formated percentage cells.

When I have a regular number format, I can limit the decimal to 4 places (0.0001). When I change the format of the cell to a percentage, the decimal validation no longer works.

Example: .0555555 is not allowed under a number format but 5.55555 is allowed under a percentage format.

How do I get the validation to limit a percentage to 4 decimals (example:5.55%)?


I have a series of worksheets (~70) in which there columns that contain cells with what should be percentages reflecting efficiency. The problem is that the numbers were not entered in decimal format, so when I try to format the row as a percentage, the numbers are multiplied by 100, which is not what I want. All I want is to get a percentage sign to appear next to the numbers in these cells.

I have tried using a formula to convert the numbers to decimal form so that I could then format the cells as percentages, but then I have one row with percentages and another with numbers and no percentage signs, which is not ideal at all.

Does anyone know of a way that I can simply get a percentage sign to show up next to a number in a cell? Keeping in mind that I will have to do this to MANY worksheets and spreadhseets.

Thank you in advance


In cells A1, A2 and A3, I have three values formatted in percentage format. In cell B1, I type in a formula to calculate the average of the three, and it automatically gives the result in percentage format. I want this as a general number so I change it manually. The problem is that everytime I change the cell reference in the formula of B1, Excel automatically reverts back to the percentage format.

How can I stop this from happening? Thanks.

I'm trying to change the decimal format from number format to percentage format. The problem is that all of the numbers are already typed in number format. For instance, I just want to change the format of 17.31 to 17.31% without having to retype in the number once the format is changed to a percentage format. Can it be done?

OK, this might be an easy question for someone other than myself. I am a beginning excel user who is having a difficult time getting my spreadsheet to do right.

I am multiplying a salary by a percentage. I have the destination column for the answer in accounting format so it will will only display 2 numbers to the right of the decimal point and put the dollar sign in etc.

However, when I go to sum all of the entries in the destination column, it gives be a figure but it is usually off by 1-2 cents.

I figure that it has something to do with a rounding issue.

Can anyone give me an example formula of how to multiply a column X a percentage & round off the resulting number to 2 digits to the right of the decimal point so when I go to add the column it gives me the correct number??



I have a really simple problem that I have not been able to figure out. I have a column that contains many rows of data that is in decimal format. I want these numbers that are in decimal format to be displayed as percentages rather than decimals, but without the percentage sign showing up, so really I just want to multiple this data by 100 but without creating an extra column of data. Is there a custom format I can use for this? I have not been able to figure this out.

Thanks for any help!


I have a formula that computes for a percentage. What i want is to format the cells where negative percentages should appear with parenthesis i.e.

from: -1.20%
to: (1.20%)

Just like the accounting format or its like combinng the percentage and accountancy format.



I am using concatenate formula but one of the cells is a percentage value, when I concatenate the values the percentage value appers in other format (decimal)... how can i make that the value in my formula appers in "percentage" format?

Dear Friends,

Best wishes!!!

In a pivot table a percentage column is added which calculates the percentage automatically. The percentage column shows the value with "%" (Ex. 54.12%). I tried different possibilities, but in vain.

How to avoid / re-format this to show only the value (i.e. I need only the value 54.12 - instead of 54.12%)

Thanks in advance,


I am trying to add the percentage symbol to a column of numbers, but I do not want Excel to convert it to percentage format. My purpose for this is to use it on data that comes in from an excel plug-in, so I can't change the format it is imported in. For example, it pulls 3.5, and I would like this to read 3.5%. Of course, when I make it a percentage, the number changes to 350%.

I know I could divide by 100, but I'd prefer something I can do at the click of a button (that doesn't involve a macro). I tried doing a custom format such as "#.#%", but even that changes the number to 350%.

This seems like it should be very easy, but I can't figure it out, even after lengthy Googling. Can anyone help?

I have a report which has given me a $ amount as a percentage, i have changed it to become a percentage however it has added two numerical places and for some reason i cant change this when i am in the format cells section.

At the moment the percentage looks like this: 500% but it should be only 5%, can anyone help?.

Hello all,

I have an unusual problem with Excel 2010. I have a file created with Excel 2003 with percentages and money values and it all looks fine in 2003. When I open it in 2010 it does not show the percentage, but shows me 0.9540 instead of 95.40%. Cell format is set to percentage and 2 decimals and in the formula bar it shows 95.40%.

I tried saving the file in 2010 format, reentering the percentage, creating custom format for the cell, all with no luck.

If I try to change the font, it displays all the cells as percentage, but when I close the font selection or when I change the font, it goes back to displaying 0.9540. Any ideas?

I tried to do a custom formatting but it doesn't make sense. This is what I
want to do, the users of this spreadsheet are entering percentages into the
cells. The percentages have a range from 0.5% to 5%. If we format the
percentage to 2 places and we enter a 1 in then it comes up as 1% but if we
enter in .51% as .51 then it becomes 51%. How I would like to format the
cell is so that what ever whole or decimal number that is entered is shown
as a percentage i.e. if I type in 1 it will show 1% and if I type in .51 it
shows as .51%.

Is there any way to do this?

Can somebody help with a formula for the below?

I have the following information, and want to calculate the percentage complete based on a value in column a.

So column a contains a task number, and column I contains the percentage complete for the task:
1 20%
2 10%
1 5%
1 100%
3 50%
2 25%
4 9%

I want a formula in a different sheet to provide the total percentage complete for each task. So, where column A = 1, bring back a percentage (so adding up all percentages for every "1" task and dividing by the number of "1" tasks.

Hope that makes sense.
Thanks in advance....


I need a formula or cell format for this. that is, I have a percentage cells from A5:K5. Answer is like this "19.15%", "21.25%" etc.... I don't want to display "%" sign, but i need the same answer "19.15%", "21.25%". etc.... and i need last coloumn in percentage format with sign. How can i do it.

Thank you

I am using an index formula to look up values. Depending on what the user selects the value could be a date, number, percentage, text, whatever...
My question is this, How can I get it to mimic\inherit the format of the cell\value it indexes? Meaning if it pulls in a Percentage it needs to be formatted as such and the same for any other type of value.

Hi there

This seems like it shoudl be really simple but I have not been able to work out how to do this despite searching Help etc.
I have a column of data that contains various whole numbers e.g.

33 144 107 34 12 330 10 340

I need to adjust the numbers so that they all show as a percentage of their current value e.g. 25%. Is it possible to do this without having to go through and apply a percentage formula to each cell individually? If I apply the percentage formula i.e. =33*.25 to the first cell and then copy it down to the rest of the cells using Ctrl + D I get the following result:

8 8 8 8 8 8 8 8

To save time I really need to be able to do this without having to create a second column with formulas referring the first column of data as below:

33 8 144 36 107 27 34 9 12 3 330 83 10 3 340 85

If anyone can help it would be much appreciated.

Thank you.

Sue P


I would like the formula that will compare two numbers and give me the
percentage in the third column.

ex C4 compared to B4 put in cloumn D4 as a percentage,

New to excel and need to know how to put the formula in the cell too?
thanks for hel p in advance
Mike B

I have an excel sheet containing certain range whose number are in percentage format, but i want to write a formula in each cell so that if one cell (eg. A1) 's value equal to 1, certain range's format would be changing from percentage to normal number automatically, how can i do that? Thanks

I have an excel sheet containing certain range whose number are in percentage format, but i want to write a formula in each cell so that if one cell (eg. A1) 's value equal to 1, certain range's format would be changing from percentage to normal number automatically, how can i do that? Thanks

One of the spreadsheets I use is meant to calculate a total percentage adding up to 100%. The numbers that are added to make that percentage are created from a formula, and though their decimal place visibility is limited to between three and five, the other numbers on the tail end that aren't seen are throwing off the percentage. Is there a way to limit the number of sig figs that Excel uses to calculate with?

Thanks in advance for any help.


I am using Excel to calculate student results. The first screenshot shows an example of the data that I am using. Cell G3 contains the total marks available and cells G6:G9 contain the marks achieved. In cell H6 I have generate a formula to work out the percentage =G6/$G3. I then format this to a percentage style of 0 decimal place. When I use this as a data source in Word, it then displays the results as numbers, eg 92% becomes 0.92 or even 0.666666673. I can get around the problem but using this function =ROUND(G6/$G$3*100,0) and simply inserting the % sign into the Word document but I feel there must be a better way of doing this. I have also tried using Paste Special to replace the formula with values but this is not ideal as the result sometimes change so I need to remember to fix it all up.

If anyone can come up with a really good fix for this problem I would be most grateful.

Shirley Munro

I feel like I'm losing my mind because I've never had this happen to me before. I'm trying to format a column as dates. Simple as that. I formatted the column before typing anything in, and then I typed in 6/9/2011 in the first cell and it displays as 40703. It shows 6/9/2011 in the formula bar, but no matter what I do, I cannot get it to format the column as dates.

Oh, and it won't format it correctly as any other format, either. If I format it as a number with two decimal places, it still displays 40703. If I format it as a percentage, it displays 40703. If I format it as time, it displays 40703 (6/9/2011 12:00:00 in the formula bar).

Are there pixies in my computer? Why in the world would this happen?

=(N54+N55)/N59 & AN55
=(N54+N55)/N59 & "Help Please"

Have the above concatenation and want the calculation to spit out in a 1 decimal % format,

so that the calculation would produce 82.7% Help Please

any thoughts on a percentage format formula?