Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsTo highlight negative numbers in Excel, select the range, then choose Home > Conditional Formatting > Highlight Cell Rules > Less Than. Enter 0, choose a style such as red text or a red fill, and select OK. Excel will apply the rule whenever a cell’s value is below zero.
If you only need a different display—such as red text or parentheses—use Format Cells > Custom instead. A format such as #,##0;[Red]-#,##0; changes how negative numbers look but leaves their underlying values available for calculations.
As an Amazon Associate I earn from qualifying purchases.
Choose the right method first
Excel has two practical ways to make negative values stand out. They solve different problems, so choosing before you format prevents unnecessary rules and maintenance.
Free tools Windows power users keep installed
One-click scans. No signup required.
| Method | What it changes | Best use | What happens as values change |
|---|---|---|---|
| Conditional formatting | Font, fill, border, number format, or other visual formatting | You need a colored background, border, formula logic, or a rule that is easy to manage separately from the cell’s normal format | The appearance updates automatically when a value becomes negative or stops being negative |
| Custom number format | Only the way the value is displayed | Red negatives, minus signs, parentheses, currency, or percentage conventions are sufficient | The display follows the format while the numeric value remains unchanged |
Use conditional formatting when the visual signal itself matters—for example, a red fill in a financial exception report. Use a custom format when you want a lightweight convention, such as red negative amounts, without adding a separate rule. You can use both, but do so deliberately: overlapping formats can make a worksheet harder to audit.
Highlight negative numbers with conditional formatting
The built-in Less Than rule catches every numeric value below zero in the selected range. This is the most direct approach when you want a fill, border, font color, or another prominent signal.
Apply a Less Than 0 rule
- Select the cells containing the numbers. You can select one column, a rectangular range, or a larger block that includes the values you want monitored.
- On the ribbon, choose Home > Conditional Formatting > Highlight Cell Rules > Less Than.
- In the comparison box, type
0. - Choose one of the preset styles, or select Custom Format to specify the font color, fill color, border, and number format.
- Select OK. Excel immediately evaluates the selected cells and applies the style to values below zero.
- Scan the range to confirm that negative values are visibly different and that zero and positive values retain their intended appearance.
The comparison is numeric: a value of -0.01 qualifies, while 0 does not. The rule does not rewrite, round, or otherwise alter the number used by formulas.
Set a custom style in the rule
When the preset style is too strong or too subtle, select Custom Format in the Less Than dialog. In the formatting window, choose the Font tab for a color or emphasis, Fill for a background, and Border for an outline. You can also set a number format there if the negative values should display with a particular convention.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →A red font is familiar in accounting worksheets, but a light fill or border may remain easier to read when a sheet is printed in grayscale. Avoid relying on color alone if readers may have color-vision differences; pair the color with a minus sign, parentheses, a border, or a nearby label.
Make the rule cover a changing range
A rule applies only to the range listed in its Applies to field. If new rows will be added, review that range instead of assuming Excel will extend it.
- Select any cell in the formatted area.
- Choose Home > Conditional Formatting > Manage Rules.
- Find the Less Than rule and inspect Applies to.
- Edit the range so it includes the rows or columns that may receive future values, then select Apply or OK.
For a table or a deliberately oversized input range, include the expected area from the start. For a fixed report, keep the range narrow so unrelated cells do not acquire the style.
Use a formula when “less than zero” is not enough
The built-in comparison is suitable for ordinary numeric cells. Conditional formatting also supports formula-based rules when the decision depends on more than one condition—for example, a negative amount in a particular category or a negative value only when a status cell says “Open.”
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchIn a formula rule, write a logical expression that evaluates to TRUE or FALSE, and make the cell references relative to the first cell in the selected range. A simple negative-value test is =A1<0 when the top-left cell of the range is A1. Excel adjusts the relative reference for the other cells. Add the other required conditions with normal worksheet logic, then choose the format to apply when the formula is TRUE.
Show negatives in red with a custom number format
A custom number format is preferable when the goal is display rather than a separate visual rule. It does not change the underlying numeric value, so formulas, sorting, filtering, and charts continue to use the original number.
Enter a custom format
- Select the cells.
- Open the cell-formatting dialog and choose the Custom category.
- Enter the desired code in the Type box.
- Select OK and check both a positive and a negative example.
Excel’s custom-format sections are separated by semicolons and appear in this order: positive; negative; zero; text. With two sections, the second section controls negative numbers. A trailing empty section can define how zero is shown without adding visible characters.
Rank #3
Common formats for whole numbers
| Purpose | Format code | Result for a negative value |
|---|---|---|
| Red minus sign with thousands separators | #,##0;[Red]-#,##0; |
-1,250 appears in red |
| Red currency with two decimals | $#,##0.00;[Red]-$#,##0.00; |
-$1,250.00 appears in red |
| Parentheses for negatives | #,##0;[Red](#,##0); |
(1,250) appears in red |
The first section controls positive numbers, the second controls negatives, and the third controls zero. In the examples above, the empty third section leaves zero with no displayed characters; remove that final semicolon section if you want the normal zero display instead.
Format negative percentages
Percentages need a percent sign in the format code. Use 0.00%;[Red]-0.00% to show two decimal places and color negative percentages red. A stored value of -0.125 is displayed as -12.50%; the value used in calculations remains -0.125.
If your percentage data uses a different number of decimal places, change both 0.00% sections consistently—for example, 0%;[Red]-0% for whole percentage points. Check a small positive, negative, and zero sample after applying the code so rounding is visible before you format a full report.
Conditional formatting versus custom formatting
| Question | Conditional formatting | Custom number format |
|---|---|---|
| Can it apply a fill or border? | Yes | No; it controls number display |
| Can it use a formula or several conditions? | Yes | No; the sections describe display patterns |
| Does it preserve the numeric value? | Yes | Yes |
| Is it suitable for red negatives only? | Yes, though it may be more setup than necessary | Usually the simplest choice |
| Does it react when a formula result changes sign? | Yes | Yes, because the display section is selected from the current value |
| Can it show a special symbol or convention such as parentheses? | Yes, by combining the rule with a number format | Yes, directly in the format code |
For a dashboard or exception list, conditional formatting communicates urgency through fill, font, or borders. For a ledger where red text or parentheses are the established convention, a custom number format keeps the worksheet simpler.
What happens to calculations and data?
Both approaches change appearance, not the stored number. A cell formatted as red -1,250 still contributes -1250 to a sum. A percentage displayed as -12.50% still has its underlying decimal value. You can inspect the unformatted value in the formula bar and use it in formulas, sorting, filtering, and charts.
This distinction matters when a displayed value appears rounded. A format may show no decimal places while the cell retains fractional precision. Conditional formatting compares the underlying value, not merely the characters visible in the cell. Consequently, a value that displays as zero after rounding can still be slightly negative and satisfy a Less Than 0 rule.
Highlight an entire row when one value is negative
If the requirement is to emphasize a complete record rather than just the negative cell, create a formula-based conditional-formatting rule. Select the full data region, then use a formula anchored to the value column. For example, if the amount is in column D and the first data row is row 2, a rule based on =$D2<0 applies the chosen style across each row while allowing the row number to change. Keep the dollar sign before D so the test remains tied to the amount column as Excel evaluates other cells in the row.
Before applying such a rule, verify that the selected range starts on the same row used in the formula. An offset between the first selected row and the formula reference is a common cause of apparently incorrect highlighting.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Troubleshooting negative-number highlighting
Nothing is highlighted
- Confirm the comparison value is
0, not the text string"0"entered as part of a formula or label. - Check that the selected range includes the cells you are inspecting.
- Open Manage Rules and verify the Applies to range.
- Check whether the cells contain numbers rather than text that merely looks numeric. A text value with a leading apostrophe will not behave like a number in a Less Than comparison.
- Inspect the formula bar for a small negative value that may be hidden by rounding or another number format.
Positive values are also colored
- Review the rule condition and ensure it is Less Than, not Less Than or Equal To or another comparison.
- In Manage Rules, look for another rule applying to the same cells. Overlapping rules can produce a style that appears to come from the negative-value rule.
- For a formula-based rule, verify the first-cell reference and any absolute or relative markers.
The color disappears after editing
Conditional formatting reevaluates after a value changes. If the cell is no longer negative, the negative style should disappear. If a formula returns an error, text, or an empty string, the Less Than 0 test may not produce the expected result; inspect the formula result and the rule’s logic separately.
The format works in one area but not new rows
Open Conditional Formatting > Manage Rules and extend Applies to. For a custom number format, copy the formatted cell or apply the format to the new range; custom formats are properties of the cells, not an independent rule with a range manager.
Red text is hard to read when printed
Choose a darker font, a light fill, or a border in the conditional-formatting style. Keep the minus sign or parentheses visible so the meaning survives grayscale printing and common forms of color-vision deficiency.
Practical examples
Monthly budget variance
Select the variance column and apply Less Than 0. Use a light red fill and dark red font so overspending is visible without obscuring the numbers. Set Applies to to the full expected variance range, including rows that will be added during the reporting period.
Profit-and-loss statement
For a traditional statement, select the amount cells and apply $#,##0.00;[Red]-$#,##0.00; as a custom format. This keeps the worksheet uncluttered while displaying negative amounts consistently. If management also needs a prominent warning, add a conditional-formatting fill to the same range.
Recommended Free Tools
Performance percentages
Use 0.00%;[Red]-0.00% when negative percentages should retain two decimal places. If a negative result should be emphasized only when a related status is active, use a formula-based conditional-formatting rule instead of coloring every negative percentage.
Keep the workbook maintainable
- Use one clearly named rule for a straightforward negative test rather than many overlapping rules.
- Document the intended Applies to range when a report is handed to another person.
- Use a consistent negative convention—red text, red fill, parentheses, or a combination—across related sheets.
- Test positive, negative, zero, and percentage values after changing a format.
- Review conditional-formatting rules periodically, especially after inserting columns, copying sheets, or importing data.
With these checks, the Less Than 0 rule provides automatic highlighting, while a custom number format provides a compact display convention. In either case, Excel keeps the original numeric values intact for every calculation.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
