Skip to content
Tutorials Center
My learning

Topics

Lesson 59 of 93 ยท Charts and Visualization

Conditional Formatting

Use Excel conditional formatting to highlight values, top and bottom items, whole rows and deadlines with formula rules, rule order and Stop If True.

Conditional formatting changes how a cell looks depending on what it contains. A late invoice turns red, the top five sales get a green fill, every other row gets a light band. The cell value never changes; only the formatting does, and it updates the moment the data does. That makes it one of the cheapest ways to make a sheet readable.

In the previous lesson you met data bars, icon sets and color scales, which are conditional formats too. This lesson covers the rule-based kind: highlight rules, top/bottom rules and formula-based rules. Excel cannot run on this site, so the menu steps and formulas are shown as text, and the TryIts use a small JavaScript rule-evaluation stand-in. It mimics how Excel walks through a list of rules; it is not Excel's code.

Three families of rules

Highlight cells Greater than, text contains, duplicates.
Top / Bottom Top 10 items, above average.
Formula rules Any TRUE/FALSE test you can write.

Formula rules can do everything the others can, and more.

1 / 2

Highlight cells rules

Select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules. The submenu offers:

  • Greater Than, Less Than, Between, Equal To for numbers.
  • Text that Contains for words.
  • A Date Occurring for yesterday, today, next week, last month and so on.
  • Duplicate Values, which can also mark unique values.

Each opens a small dialog: type the threshold, pick a preset format (such as light red fill with dark red text) or choose Custom Format, and click OK. The threshold can also be a cell reference, such as =$F$1. That is better than a typed number, because you change the limit in one visible place and the highlighting follows.

A relative reference in a rule (without $) is relative to the active cell at the moment you create it, which surprises many people. Anchor limit cells with $F$1.

Top/Bottom rules

Top/Bottom Rules highlights the Top 10 Items, Top 10 %, Bottom 10 Items, Bottom 10 %, Above Average and Below Average. Despite the name you can change 10 to any number, such as the top 3. These rules compare each cell with the others in the selected range, so they react when values are added inside the range. If you add rows below the range, the rule does not cover them unless the data is an Excel Table or you extend the rule's range in Manage Rules.

Formula-based rules

The most powerful choice is New Rule > Use a formula to determine which cells to format. You write a formula that returns TRUE or FALSE. Excel evaluates it for every selected cell, with references shifting just as they do when you copy a formula (see Cell References).

The golden rule: write the formula as if for the first cell of the selection, and anchor what must not move.

TEXT
Selection A2:E20, highlight the whole row when Status (column E) is "Late":
    =$E2="Late"

Selection B2:B20, highlight values over the average of the column:
    =B2>AVERAGE($B$2:$B$20)

Selection A2:A20, highlight duplicates in column A:
    =COUNTIF($A$2:$A$20,A2)>1

Selection A2:E20, band every other row:
    =MOD(ROW(),2)=0

Selection A2:A20, mark blank cells in column B:
    =ISBLANK($B2)

The dollar sign before the column letter in $E2 means "always look at column E", while the row number is free to change from row to row. That is the pattern for highlighting entire rows: mixed reference, column locked, row relative.

Row banding and deadlines

=MOD(ROW(),2)=0 is TRUE on even rows, so every second row is shaded. It keeps working when you sort, insert or delete rows, which manual fills do not. (A Table's built-in banded rows do the same job for tables.)

For deadlines, compare a date column with TODAY(). The next formulas assume the due date is in column D:

TEXT
Overdue (red):          =AND($D2<TODAY(),$F2<>"Done")
Due within 7 days:      =AND($D2>=TODAY(),$D2<=TODAY()+7,$F2<>"Done")
Done (grey text):       =$F2="Done"

The $F2<>"Done" part stops finished tasks from turning red. Dates are plain serial numbers (see Dates and Times in Excel), so comparing with TODAY() just works. TODAY() is volatile, so these rules re-evaluate whenever the workbook calculates.

Run it: a rule-evaluation stand-in

The stand-in stores each rule as a test function plus a format name. It applies the rules to a small task list and prints the formatting each cell would get. A row's formula uses the same locked-column idea as =$E2="Late": the test receives the whole row.

JS Evaluating formula-based rules per row

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Rule order, conflicts and Stop If True

A range can have many rules. Open Home > Conditional Formatting > Manage Rules, set Show formatting rules for: This Worksheet, and you see the list. The rule at the top has the highest priority. Use the arrows to move rules up and down.

When two rules are TRUE for the same cell:

  • If they set different properties (one the fill, the other the font color), Excel applies both.
  • If they set the same property (both set the fill), the higher rule wins.

Stop If True (a checkbox per rule) tells Excel: if this rule is TRUE for the cell, ignore every rule below it. Use it for "first match wins" logic, such as a Done row that must stay grey even if the due date is in the past. Put the Done rule first with Stop If True ticked.

Note that Stop If True does not work with data bars, color scales and icon sets in the same way: it simply stops lower rules from applying, and for those visual types it exists mostly for compatibility with older versions.

Run it: priority, merging and Stop If True

This second stand-in applies the Excel conflict logic: the first rule to set a property keeps it, and rules marked stop end the walk.

JS Priority, merging and Stop If True

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Managing rules

Manage Rules is also where you repair a messy sheet.

  • Edit Rule changes the formula or format. Delete Rule removes one.
  • The Applies to column holds the range. Change it to extend or shrink coverage; separate several ranges with commas.
  • Copy and paste cells normally and the rules travel with them, but repeated copy-and-paste splits one rule into dozens of fragments with tiny ranges. If a sheet gets slow or the list is long, delete the fragments and recreate one rule over the full range.
  • Clear Rules (from the main menu) removes rules from the selected cells or the whole sheet.
  • Format Painter copies formatting including conditional rules.

Color alone should not carry the meaning. Some people cannot tell red from green, so combine color with text, an icon or bold. Keep the palette small: two or three rules per sheet are easier to read than a rainbow.

A few practical limits: conditional formats cannot reference other workbooks in very old versions (modern Excel allows it but it is fragile), cannot change cell size or font name, and do not count as cell content, so a filter by color works but a formula cannot "see" the color.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You select A2:E20 and want the whole row red when column E says Late. Which formula is right?

    The column must stay locked at E while the row changes, so $E2 is the right mixed reference. Without the $, the test would shift to column F, G and so on. With $E$2, every cell would test the same single cell.
  2. 2. Two rules are TRUE for one cell and both set the fill color. What happens?

    Priority follows the list order in Manage Rules. Rules that set different properties are both applied, but for the same property the higher one wins.
  3. 3. What does ticking Stop If True on a rule do?

    Stop If True gives first-match-wins logic, handy for a Done status that must override overdue warnings.
  4. 4. Why is =MOD(ROW(),2)=0 better for row banding than filling rows by hand?

    The rule looks at the row number each time, so the pattern follows the data instead of staying where the fill was painted.

Summary

  • Conditional formatting changes appearance, never values, and updates as data changes.
  • Highlight rules and Top/Bottom rules cover quick jobs; formula rules cover everything else.
  • Write a formula rule for the first cell of the selection and lock the right parts, for example =$E2="Late" for whole rows.
  • Use TODAY() with AND to flag overdue and soon-due items, and exclude finished items.
  • The list order in Manage Rules sets priority; Stop If True gives first-match-wins.
  • Keep rules few, reuse one rule over the full range, and never rely on color alone.

Next: Building an Interactive Dashboard.