PlayInfoBox

Home /Utilities /How to Create Conditional Formatting with a Formula in Excel

How to Create Conditional Formatting with a Formula in Excel

Tap through each step to build a formula-based conditional formatting rule.

  1. Select the cell range to format

    Drag to select the cells you want to change formatting on based on a condition.

  2. Go to "Home β†’ Conditional Formatting β†’ New Rule"

    Click "New Rule" from the Conditional Formatting menu.

  3. Choose "Use a formula to determine which cells to format"

    Select the formula-based rule type from the list of rule types.

  4. Enter your condition formula, then set the format

    Type a formula that returns TRUE or FALSE, such as "=A1>B1," then click "Format" to choose the color or style to apply.

  5. Why it's useful

    Beyond comparing a single value, writing your own formula lets you compare against another cell or apply a condition to an entire row, giving you far more precise control than the built-in preset rules.

When the built-in rules aren't flexible enough

The preset options only compare a cell against a fixed number or a short list of conditions. Writing your own formula lets you compare one cell against another, reference a value elsewhere on the sheet, or combine several conditions β€” covering cases the built-in menu simply doesn't offer.

Highlight an entire row based on one column's value

Mixing relative and absolute references in the formula β€” locking only the column letter, like "$A1," while leaving the row unlocked β€” applies the formatting across the whole row whenever that row's condition is met, which is a common trick for highlighting, say, every overdue task in a tracking sheet.

Frequently Asked Questions

How do I apply formatting to an entire row instead of just one cell?

When entering the formula, lock only the column letter (for example, "$A1") and leave the row reference unlocked β€” the formatting then applies to the whole row wherever the condition is true.

Can I combine multiple conditions in one rule?

Yes β€” wrapping several conditions in AND or OR, such as `=AND(A1>60, B1="Pass")`, applies the format only when every condition (with AND) or at least one condition (with OR) is met.