Conditional Formatting for Negative Values in Excel

In Excel for Microsoft 365 on Windows, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Less Than, enter 0, choose a format, and select OK. Excel will apply that format to values below zero and update it as the values change.

What conditional formatting does

Conditional formatting changes how a cell looks when it meets a rule; it does not change the cell’s value. For example, a cell containing -25 remains -25 even if the rule displays it with a red fill, red text, or both. If the value later becomes 12, the negative-value formatting disappears because the condition is no longer true.

This makes conditional formatting useful for spotting losses, negative balances, below-zero temperatures, inventory adjustments, or any other values that need attention. It is also different from simply typing a minus sign or changing a number format: the rule checks the underlying value and responds when that value changes.

Use the built-in Less Than rule

The built-in rule is the quickest option when you want to highlight negative numbers in a range. In Windows Excel for Microsoft 365:

Select the cells to format. For a column of results, select just the data cells rather than the heading.
On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Less Than.
Enter 0 in the value box. The comparison is strictly less than zero, so zero itself is not highlighted.
Choose one of the suggested formats, or choose Custom Format to set the font, fill, border, or number format.
Select OK to create the rule.

For example, if the selected cells contain 14, 0, -3, and -18, the rule formats -3 and -18 but leaves 14 and 0 unchanged. The rule works on each selected cell, so you usually do not need to create a separate rule for every row.

Choose a format people can read

A red font or a pale red fill is a familiar way to flag negative figures, but the best choice depends on what the workbook is used for. A high-contrast fill can make exceptions easy to find in a large table. For a printed report, a font change or a border may be clearer than a very light fill. Avoid relying on color alone when the information must also be accessible in grayscale; a label, icon, or clear number format can provide another cue.

Custom Format opens formatting controls similar to ordinary cell formatting. You can combine a font color and a fill, for example, but the underlying value and formulas remain untouched. Consider using the same visual convention throughout a workbook: if red means negative in one report, using it to mean “complete” in another can confuse readers.

Use a formula when the built-in rule is not enough

A formula-based rule is useful when the condition depends on a different cell, or when you need to format an entire row based on one column. Select the range first, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the format and confirm the rule.

For a rule applied to cells B2:B100, with the first selected cell being B2, enter =B2<0 as the formula. Excel evaluates the reference relative to each cell in the selected range: B2 is checked in the first row, B3 in the next, and so on. The result is TRUE for a negative value and FALSE otherwise.

Reference choice matters. If the formula uses =$B$2<0, both dollar signs lock the reference, so every cell in the range checks B2 rather than its own row. That can be intentional, such as coloring a group based on one control cell, but it is usually not what a per-cell negative-value rule needs.

Highlight a full row from a negative value

To shade an entire record when its amount is negative, apply a formula rule to the rows or columns you want to shade, while referring to the amount column. Suppose the table occupies A2:F100 and the amount is in column D. Select A2:F100, create a formula rule, and use =$D2<0. Choose a format and confirm.

The dollar sign before D keeps the check in the amount column as the rule moves across columns. The row number has no dollar sign, so it changes as the rule evaluates row 3, row 4, and subsequent rows. If the amount is in another column, substitute that column letter. Keep the first row number aligned with the first row of the selected range; a mismatch can make the highlighting appear one row too high or too low.

Manage the rule and its range

Conditional formatting rules apply to a range, shown as “Applies to” in the rules manager. To inspect or edit a rule in Windows Excel, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. Use the “Show formatting rules for” list to view rules for the current selection, the current worksheet, or other relevant scopes. From the manager, you can edit a rule, change its range, delete it, or adjust its order.

Check the range whenever the data grows. A rule covering B2:B100 will not necessarily cover a new entry in B101. You can update “Applies to” to include additional rows or apply the rule to a larger planned range. If the data is an Excel table, adding rows may carry formatting behavior into new rows, but it is still worth checking the rule’s range when results do not appear as expected.

When several rules apply to the same cells, their order and settings can affect the final appearance. In Manage Rules, move rules to change their priority. “Stop If True” can prevent lower-priority rules from being evaluated for a cell when the current rule is true. This is useful in carefully designed rule sets, but it is not needed for a single negative-value rule. If a cell has an unexpected color, inspect all rules that apply to it, not just the one you most recently created.

Common problems and how to fix them

Numbers stored as text: A value that looks like -12 may be text rather than a numeric value, especially if it was imported or preceded by an apostrophe. A numeric comparison rule may not treat it as a negative number. Check the cell’s alignment and value, then convert the data to numbers if appropriate.
Nothing is highlighted: Confirm that the rule applies to the cells you are viewing and that the values are truly below zero. Also check for another rule overriding the format. A cell that displays a dash may contain zero rather than a negative value.
The wrong row is highlighted: For formula rules, verify that the row number in the formula matches the first row of the “Applies to” range. For full-row formatting, lock the amount column with a dollar sign but leave the row relative.
Zero appears negative: The displayed value may be rounded. A stored value such as -0.004 can display as 0 when the cell shows no decimal places, but it is still less than zero and qualifies. Increase the displayed decimal places or decide whether the rule should ignore very small amounts.
Copied formatting causes duplicates: Copying cells can also copy conditional-formatting rules, sometimes creating overlapping ranges or extra rules. Review Manage Rules and remove redundant entries rather than adding another rule to compensate.

Negative values, number formats, and displayed zeros

Conditional formatting is not the only way to make negative numbers stand out. A number format can display negative values in red or parentheses without testing a separate condition. For example, a financial report may show a negative amount in parentheses while leaving positive values unchanged. That changes presentation only; it does not turn the value into text or alter calculations.

The distinction is practical: use conditional formatting when the appearance should signal a condition, such as a warning color or a full-row highlight. Use a number format when you want a consistent display convention for all numbers in a range. The two features can coexist, but check the result if both specify colors or other display choices.

Rounding also explains a frequent puzzle. Excel compares the stored numeric value, not merely the rounded value visible on screen. If a calculation produces a tiny negative amount that displays as zero, the less-than-zero rule still applies. If the business meaning is “negative by more than a tolerance,” define that tolerance explicitly rather than assuming displayed rounding changes the comparison.

Excel on Mac and Excel for the web

Excel for Mac also supports conditional formatting, including rules for values less than a number and formula-based rules. The ribbon layout and dialog arrangement can differ from Windows, so look under the Home tab’s Conditional Formatting controls rather than expecting every Windows dialog to look identical. The central steps remain: select the target range, create the rule, set the condition and format, and check the range afterward.

Excel for the web provides conditional-formatting controls as well, though some rule-editing options may be arranged differently or be less convenient than in the desktop application. If a workbook has complex overlapping rules, formula-based formatting, or a range that is behaving unexpectedly, open it in the desktop version to inspect Manage Rules and the full set of rule settings.

Frequently Asked Questions

How do I highlight negative numbers in Excel?
Select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 0, select a format, and confirm. Only values below zero meet that condition.

Can I highlight negative numbers in a different color?
Yes. In the Less Than dialog, choose Custom Format and set the font color, fill, or other available formatting. You can also edit the rule later through Home > Conditional Formatting > Manage Rules.

How do I highlight a whole row when one cell is negative?
Select the rows’ full data range and create a formula rule that checks the amount column. Lock that column in the reference and leave the row relative, as in the example =$D2<0 when the first selected row is row 2 and the amount is in column D.

Does conditional formatting change the number?
No. It changes the cell’s appearance when a condition is true. The value remains available to formulas and calculations as before.

Why is a value displayed as zero highlighted as negative?
It may contain a small negative value that is rounded for display. Increase the number of displayed decimal places to inspect it, or choose a rule that matches the tolerance your worksheet requires.

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.