How do I automatically highlight cells that meet a condition?
Conditional formatting applies a color, bold, or icon automatically whenever a cell meets a rule you set, without changing the underlying data. For a custom condition, use a formula rule like =A2<0 to highlight negative numbers in a selected range.
Formula (Excel and Google Sheets)
=A2<0Both Excel and Google Sheets offer built-in presets (highlight cells greater than, less than, duplicate values, and so on) plus a custom formula option for anything a preset does not cover. The formula option evaluates a TRUE/FALSE test against the first cell in your selected range, then applies the same logic to every other cell in that range automatically.
This is useful for flagging exceptions at a glance: negative balances, overdue dates, values above a target, or rows matching a specific status, all without a helper column cluttering the sheet.
Step by step
- 1
Select the range you want to format
Highlight the cells the rule should apply to.
- 2
Open conditional formatting and choose Custom formula
In Excel: Home > Conditional Formatting > New Rule > Use a formula. In Google Sheets: Format > Conditional formatting > Custom formula is.
- 3
Write a formula referencing the top-left cell of your selection
e.g. =A2<0 for the first cell in the range; the rule applies it relatively to every other cell.
- 4
Choose the formatting to apply
Pick a fill color, text color, or bold style to apply when the condition is TRUE.
Common questions
Does conditional formatting change the actual value in the cell?
No. It only changes how the cell looks. The underlying value and any formulas referencing it are unaffected.
Can I use a condition based on a different cell, not the one being formatted?
Yes. Custom formula rules can reference any cell, e.g. highlighting a row based on a status value in a different column.
Related formulas
How to Write a Nested IF Formula (and When to Avoid It)
RelatedHow to Remove Duplicate Rows in Excel and Google Sheets
RelatedHow to Calculate Percentage Change Between Two Numbers
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator