45

When I set format of a cell as %, it automatically multiply the value with 100 & show the value. So 5.66 becomes 566 %.

I want to apply % format on a column but want to keep the values same as before. I just want % sign to be shown along with the value. To achieve this, I used following format 0.00##\%

Now I want to show negavtive values in RED color without '-' sign(negative) and the values should be wrapped in brackets. so -5.66 should be shown as (5.66%) in red color.

Please help

SharpCoder
  • 18,279
  • 43
  • 153
  • 249

7 Answers7

67

You just have to change to a Custom format - right click and select format and at the bottom of the list is custom.

 0.00##\%;[Red](0.00##\%)

The first part of custom format is your defined format you posted. Everything after the semicolon is for negative numbers. [RED] tells Excel to make the negative numbers red and the () make sure that negative number is in parentheses.

AxGryndr
  • 2,274
  • 2
  • 22
  • 45
  • very good answer - works perfectly! This solution is very helpful because getting a number (result of a sum) to display as a proper percentage was previously quite frustrating .. – tm_forthefuture Mar 09 '14 at 17:38
  • 11
    To clarify: in custom formats, `%` means "add % and multiply by 100", **while `\%` means "just add %"**. In general, \ means "turn off any fancy stuff the next character does and just read it as a plain character" (this is sometimes referred to as "escaping" the next character) – user56reinstatemonica8 Jun 23 '15 at 10:04
  • Thanks for the answer. – bot Mar 28 '16 at 03:48
  • Perfect Explanation @user568458.I Wish this was posted in Answer and i could upvote it from multiple account – Anjani Sep 21 '16 at 11:26
14

Be aware that a value of 1 equals 100% in Excel's interpretation. If you enter 5.66 and you want to show 5.66%, then AxGryndr's hack with the formatting will work, but it is a display format only and does not represent the true numeric value. If you want to use that percentage in further calculations, these calculations will return the wrong result unless you divide by 100 at calculation time.

The consistent and less error-prone way is to enter 0.0566 and format the number with the built-in percentage format. That way, you can easily calculate 5.6% of A1 by just multiplying A1 with the value.

The good news is that you don't need to go through the rigmarole of entering 0.0566 and then formatting as percent. You can simply type

5.66%

into the cell, including the percentage symbol, and Excel will take care of the rest and store the number correctly as 0.0566 if formatted as General.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Thank you for making me aware of the risk but my application users want data in this format. I will make them aware of this risk. – SharpCoder May 29 '13 at 08:34
  • 1
    The format your users want can be achieved by entering the number, followed by a % sign. Then you don't need any data manipulation. A little user education goes a long way. If they want to see 5.66%, then tell them to enter 5.66% with the % sign and all is well. – teylyn May 29 '13 at 09:14
12

Pretty easy to do this across multiple cells, without having to add '%' to each individually.

Select all the cells you want to change to percent, right Click, then format Cells, choose Custom. Type in 0.0\%.

Andy
  • 4,783
  • 2
  • 26
  • 51
Dan
  • 129
  • 1
  • 2
3

This worked better for me as it turns 0 into 0% (no decimals).

I just use this format code, from excelhow.net:

0\%
vn2021
  • 83
  • 8
0

In Excel workbook - Select the Cell-goto Format Cells - Number - Custom - in the Type box type as shows (0.00%)

suhas
  • 9
  • 1
0

Here's a simple way:

=NUMBERVALUE( CONCAT(5.66,"%") )

Just concatenate a % symbol after the number. By itself, this output would be text, so we also tuck the CONCAT function inside the NUMBERVALUE function.

p.s., in old excel, you might need to type the full word "CONCATENATE"

fcdt
  • 2,371
  • 5
  • 14
  • 26
Mitchell Miller
  • 21
  • 2
-5
_ [$%-4009] * #,##0_ ;_ [$%-4009] * -#,##0_ ;_ [$%-4009] * "-"??_ ;_ @_
Makyen
  • 31,849
  • 12
  • 86
  • 121
Murtuza
  • 1
  • 4
    Please [edit] in an explanation of why/how this code answers the question? Code-only answers are discouraged, because they are not as easy to learn from as code with an explanation. Without an explanation it takes considerably more time and effort to understand what was being done, the changes made to the code, if the code answers the question, etc. The explanation is important both for people attempting to learn from the answer and those evaluating the answer to see if it is valid, or worth up voting. – Makyen Feb 25 '15 at 05:06