Make your spreadsheet do the visual work — highlight problems, patterns, and outliers automatically.
Conditional formatting applies formatting — color, bold, borders — to cells automatically, based on rules you set. The formatting updates live: change a value and the color changes with it, instantly, with no manual work.
It's most useful for making exceptions visible at a glance. A column of sales numbers looks identical whether one is dangerously low or impressively high. Conditional formatting makes the outlier obvious without anyone having to read every row.
This is the simplest starting point. Select your range, then:
Click the first cell, then drag to the last, or press Ctrl+Shift+End to select everything.
You'll see options: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values.
Enter the value, pick a preset color scheme (or Custom Format for full control), and click OK. The cells update immediately.
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. This marks every value that appears more than once in your selection — useful for finding data entry errors in ID columns or email lists.
Color scales shade every cell on a gradient between two or three colors, based on its relative value. The lowest value gets one color, the highest gets another, everything in between is interpolated. A green-to-red scale on a profit column makes high-margin rows green and low-margin rows red without you setting a threshold.
Data bars draw a proportional horizontal bar inside each cell — like a miniature bar chart without leaving the spreadsheet. The longest bar is the cell with the highest value. At a glance you can see which rows are biggest.
Both are under Home → Conditional Formatting, and both update automatically when your underlying data changes.
This is where conditional formatting becomes genuinely powerful. Instead of a preset rule, you write your own formula — any formula that returns TRUE highlights the cell.
Select your entire data range (e.g. A2:F50), then use a formula rule. To highlight every row where column D says "Overdue":
=$D2="Overdue"The dollar sign before D locks the column (D) but lets the row ($2) vary — so the formula checks column D for each row, and highlights every cell in that row when D says "Overdue".
=A2>AVERAGE($A$2:$A$50)Apply this to A2:A50. Each cell is compared to the fixed average of the whole range.
Lock the column with a $ when you want every cell in a row to check the same column (=$D2). Lock both row and column (=$D$2) when you want every cell to compare against one fixed cell.
Home → Conditional Formatting → Manage Rules opens the rules manager — a list of every rule on the sheet, their ranges, and their priority order. Rules at the top take precedence. You can reorder them by dragging, edit any rule by double-clicking, or delete rules you no longer need.
To clear all formatting from a selection: Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.
If you set up a rule on A2 then copy the format elsewhere, the "Applies to" range in the rules manager might drift. Always check it says exactly what you intended.
The most common cause: the formula uses relative references that don't anchor correctly. If your formula is =D2="Overdue" without the $ before D, each cell checks a different column. Use =$D2 when you want to check column D for every row.
Conditional formatting recalculates every time anything on the sheet changes. Apply rules to specific ranges rather than whole columns (like D:D) — a rule on D2:D5000 is dramatically faster than a rule on the entire column D.
ExcelPro has 700+ real exercises across 7 career tracks — type actual formulas, get instant feedback.
Start practising free →