Conditional Formatting Builder

Pick a condition such as greater than, contains text, duplicate, date in the past, weekend, or highlight the whole row, and the Conditional Formatting Builder writes the formula for Excel's Use a formula to determine which cells to format option. Use it when the built-in presets do not cover your rule.

What the Conditional Formatting Builder does

Excel's conditional formatting presets cover simple rules, but many practical rules need a formula: highlighting a whole row based on one column, flagging duplicates in a specific range, or marking weekend dates. Writing those formulas requires correct use of dollar signs, because the formula is written for the first cell and then applied to every cell in the selected range.

The builder asks for the condition, the values, and the top-left cell of your range, and returns a formula with the references already anchored correctly. It also lists the menu path to apply it.

How to use it

  1. In Excel, select the range you want to format, starting from the top-left cell.
  2. In the builder, choose the condition and enter the value or text it should test.
  3. Enter the address of the top-left cell of your selection, for example A2.
  4. Copy the generated formula.
  5. In Excel, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and paste the formula.
  6. Choose a format, select OK, and check a few cells to confirm the rule is applied where you expect.

Worked examples

Highlight the whole row

Input: Range A2:F100 selected, formula =$C2="Overdue"

Result: Every row where column C contains "Overdue" is highlighted across A to F.

Why: The dollar sign before C locks the column, so every cell in a row checks column C. The row number has no dollar sign, so it moves down with each row.

Flag duplicates in a list

Input: Range A2:A100 selected, formula =COUNTIF($A$2:$A$100,A2)>1

Result: Every value that appears more than once in A2:A100 is highlighted.

Why: The search range is fully locked so it stays the same for each cell, while A2 moves to the cell being tested.

Mark weekend dates

Input: Range A2:A100 selected, formula =WEEKDAY(A2,2)>5

Result: Saturdays and Sundays are highlighted.

Why: With return type 2, WEEKDAY numbers Monday as 1 and Sunday as 7, so values above 5 are Saturday and Sunday.

Common conditional formatting mistakes

Wrong starting cell
The formula must refer to the top-left cell of the selected range. If the selection starts at A2 but the formula uses A1, every result is shifted by one row.
Locking the row by mistake
=$C$2 checks only row 2 for every row, so the whole range is highlighted or none of it is.
Text stored as numbers
A comparison such as =A2>1000 does not work as expected if the values are text. Convert them to numbers first.
Overlapping rules
Rules apply in the order shown in Manage Rules. A rule higher in the list can hide the format of a lower one.

Frequently asked questions

Why is my whole range highlighted?

The formula is usually fully locked, for example =$C$2, so every cell checks the same cell. Remove the dollar sign before the row number.

Do formula rules work in Google Sheets?

Yes. Google Sheets has a Custom formula is option under Format > Conditional formatting, and these formulas work there in most cases.

Can I edit a rule after creating it?

Yes. Use Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule.

Related