How to Color Negative Values in Excel Automatically

How-to basics · Updated November 20, 2026

To color negative values in Excel, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Less Than, enter 0, and pick a format. Excel will apply the color to negative numbers in the selected range, including numbers calculated by formulas, and update the formatting when values change. You can use a built-in red style or choose a custom font or fill color.

This guide is for Excel for Microsoft 365 on Windows. Conditional formatting is usually the best choice because it changes the appearance without changing the stored values. If you only want negative numbers shown in a different font color, a custom number format is another option.

What You’ll Need

  • An Excel workbook with the values you want to format. The values must be numbers, including numeric results returned by formulas, rather than numbers stored as text.
  • The range of cells to format. You can select a column, a rectangular block, or a smaller group of cells.
  • A color choice, such as red font, a colored cell fill, or both. Excel includes ready-made styles, and you can set a custom color.

Step-by-Step

  1. Open the worksheet and identify the data range. Find the cells that contain the values you want to check. If a column has a heading, select the data cells below it rather than the heading, unless you also want the heading included in the rule. For example, if monthly balances are in column D from row 2 through row 100, select D2:D100. Including only the relevant cells makes the rule easier to understand and avoids coloring unrelated numbers elsewhere in the sheet.
  2. Select the cells. Click the first cell and drag to the last cell, or click the first cell, hold Shift, and click the last one. To format a full column, click its letter at the top of the worksheet. You can also select separate, nonadjacent ranges by holding Ctrl while selecting each range on Windows. On a Mac, hold Command while selecting separate ranges. If the worksheet is an Excel table, selecting the data cells in the relevant table column is often convenient; new table rows generally inherit formatting rules applied to that column.
  3. Open the Less Than rule. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. In the dialog box, enter 0 in the value field. This sets the condition to values strictly below zero. Positive values and zero do not meet the condition, so they will not receive this formatting. The rule works on ordinary numeric entries and numeric results from formulas, such as a calculated profit or balance.
  4. Choose a quick color style. Use the formatting drop-down in the Less Than dialog to select a preset, such as Red Text or Light Red Fill with Dark Red Text. The preview shows how the selected style will look. Click OK to apply the rule. If the preset is enough, you can stop here: cells with negative values should now stand out, while other cells retain their existing appearance.
  5. Set a custom font or fill color if you prefer. In the Less Than dialog, open the formatting drop-down and choose Custom Format. In the Format Cells window, use the Font tab to choose a font color, or the Fill tab to choose a background color. You can set both if you want the negative values to be especially visible. Click OK to close Format Cells, then click OK again to create the rule. Choose colors with enough contrast to keep the numbers readable, particularly if the worksheet will be printed or viewed on a small screen.
  6. Check that the rule covers the intended cells. Click one of the formatted cells, then choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the Applies to range. It should match the cells you selected, for example =$D$2:$D$100. If it does not, edit the range in Applies to and select the correct cells. In the Show formatting rules for list, choose This Worksheet if you need to see rules that are not associated with the active selection.
  7. Test the result with a few values. Check a negative value, a positive value, and zero. A negative number should show the chosen color, while zero and positive numbers should not. If the cells contain live data, avoid replacing important values just to test the rule. You can test in a spare cell within the range and then undo the temporary entry, or use a copy of the worksheet. To change or remove the rule later, use Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule or Delete Rule.

Excel for Mac uses the same general ribbon path: Home > Conditional Formatting > Highlight Cells Rules > Less Than. The dialog wording can vary slightly by release, but the threshold and formatting choices work the same way. In Excel for the web, select the cells and use Home > Conditional Formatting to create or manage a rule; the available formatting controls may be simpler than in the desktop app. If you cannot find a custom color option in the web version, open the workbook in desktop Excel.

Common Mistakes

  • Entering a threshold other than zero. The Less Than rule compares values with the number you enter. If you type 1, Excel colors values below 1, including zero and some positive numbers. Enter 0 when your intention is to highlight only negative values.
  • Formatting the wrong range. A rule may work but be applied to a neighboring column, only one cell, or a range that excludes newly added rows. Review Applies to in Manage Rules, and expand the range when needed. For an expanding data set, using an Excel table can help keep the rule associated with the table column as rows are added.
  • Assuming negative-looking text is a negative number. A cell can contain text such as a dash followed by digits, or a number imported with an unexpected character. Conditional formatting evaluates numeric values, so text that merely looks negative may not be treated as a number. Check the cell by selecting it and reviewing the formula bar, and inspect how the data was imported.
  • Expecting the rule to change the value. Conditional formatting only changes how the cell looks. It does not convert a loss to a positive number, add a minus sign, or affect calculations. If a value is wrong, correct the underlying entry or formula separately.
  • Overlooking another formatting rule. A cell can have several conditional formatting rules, and their order or settings can affect the final appearance. If the color does not match your new rule, open Manage Rules and check for overlapping rules or a rule that stops other rules from applying.

Tips

  • Choose a color that communicates without obscuring data. Red font is easy to scan and leaves the cell background unchanged. A pale fill can make a whole row or column easier to review, but avoid a dark fill with dark text. If the figures are important, use a readable font size and strong contrast.
  • Apply the rule to formula results as well as typed values. Conditional formatting checks the value displayed by a formula. For example, if a cell calculates a difference and returns a negative number, it can be colored by the same Less Than 0 rule. You do not need to change the formula.
  • Decide whether blanks and zeros should remain uncolored. The Less Than 0 condition does not match either blank cells or zero. That is useful for many reports. If you have a separate requirement, create an additional rule rather than changing the negative-value rule to a threshold that catches unintended values.
  • Use a custom number format when font color alone is enough. Select the cells, press Ctrl+1 on Windows, choose the Number tab, select Custom, and enter #,##0.00;[Red]-#,##0.00;0.00 in the Type box. This displays positive numbers with two decimal places, negative numbers in red with a minus sign, and zero with two decimal places. It changes the display, not the underlying value. On a Mac, use Command+1 to open Format Cells. Conditional formatting is more flexible if you want a fill color, need a custom threshold, or want the rule to be managed separately from number display.
  • Keep rules simple and inspect them before sharing. A small number of clear rules is easier for another person to maintain. If you copy and paste cells from elsewhere, review the rules afterward: pasted formatting can bring in extra rules or modify the area a rule applies to.

Troubleshooting

Negative values are not changing color: First confirm that you entered 0 as the Less Than threshold and clicked OK in both dialog boxes if you used Custom Format. Then open Manage Rules and check that the selected rule applies to the cell you are looking at. If the value is stored as text, convert it to a number using a suitable method for the source data, then check the rule again. A warning icon may offer Convert to Number for some imported values, but confirm the result before applying it to a large range.

Positive values or zeros are also colored: Check the threshold and rule condition. A rule set to Less Than 1 will include zero, and a rule set to Less Than 0.01 will include zero and negative values. Also look for another conditional formatting rule on the same cells. In Manage Rules, review the rules for the worksheet or selected cells and edit or remove any rule that is adding a similar color.

The cells use the wrong color or fill: Edit the rule through Home > Conditional Formatting > Manage Rules > Edit Rule. Confirm the chosen preset or custom font and fill settings, then check the preview. If a cell still looks different, inspect other rules and any manually applied formatting. Conditional formatting can take precedence over ordinary font or fill settings while its condition is true.

New rows are not highlighted: Check whether the new rows fall inside the rule’s Applies to range. Extend that range in Manage Rules. If the data is a table, make sure the rule covers the table’s data column rather than just the rows that existed when you first created it. After expanding the range, enter or calculate a negative value in a new row to confirm that the formatting follows.

The result changes after copying or sorting: The rule may have been copied with a different Applies to range, or the cells may have picked up additional formatting rules. Use Manage Rules to inspect the target range and remove duplicates. Sorting a properly selected table should keep row values together, but check the results if you sorted only one column instead of the full table.

Frequently Asked Questions

Can I color negative numbers red without changing their values?
Yes. Conditional formatting changes the cell’s appearance only. The number remains negative, so formulas and totals continue to use its original numeric value.

Will this work when a negative value comes from a formula?
Yes. A Less Than 0 conditional formatting rule evaluates the formula’s result. If the result is negative, Excel applies the selected style; if the result later becomes zero or positive, the style no longer applies.

Can I use a color other than red?
Yes. Choose Custom Format in the Less Than dialog, then choose a font color on the Font tab or a background color on the Fill tab. You can also select another preset style from the formatting drop-down.

Can I highlight values below a different amount?
Yes. In the Less Than dialog, enter the threshold you want instead of 0. For example, entering -100 highlights values below -100, but not -100 itself. Use Less Than or Equal To from Highlight Cells Rules if the threshold itself should also be included.

Can I color negative values with a formula-based rule?
Yes, but the Less Than rule is simpler for a straightforward range. A formula rule can help when the condition depends on another cell or when you need more control. For a basic negative-value check, select the range and use Conditional Formatting > New Rule, then choose the option to use a formula to determine which cells to format. Make sure the reference in the formula matches the first cell in the selected range.

Does the custom number format color the cell background?
No. The example custom format colors negative numbers’ font red, but does not add a fill. Use conditional formatting if you want a colored background, or if you want to set both font and fill colors based on the value.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.