Show minus figures with brackets

[Guest]

Good afternoon all

I have a colleague who is extremely new to Excel and when he types a
negative value into a cell, he wants it to show brackets rather than the
minus sign. I realise this can be done by applying a Custom Number Format,
however, as he is a complete beginner with Excel, I have a feeling this will
be too confusing for him.

Is there another way that he can apply this formatting rather than setting
up a custom number format?

Thank you.

Louise

 

[Bob Phillips]

No. You could put

=TEXT(A1,"#,##0;(#,##0)")

in another cell, but isn't this just as confusing?

 

[Dave Peterson]

Can your colleague use a built in format?

Format|Cells|number tab
Number category

There are options how to show negative numbers (red/with a minus/inside
parentheses).

If you don't see the parentheses option, you/your colleague can toggle a windows
setting.

Windows start button|settings|control panel|regional settings
(Or regional and language settings in winXP)

Regional Options tab, customize button in winxp

Currency Tab
change the negative currency format to show ()'s.

 

[Guest]

Hi Dave

Thanks for your reply. I have gone into the Regional Settings, however, on
the Currency tab I don't have an option for Negative numbers, it simply says
Number, Currency and Date??

Thanks.

Louise

 

[Guest]

Hi Louise,

When you get to Currency, click on Customize, which is right next to
Language, and go doen the list to Negative Number Format (5th from the top,
4th from teh bottom)

 

[Guest]

Hi

Really sorry about this but it doesn't seem to have worked??

I've put () in the negative section and the negative number format is now
(1.1). Any idea why it's not working??

Thank you.

Louise

 

[Guest]

Hi Louise

Either exit Excel, and go back in, or reformat the affected cells as
number/currency, an d re-enter the data

 

[Petitboeuf]

Hi Louise

Here are some examples that might help you.

First select the cell or cells you what to reformat.

Then go to Format > Cells...
Select the Number tab then go right down the list to Custom.

For currency you have 3 default preloaded:

£#,##0;-£#,##0
which would give you: £1,000 or -£1,000

£#,##0;[Red]-£#,##0
which would give you: £1,000 or -£1,000 (in red)

£#,##0.00;-£#,##0.00
which would give you: £1,000.00 or -£1,000.00

£#,##0.00;[Red]-£#,##0.00
which would give you: £1,000.00 or -£1,000.00 (in red)


The formula you are after is the following:
£#,##0.00;(£#,##0.00)
which would give you: £1,000.00 or (£1,000.00)

You will need to type is manually in the Type: box.

You can pretty much choose whatever suits you best based on the way you
want it dipslayed:

£#,##0;(£#,##0)
which would give you: £1,000 or (£1,000)

£#,##0;£(#,##0)
which would give you: £1,000 or £(1,000)

£#,##0.00;(£#,##0.00)
which would give you: £1,000.00 or (£1,000.00)

£#,##0.00;£(#,##0.00)
which would give you: £1,000.00 or £(1,000.00)

You can even combine parentheses and colours:
£#,##0;[Red](£#,##0)
which would give you: £1,000 or (£1,000) (in red)

Hope this helps!