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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
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?

  3. Chris says:

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

  4. Dan says:

    We did you put 0 at the end ?

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

  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.

  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”

  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.

Leave a Reply

Your email address will not be published.

*