MO-211 Expert Prep
Module 2 · Lesson 2.10

Conditional Formatting Rules That Use Formulas

Edit on GitHub

Standard conditional formatting rules check the value of the cell being formatted. Formula-based rules are fundamentally more powerful: the formula can reference any other cell — allowing one cell's value to control the formatting of an entirely different cell or an entire row. This is the closest Excel gets to a dynamic, data-driven styling system.

Part 1: Creating a Formula-Based Rule

  1. Select the range you want to format (e.g., A2:E100 — an entire table).
  2. Home > Conditional Formatting > New Rule...
  3. Select rule type: "Use a formula to determine which cells to format"
  4. Enter your formula in the field provided.
  5. Click Format... and configure the desired style.
  6. Click OK.

The formula must evaluate to TRUE (or any non-zero value) for the format to apply. If it returns FALSE or 0, no formatting is applied.

Part 2: Reference Behavior — The Most Critical Concept

The formula you enter is evaluated relative to the top-left cell of the applied range. Excel then shifts the reference for every other cell in the range — exactly like copying a formula down and across.

This means absolute vs. relative references are critical:

Reference Type Behavior in CF Formula When to Use
$A$2 Fully locked — always checks the same cell Reference a fixed threshold or label
$A2 Column locked, row shifts Check a specific column for every row (most common for row-based rules)
A$2 Row locked, column shifts Check a specific row across columns
A2 Both shift — rarely useful Almost never used in CF formulas

The Standard Pattern for Row-Based Formatting

To highlight an entire row based on the value in a specific column: - Select the entire data range (e.g., A2:E100) - Write the formula referencing only the decision column with a locked column, free row: =$D2="Overdue" - The $D locks to column D for every cell in the row; the 2 shifts down as Excel evaluates each row

Part 3: Practical Formula Examples

Highlight entire row when status is "Overdue"

Applied to A2:E100:

=$D2="Overdue"

Flag past-due dates

Applied to C2:C100:

=$C2<TODAY()

Mark rows where sales are below target

Applied to A2:E50 (target is in column F):

=$B2<$F2

Highlight blank required fields

Applied to B2:B100:

=$B2=""

Alternating row shading (zebra stripes)

Applied to A2:E100:

=ISODD(ROW())

Applies fill to every odd-numbered row automatically.

Highlight the current row when a checkbox is checked (column A = TRUE)

Applied to A2:E100:

=$A2=TRUE

Highlight values more than 2 standard deviations above the mean

Applied to B2:B100:

=B2>AVERAGE($B$2:$B$100)+2*STDEV($B$2:$B$100)

Part 4: Debugging Formula-Based Rules

Formula-based conditional formatting rules are invisible — if they do not work, there is no error message. Use this debugging approach:

  1. In an empty helper column, enter the same formula you used in the CF rule for the first row.
  2. Check that it returns TRUE or FALSE as expected.
  3. Copy the formula down several rows to verify it shifts correctly.
  4. Once confirmed, delete the helper column — the CF rule will behave identically.
IMPORTANT
Always write your formula as if you are evaluating the **top-left cell of your selected range**. If your range starts at `A2`, write the formula for row 2. If it starts at `A1`, write for row 1. The most common mistake is selecting `A2:E100` but writing a formula referencing row 1.

Official Resources