Excel 2010: Conditional Formatting Accounting Cells 1

[pendle666]

Hello

I've a large spreadsheet with lots of figures - positive and negative ones.

They're formatted as Accounting cells because it's a lot easier on the eye to look at rows of £- and £123.04 rather than a mixture of £0.00 and £123.04.

I'd like any negative figures to be in red. This can be done when formatted as numbers or currency, but it means that when there is a zero figure it gives me a 0.00 rather than a dash.

I've tried using conditional formatting to make my negative figure red, but it means that all £- are red too and I don't want that.

Can anyone help?

thank you for helping

____________
Pendle

 

[SkipVought]

HI,

What CF FORMULA did you use that made zero values RED?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

=$B$30<0 is what I've used.

thank you for helping

____________
Pendle

 

[strongm]

Why would you use conditional formatting? Why not just format the cells with a Custom format rather than Accounting: [tt]£#,##0.00;[Red]£#,##0.00;£-[/tt]

 

[SkipVought]

=$B$30<0 works for the value in ONLY cell B30: no other cell!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

I know that SkipVought, but if the value was nil it would still be in red and I don't want that.

thank you for helping

____________
Pendle

 

[SkipVought]

Did you try strongm's custom format?


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

Strongm: I have tried that and it works, but it just gives the layout like currency - thank you.

However, it just gives the currency layout rather than the neatness of the accounting layout - sounds picky but when you're sitting looking at a mass of figures each day lol

thanks anyway

thank you for helping

____________
Pendle

 

[SkipVought]

Try looking at the custom format for Accounting and compare to the custom format for Currence.

Then see if you can adapt.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

You can quite happily duplicate the Accountancy format (it after all just a predefined custom format).

[tt]_-£* #,##0.00_-;[Red]-£* #,##0.00_-;_-£* "-"??_-;_-@_-[/tt]

If you want to customise predefined formats yourself, simply pick a cell, apply the predefined format to it, then switch to custom format. The predefined format will be copied to the custom format, and you can then edit it.

 

[pendle666]

Great minds think alike as this is what I've just been doing:

_(£* #,##0.00_);[Red]-£* #,##0.00_)

Is the one that I've got so far, but yours strongm gives me the

£ -

look when there's a formula in the cell, which was giving me 0.00 with mine.

Thank you very much!

thank you for helping

____________
Pendle