Formatting numbers in excel so they are in parentheses

Question

I want to format cells in Excel so the numbers the cells contain are displayed in parentheses (both negative and positive numbers). Examples:

(4.54)

(-2.32)

The cells I want to format this way contain formulas. This seems to be the reason why custom formatting doesn't work.

Thanks!

Mike Fitzpatrick · Accepted Answer · 2015-11-11 00:56:26Z

6

You want a custom number format:

"("0.00")";"("-0.00")"

This defines the format for positives "("0.00")" and another for negatives "("-0.00")"

You could optionally provide a third format for zero value but if you don't specify it then zero will be formatted using the positive format.

answered Nov 11, 2015 at 0:56

Mike Fitzpatrick

17k4 gold badges48 silver badges49 bronze badges

This works great. Now just find out the version of excel that the OP is using and gives steps on how to do this.
– jo99blackops
Nov 11, 2015 at 1:02
This works perfectly for when cells contain values, but doesn't seem to work when they contain formulas. Any idea how to deal with that?
– F Bert
Nov 11, 2015 at 10:12
@FBert - this should work the same for both constants and formulas? Do your formulas results numbers formatted as text? Please update your question with some sample formula you us.
– Máté Juhász
Nov 11, 2015 at 10:35
Right - my formulas reference to another sheet. that sheet however contains numbers in the following way: ="4.44". I guess the "" are causing the problem
– F Bert
Nov 11, 2015 at 14:26
@FBert, yes, ="4.44" is a text value, not a numeric value so the custom number format as specified will not be applied. If you want to place parentheses around text values as well then you can supply a full custom format for positives, negatives, zero and text, which would be "("0.0")";"("-0.0")";"("-0.0");"("@")" but I recommend you to use genuine numeric values wherever possible and your overall Excel experience will be much more productive.
– Mike Fitzpatrick
Nov 11, 2015 at 20:14

Add a comment |

tyelford · Accepted Answer · 2015-11-11 00:54:04Z

0

Add a single quote before you type the value into the cell

'(4.54)
'(-2.32)

Then press enter and the single quote will be removed and the literal value you typed in will remain

answered Nov 11, 2015 at 0:54

tyelford

4402 silver badges7 bronze badges

i just tried this in Excel 2010. It doesn't work.
– jo99blackops
Nov 11, 2015 at 0:54
@jo99blackops What output do you get? I've been using this for years in all versions of excel and it's worked. I am currently on 2016 however.
– tyelford
Nov 11, 2015 at 0:56
The answer works but is not very useful because the numbers are converted to text and can no longer be used for calculations.
– Mike Fitzpatrick
Nov 11, 2015 at 1:00
I type: '5.6 and I get this: s21.postimg.org/w94rfcqnr/temp.png
– jo99blackops
Nov 11, 2015 at 1:00
@jo99blackops, OP was suggesting to type '(5.6) including the parentheses. It works under all versions of Excel but converts the result to a string of text, no longer a numeric value.
– Mike Fitzpatrick
Nov 11, 2015 at 1:10

| Show 3 more comments

2 Answers 2