Excel conditional formatting and data validation
For office staff who keep trackers, registers and forms in Excel and want problems to stand out and bad entries to be stopped at the door. Complete 'Spreadsheet fundamentals for office work' first, or be comfortable with ranges and absolute references ($A$1). You'll apply built-in conditional formatting rules, write formula rules that highlight whole rows, control rule order, set up drop-down lists and limits with helpful messages, and find the invalid entries that validation doesn't catch. Instructions follow Microsoft Support for Excel for Microsoft 365 on Windows.
- Level
- Beginner
- Length
- About 66 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Apply highlight-cell, top/bottom, data bar, colour scale and icon set rules to a range
- Write a formula-based conditional formatting rule with the right mix of relative and absolute references
- Manage rule order, Stop If True and the Applies to range in the Rules Manager
- Set up data validation with a list, number or date rule, an input message and the right error alert style
- Find entries that bypass validation, such as pasted, filled or existing data, using Circle Invalid Data
Course outline
- 1.Applying built-in conditional formatting rulesLesson · 12 min
- 2.Writing a formula-based rule with the right referencesLesson · 14 min
- 3.Managing rule order, Stop If True and the Applies to rangeLesson · 11 min
- 4.Setting up data validation with messages and the right alert styleLesson · 14 min
- 5.Finding entries that bypass validation with Circle Invalid DataLesson · 9 min
- 6.Excel conditional formatting and data validation: knowledge checkKnowledge check · 14 questions
- 7.Excel conditional formatting and data validation: practical exerciseKnowledge check · 1 question
- 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: Use conditional formatting to highlight information in Excel
- Microsoft Support: Apply data validation to cells
- Microsoft Support: More on data validation
- Microsoft Support: Create a drop-down list
- Microsoft Support: Make your Excel documents accessible to people with disabilities
- Microsoft Support: Keyboard shortcuts in Excel
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.