Cellwise Start free

Excel guide · 4 min read

How to show negative numbers in red in Excel

Quick answerSelect the cells, press Ctrl+1, choose Number (or Currency), and pick a red option in the Negative numbers list. For more control, choose Custom and type a format such as #,##0;[Red]-#,##0, where the part after the semicolon is how negatives look. Only the display changes: the value stays negative, so SUM, SUMIF and comparisons still work.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEF
1LineBudgetActualVariancePasted adjustment
2Salaries5200053400-1400-250
3Rent800080000
4Marketing65007900-1400
5Software32002950250
6Travel25003150-650
7Training18001200600
8Utilities14001530-130
9Supplies900640260

Download this sheet as an Excel practice file (.xlsx)

Method 1: the built-in red option (fastest)

The sample is a monthly budget check. Variance in column D is budget minus actual, so a negative number means the line went over budget. Four lines are negative, and they should jump out.

  1. Select D2:D9.
  2. Press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells.
  3. On the Number tab, click Number or Currency in the Category list.
  4. Set Decimal places (0 here) and tick Use 1000 Separator if you want commas.
  5. In Negative numbers, pick one of the red options and click OK.

Watch out: red without a minus sign

One of the red options shows the number in red with no minus sign at all: -1,400 appears as a red 1,400. On screen that's fine. Printed in black and white, or read by someone who can't easily tell red from black, an overspend looks like an underspend. Pick the red option with a minus sign or parentheses instead.

If the parentheses options aren't in the list, Microsoft notes that it can be caused by an operating system setting: the negative number format in your regional settings. You don't need to change Windows to fix it; a custom format (below) gives you parentheses anyway.

Method 2: a custom format, section by section

A number format can have up to four sections separated by semicolons: positive; negative; zero; text. You can give each section a color in square brackets, and the color has to come first in its section. Microsoft's guidelines list eight color names, including [Red], [Blue], [Green] and [Black].

So #,##0;[Red]-#,##0 means: positives with a thousands separator, negatives the same but in red with a minus sign. Some formats worth copying:

  • #,##0;[Red]-#,##0 shows red with a minus sign: -1,400.
  • #,##0;[Red](#,##0) shows red in parentheses, the usual accounting look: (1,400).
  • #,##0_);[Red](#,##0) does the same, and the _) adds a space the width of a bracket after positives so the digits line up.
  • #,##0;[Red]-#,##0;"-" adds a third section, so the Rent line's 0 shows as a dash.
  • $#,##0.00;[Red]-$#,##0.00 is the currency version, and 0.0%;[Red]-0.0% works for percentages.
  • [Blue]#,##0;[Red]-#,##0 colors both: under-budget lines blue, overspends red.
  1. Select D2:D9 and press Ctrl+1.
  2. Click Custom at the bottom of the Category list.
  3. Replace whatever is in the Type box with your code, for example #,##0;[Red](#,##0).
  4. Check the Sample above the box, then click OK.

Method 3: conditional formatting (fills, bold, or the whole row)

A number format only colors the font. When you want a red fill, bold text, or the whole row highlighted, use conditional formatting. It's also easier for someone else to find and change later: Home > Conditional Formatting > Manage Rules lists every rule.

  1. Select D2:D9.
  2. Home > Conditional Formatting > Highlight Cells Rules > Less Than.
  3. Type 0 in the box, pick Light Red Fill with Dark Red Text (or Custom Format), and click OK.
  4. For the whole row: select A2:D9, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =$D2<0, and pick a format.

The value doesn't change, so formulas still work

Formats change how a number looks, not what it is. The red (1,400) in D2 is still -1400, so totals and counts work normally. That's the big advantage over typing brackets yourself or using ABS to hide the sign: see make negative numbers positive for when you really do want to flip the value.

=SUMIF(D2:D9,"<0")

Result: -3580

-3580: the four red lines add up to 3,580 over budget, even though they're displayed in red with brackets.

How many lines are over budget?

Count the negatives directly; the format makes no difference.

=COUNTIF(D2:D9,"<0")

Result: 4

4 lines are negative.

Does the total still net out?

Yes. SUM uses the real values.

=SUM(D2:D9)

Result: -2470

-2470: 1,110 under budget on four lines minus 3,580 over on the others.

When a negative number won't turn red

The usual reason is that it isn't a number. Values pasted from a bank export, a PDF or another system often arrive as text, like the -250 in F2. Text ignores number formats, so no red, and SUM skips it too. A green triangle in the corner, left alignment, or the formula below gives it away. Convert it with Data > Text to Columns > Finish, or follow numbers stored as text.

Other causes: a conditional formatting rule is overriding the font color (check Manage Rules), the cell is formatted as Text, or the format you typed has the color after other characters instead of at the start of its section.

=ISNUMBER(F2)

Result: FALSE

FALSE: the pasted -250 is text, which is why the red format does nothing to it.

Common mistakes

Choosing the red option with no minus sign

It prints as a plain positive number in black and white. Use a minus sign or parentheses as well as red.

Typing the color in the wrong place

The color comes first in its section: #,##0;[Red]-#,##0, not #,##0;-#,##0[Red].

Expecting a red format on text values

Convert numbers stored as text first; formats only apply to real numbers.

Using ABS or typed brackets to show losses

That changes or hides the value. Use a format so formulas still see the negative.

FAQ

How do I make negative numbers red in Excel automatically?

Select the cells, press Ctrl+1, choose Number and pick a red option under Negative numbers. Any negative entered or calculated there later turns red on its own.

What is the custom format for red negative numbers in parentheses?

#,##0;[Red](#,##0) for whole numbers, or #,##0.00;[Red](#,##0.00) with two decimals. Add _) to the positive section to line the digits up.

Why is the parentheses option missing for negative numbers?

Microsoft says it can be caused by an operating system setting. Use a custom format such as #,##0;[Red](#,##0) instead.

Can I make the whole row red when a number is negative?

Yes, with conditional formatting: New Rule > Use a formula, enter =$D2<0 for the first row of your selection, and choose a fill.

Functions used

Related guides

Updated