Excel guide · 4 min read
How to show negative numbers in red in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Line | Budget | Actual | Variance | Pasted adjustment | |
| 2 | Salaries | 52000 | 53400 | -1400 | -250 | |
| 3 | Rent | 8000 | 8000 | 0 | ||
| 4 | Marketing | 6500 | 7900 | -1400 | ||
| 5 | Software | 3200 | 2950 | 250 | ||
| 6 | Travel | 2500 | 3150 | -650 | ||
| 7 | Training | 1800 | 1200 | 600 | ||
| 8 | Utilities | 1400 | 1530 | -130 | ||
| 9 | Supplies | 900 | 640 | 260 |
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.
- Select D2:D9.
- Press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells.
- On the Number tab, click Number or Currency in the Category list.
- Set Decimal places (0 here) and tick Use 1000 Separator if you want commas.
- 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.
- Select D2:D9 and press Ctrl+1.
- Click Custom at the bottom of the Category list.
- Replace whatever is in the Type box with your code, for example #,##0;[Red](#,##0).
- 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.
- Select D2:D9.
- Home > Conditional Formatting > Highlight Cells Rules > Less Than.
- Type 0 in the box, pick Light Red Fill with Dark Red Text (or Custom Format), and click OK.
- 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.
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.
Result: 4
4 lines are negative.
Does the total still net out?
Yes. SUM uses the real values.
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.
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
- How to hide zero values in Excel
- How to make negative numbers positive in Excel
- How to combine text with numbers and dates in Excel
- How to convert numbers stored as text in Excel
- How to calculate budget vs actual variance in Excel
- How to calculate net worth in Excel
Updated