0

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!

5

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.

5
  • 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 '15 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 '15 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 '15 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 '15 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 '15 at 20:14
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

8
  • i just tried this in Excel 2010. It doesn't work. – jo99blackops Nov 11 '15 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 '15 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 '15 at 1:00
  • I type: '5.6 and I get this: s21.postimg.org/w94rfcqnr/temp.png – jo99blackops Nov 11 '15 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 '15 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.