Conditional formatting and data validation

Intermediate

Introduction

A sheet with hundreds of numbers hides its story. Conditional formatting makes the important cells stand out on their own: overdue dates in red, the top sales in green, a bar in every cell showing its size. Data validation works from the other side, guiding what people can type so that errors never get in. Together they make a workbook easier to read and harder to break.

How it works

Conditional formatting is on the Home tab. Select a range and choose a rule:

  • Highlight Cells Rules: greater than, less than, between, equal to, text that contains, a date occurring (such as last week) and duplicate values.
  • Top/Bottom Rules: the top 10 items, the top 10%, above or below average.
  • Data Bars, Colour Scales and Icon Sets show values graphically inside the cells.

The rules are live: when values change, the formatting follows. Manage Rules on the Conditional Formatting menu lists, edits and reorders them.

Formula-based rules (New Rule, "Use a formula to determine which cells to format") give full control. The formula is written for the top-left cell of the selection and must return TRUE for cells that should be formatted. To highlight whole rows of a list in A2:D100 where column D says Overdue, select A2:D100 and use =$D2="Overdue". The dollar sign locks column D, so every cell in the row checks the same column, while the row number stays relative and moves down the list.

Data validation is on the Data tab. Select the cells and choose what is allowed:

  • List: a drop-down of choices, typed as Open,Closed,On hold or taken from a range such as =$H$2:$H$5.
  • Whole number, Decimal, Date or Text length between limits.
  • Custom: a formula that must be TRUE, such as =COUNTIF($A:$A,A2)=1 to prevent duplicate IDs.

The Input Message tab shows a note when the cell is selected, and the Error Alert tab sets what happens on a bad entry: Stop refuses it, Warning and Information let the user choose to keep it. Validation only checks what is typed: values pasted in can bypass it, so use Data, Circle Invalid Data to find entries that break the rules.

Example

A task tracker in A1:C4 has a Status column that should only hold three values, with overdue tasks in red:

ABC
1TaskDueStatus
2Report10/11/2026Open
3Invoice20/11/2026Done
4Backup01/11/2026Open

Line by line

  1. Select C2:C100, choose Data, Data Validation, allow List with the source Open,Done,On hold, and an input message "Choose a status".
  2. Select A2:C100 and add a conditional formatting rule with the formula =AND($B2<TODAY(),$C2<>"Done") and a light red fill.
  3. Select B2:B100 and add Highlight Cells Rules, A Date Occurring, Next Week, to flag tasks due soon.

Expected result

Each Status cell shows a drop-down with the three choices, and typing Finished is refused with a Stop message. With 13 November 2026 as today, the Report and Backup rows turn light red, because their due dates have passed and they are not done; the Invoice row stays plain. Any task due next week is highlighted by the third rule.

In the real world

Project trackers, stock sheets and finance reports use these features constantly: red for late, amber for at risk, green for done, and drop-downs so that "Done", "done" and "Finished" never appear side by side. Templates shared with many people rely on validation and input messages to keep the data consistent.

Common mistakes

  • Formula rules without the $ before the column, so each cell checks a different column.
  • Writing the rule for the wrong row: it must match the top-left cell of the selection.
  • Too many rules and colours, which hide the message instead of showing it.
  • Trusting validation completely, when pasted values bypass it.

Best practices

  • Use one colour meaning per workbook, and explain it in a note or legend.
  • Keep drop-down choices in a range on a settings sheet, so they can be updated in one place.
  • Review rules with Manage Rules after copying or inserting rows.

Keyboard shortcuts

ActionWindowsMac
Open a cell's validation drop-downAlt+Down ArrowOption+Down Arrow
Open the Home tab's commands by key tipsAlt, H—
Open the Data Validation dialog by key tipsAlt, A, V, V—
Repeat the last action, such as a formatF4Command+Y

Summary

Conditional formatting applies formats automatically with highlight rules, top and bottom rules, data bars, colour scales, icon sets and formulas; a formula rule returns TRUE for the cells to format, and $ before the column formats whole rows. Data validation restricts entries with lists, limits and custom formulas, with input messages and error alerts, but pasted values can bypass it.

Quick check

1. Which rule formats whole rows of A2:D100 where column D says Late?
2. What must a formula rule return for a cell to be formatted?
3. Which error alert style refuses an invalid entry?
4. Why is data validation not a guarantee of clean data?

Key takeaways

  • Conditional formatting changes with the data; a formula rule must return TRUE for the cells to format.
  • In a formula rule, lock the column with $ to format whole rows.
  • Data validation guides typing, but pasted values can bypass it, so it is a guide rather than a guarantee.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.