Conditional formatting
Make exceptions noticeable while keeping the underlying rule readable.
By the end, you’ll be able to:
- Choose a rule that answers a specific question
- Anchor a full-row comparison correctly
- Test rule boundaries and avoid color-only meaning
Formatting should reveal a defined exception
A team report compares Actual with Budget. A highlight can draw attention to overspending without changing either value. Define “over” as Actual strictly greater than Budget, so a team exactly on budget is not flagged.
For the sample, Support is over by 120, Operations is under by 50 and Marketing is exactly on budget. A color scale answers a different question about relative size; it does not automatically implement this budget rule.
| A | B | C | |
|---|---|---|---|
| 1 | Team | Budget | Actual |
| 2 | Support | 1000 | 1120 |
| 3 | Operations | 1500 | 1450 |
| 4 | Marketing | 800 | 800 |
Begin with a simple value rule in Excel
- Select numeric cells you want to inspect.
- Open Home → Conditional Formatting.
- Choose an appropriate value rule, such as Greater Than, and supply its threshold.
- Choose a readable format and confirm the result.
A fixed threshold works when every cell shares the same limit. Here each team has its own Budget, so a formula-based rule comparing values on the same row is more suitable. Menus vary slightly between desktop, Mac and web Excel.
Worked example: highlight the complete over-budget row
Select A2:C4, with A2 as the active top-left cell. Create a rule using a formula to determine which cells to format, enter =$C2>$B2, choose a highlight and apply it to $A$2:$C$4.
The formula returns TRUE or FALSE for each row. $C and $B keep the comparison columns fixed as the format applies across the row; the unfixed row number changes from 2 to 3 to 4. With $C$2>$B$2, every row would instead depend on Support.
Test the rule and its scope
Set Support Actual to 999, 1000 and 1001 against Budget 1000. The row should highlight only at 1001. Then inspect Manage Rules and its Applies to range. A correct formula applied to the wrong range is still the wrong report.
Check other rules for overlap and priority. Avoid stacking several fills that obscure one another. Data bars can compare magnitudes, while icons or text labels can explain status. Keep exact values available and use sufficient contrast.
Practice the logic here, apply the formatting in Excel
The browser exercises calculate the rule’s result and test reference choices. They do not create or inspect Excel’s native conditional formatting objects. Use the workbook’s hands-on steps to build and test the actual rule.
Add a separate label such as Over or Within so readers do not need to distinguish colors alone. Conditional formatting is available in current web Excel and desktop editions, but rule editors and some advanced options vary by platform.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
Define the condition, anchor references for the applied range, then test boundaries. Use text and values alongside color.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.