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.
Thanks – works perfectly.
Thank you, Dean.
Is there some way to bookmark that format?
Normally when you add a custom format, it gets placed in your custom list, at the bottom.
Is there a way that this could be used with a decimal instead of whole numbers? ie. -.50
Perhaps this if you are working with positive and negative decimals:
#,###.##;[Red]-#,###.##;0
We did you put 0 at the end ?
#,###.##;[Red]-#,###.##;0
For the purpose of a zero to look like a zero.
Thank Tom.
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 ?
Take a closer look. The semicolon characters (not commas as you have it) are what separate positive numbers ; negative numbers ; zero ; text
Works great – – – many thank yous
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.
Would not Conditional Formatting achieve the visual result you want for positive (green) and negative (red) numeric formula results?
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”
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
You’re my hero. That’s exactly what I needed.
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
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.