# Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign

Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign

Absent from Excel’s standard negative number formats is one that colors negative numbers red with a preceding negative (minus) sign. You need a custom format to make the Before picture look like the After picture, as shown below.

Select the range of interest, right-click the selection, and click Format Cells.

In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list, select Custom.
• In the Type field, enter `#,###;[Red]-#,###;0`
• Click the OK button.

###### 19 comments on “Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign”
1. Dean Brown says:

Thanks – works perfectly.

2. Daanish says:

Is there some way to bookmark that format?

• Tom Urtis says:

Normally when you add a custom format, it gets placed in your custom list, at the bottom.

3. Chris says:

Is there a way that this could be used with a decimal instead of whole numbers? ie. -.50

• Tom Urtis says:

Perhaps this if you are working with positive and negative decimals:

#,###.##;[Red]-#,###.##;0

4. Dan says:

We did you put 0 at the end ?

#,###.##;[Red]-#,###.##;0

• Tom Urtis says:

For the purpose of a zero to look like a zero.

• Dan says:

Thank Tom.

• Dan says:

In your previous post, I saw you used the formatting -> #,##0.0,, \M and it worked well.
But if I put -> #,##0.0,,;”M” it doesn’t take M.

Why it doesn’t take M ?

• Tom Urtis says:

Take a closer look. The semicolon characters (not commas as you have it) are what separate positive numbers ; negative numbers ; zero ; text

5. CO Bill says:

Works great – – – many thank yous

6. Sanjay says:

i’m using the following formula, =IF(E55%,”p”,”t”&”u”)), to format cells using the wingdings3 font for up and down arrows. The formula works fine, but does someone know how I can change the color of the arrow? I need to create a distinct formulas per cell because sometime a positive value may mean a green arrow and sometimes it may mean a red arrow, hence I want to write a formula per cell.

• Tom Urtis says:

Would not Conditional Formatting achieve the visual result you want for positive (green) and negative (red) numeric formula results?

7. Michael Wise says:

Works well, but I would like whole numbers to include one decimal place, i.e. “15.0”, and decimals to include a leading zero, i.e. “0.7”

• Tom Urtis says:

I’m just doing this an as air quote, away from Excel but instead of this
#,###;[Red]-#,###;0
you could do this
#,##0.0;[Red]-#,##0.0;0

• Michael Wise says:

You’re my hero. That’s exactly what I needed.

8. Alastair Wrigley says:

Thx for this. Can I make this formula (ie having all negative numbers shown in red and with a negative sign – eg -£15.50 (but in red) my default one for all my excel worksheets? Thx

• Tom Urtis says:

It is not a formula, it is a custom format.
The custom format for what you want is
£#,###.00;[Red]-£#,###.00;0
Set a cell with that custom format in a new fresh workbook.
Press Alt+F11 then Ctrl+G. Type this in…
? application.StartupPath
…and then press Enter.
That is the path holding your XL Start folder.
Hit Alt+Q.
SaveAs that workbook. From the “Files of type” drop-down list, select “Excel Template (*.xltx)” and change the file name to “Book.”
Set the “Save in” location to the XLSTART folder. This folder is located in the path returned in the Immediate window.
Click Save.
Quit and re-open Excel. The blank workbook should contain the formatting you previously set.