Ask Your Question

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

asked 2020-09-09 15:43:07 +0200

MrHoggLegg gravatar image

updated 2020-09-09 15:44:40 +0200

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! ✝️

edit retag flag offensive close merge delete



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.


JohnSUN gravatar imageJohnSUN ( 2020-09-09 16:34:59 +0200 )edit

4 Answers

Sort by » oldest newest most voted

answered 2020-09-09 16:07:27 +0200

igorlius gravatar image

updated 2020-09-09 16:09:41 +0200


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.

Conditional Formatting

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!"

edit flag offensive delete link more

answered 2020-09-09 18:37:53 +0200

newbie-02 gravatar image

'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 ...

edit flag offensive delete link more

answered 2020-09-09 16:06:31 +0200

JohnSUN gravatar image

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


edit flag offensive delete link more

answered 2021-04-03 20:37:34 +0200

MrHoggLegg gravatar image

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" ";""

edit flag offensive delete link more


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.

ajlittoz gravatar imageajlittoz ( 2021-04-03 20:44:24 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-09 15:43:07 +0200

Seen: 103 times

Last updated: Apr 03