Need to amend a formula to show negative numbers in red?

First of all, I need to apologize to JohnSun and erAck for answering a question back on March 17th.
Thank You Both for answering my question, I should have Thanked You Back Then! May God Bless You Both!

I have a check register that shows negative numbers in black. The formula is as follows:

[>0]" $"* #,##0.00" “;[<0]” $"* (#,##0.00);" $"* -#" “;” “@” "

How can I amend this to return negative amounts in red?
Thanks. May God Bless You All! :latin_cross:

We are always happy to help. Just remember for future reference: The easiest way to express gratitude is to mark the answer to your question as correct.


Let’s see what the Number Format Codes Help says:

The first paragraph shows that we can set the color very easily with [RED]. The second paragraph explains that there is no need to set the conditions [> 0] and [<0] - it is enough to know that the first part of the format code will be applied to positive values, and the second to negative ones.

The easiest, fastest and most reliable way to solve your problem: press Ctrl+1 to open the Format - Cells - Numbers tab and check the Negative numbers in red

‘kiss’ answer (keep it simple and stupid):

try this: [>0]" $"* #,##000" ";[<0][ROT]" $"* (-#,##000);" $"* -#" ";" "@" "

observe that i added a ‘-’ sign, delete if not wanted,

probably you need to replace ‘[ROT]’ - german by ‘[RED]’ - english,

have fun

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …


you could use the Conditional Formating under

Formating > Conditional > Condition....

Here you can use the Error Style (or any other style you like), wenn the cell value is less than zero.

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let’s (continue to) “Be excellent to each other!”

I believe my solution is the easiest. The first formula is what I use in my checking account. It shows positive amounts in Black and negative amounts in Red. Notice my formula does not use Dollar Signs ($).

However, the formula with Dollar Signs is the second formula. I personally dislike the second formula because the amounts are aligned to the right with the Dollar Signs. The view looks weird with the $ signs not aligned. That is the reason I use the first formula. Since the colors of Black and Red designate positive and negative numbers, the $ sign is not needed. (In My Opinion.) My God Bless Each and Every One!

[>0][BLACK]" “#,##0.00” “;[<0][RED]” “#,##0.00” “;”"

[>0][BLACK]"$ “#,##0.00” “;[<0][RED]”$ “#,##0.00” “;”"

You can even simplify as " "#,##0.00" ";[RED]" "#,##0.00" ";" "0.00" "

It is safer to add a format code for 0. In your case with [>0] and [<0], null values are suppressed. So, unless this is the intended action, better add something for the zeroes.