Excel Intermediate Lesson 11: Conditional Formatting With Formulas

Excel Intermediate Lesson 11: Conditional Formatting With Formulas featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

πŸ“˜ Excel Intermediate Course Β· Lesson 11 of 12

In this article
  1. How a formula rule works
  2. Rules worth stealing
  3. Manage rules
  4. Common mistakes
  5. Practice
  6. Continue learning

The preset rules (greater than, top 10) colour single cells. Formula rules can colour a whole row based on any condition, which is what makes a register readable.

Advertisement

How a formula rule works

  1. Select the whole data area, e.g. A2:L81 (top-left cell A2 active).
  2. Home > Conditional Formatting > New Rule > Use a formula.
  3. Write the formula for the first row only, as if it were in A2. Format when TRUE.

Excel applies it to every cell, shifting row numbers. The $ before the column letter keeps every cell of a row looking at the same column.

Rules worth stealing

Highlight Formula (A2 active)
Overdue rows =$L2="Overdue"
Above-average amounts =$K2>AVERAGE($K$2:$K$81)
A chosen month (month in cell N1) =TEXT($B2,"yyyy-mm")=$N$1
Top 3 invoices =$K2>=LARGE($K$2:$K$81,3)
Sundays =WEEKDAY($B2)=1
Every other row (banding) =MOD(ROW(),2)=0
πŸ’‘ Test a rule as a normal formula in an empty column first. If it shows TRUE on the right rows, it will work as a rule. The practice file does exactly this.

Manage rules

Conditional Formatting > Manage Rules shows rule order. Tick Stop If True when an overdue row shouldn’t also get the above-average colour.

Advertisement

Common mistakes

  • No $ before the column: only one column highlights, in a diagonal pattern.
  • $ before the row too ($L$2): every row copies row 2’s answer.
  • Hundreds of duplicated rules after copy-pasting rows. Clean up in Manage Rules now and then.

Practice

Download this lesson’s workbook below. Each task is a rule tested as a normal formula; then add the rules to the Sales sheet. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.

πŸ“Ž Practice files for this article

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong