Excel Tutorials
Every built-in rule, plus the formula-based tricks that do things the ribbon buttons can't, highlighting a whole row, catching duplicates across columns, flagging weekends and overdue dates automatically. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Conditional Formatting automatically changes how a cell looks, its fill color, font color, an icon, or a data bar, based on its value or a formula you write, and it stays live, updating the moment the underlying data changes. The built-in rules cover common cases like "highlight anything over 100," but the real power shows up once you write your own formula-based rule, which can react to practically anything.
That formula-based option is what unlocks tricks like highlighting an entire row based on one column, something none of the built-in ribbon options can do on their own.
Step-by-step, matching the video above.
Click and drag to select the cells you want the formatting to apply to.
On the Home tab, click Conditional Formatting.
Highlight Cells Rules for simple comparisons (Greater Than, Between, Text that Contains), Top/Bottom Rules for rankings, Data Bars, Color Scales or Icon Sets for a visual gradient across the range.
Enter the value or threshold, then choose or customize the formatting (fill color, font color) to apply when it's met.
Choose New Rule > Use a formula to determine which cells to format to write custom logic the built-in options don't cover.
Conditional Formatting > Clear Rules removes formatting from the selected cells or the whole sheet.
The single most important thing to understand before writing any formula-based rule, and the reason most formula rules that "don't work" actually fail.
Whatever formula you write is evaluated for the first (top-left) cell of your selection, then applied to every other cell relative to it, exactly like copying a normal formula.
=$B2="Overdue" applied to a range like A2:D100 locks column B in place (the $ before B) while letting the row number shift naturally, meaning every column in that row gets checked against B's value in that same row.
A formula like =B2="Overdue" without the $ shifts its column reference too as it applies across a row, checking column B for column A's cells, column C for column B's cells, and so on, producing results that look almost right but aren't.
| A: Task | B: Status | C: Owner |
|---|---|---|
| Book venue | Overdue | Maria |
| Send invites | Done | David |
Select A2:C100, create a formula rule with =$B2="Overdue", and apply a red fill. Row 2 turns red entirely, task, status and owner all highlighted, because the column lock on B means every cell in that row checks the same Status cell, while the row number still advances normally down to row 3, 4, and beyond.
A handful of the most useful custom rules, ready to adapt directly.
=COUNTIF($A$2:$A$100,A2)>1 flags any value in column A that appears more than once in the range, fully locked so it always checks the same full range no matter which cell it's evaluating.
=WEEKDAY(A2,2)>5 returns TRUE for Saturday or Sunday, using the version of WEEKDAY that numbers Monday as 1 through Sunday as 7.
=A2<TODAY() flags any date earlier than today, recalculating automatically every day since TODAY() always returns the current date.
=A2<AVERAGE($A$2:$A$100) flags any value below the average of the full range, updating automatically as the data and its average both change.
=COUNTBLANK($A2:$D2)>0 applied to A2:D100 highlights an entire row if anything in columns A through D on that row is empty, useful for spotting incomplete entries at a glance.
These three don't need a single formula, but knowing how to customize them beyond the default is where they go from decent to genuinely useful.
Fills part of each cell with a colored bar proportional to its value, turning a plain number column into an instant visual comparison. Edit Rule lets you set a custom Minimum and Maximum instead of Excel's automatic range, useful when one outlier value would otherwise shrink every other bar.
Shades every cell somewhere along a 2 or 3-color gradient based on its value relative to the rest of the range, commonly green-yellow-red for a quick "how healthy is this number" read across a whole table at once.
Adds a small icon, arrows, traffic lights, stars, next to each value based on which threshold it falls into. Edit Rule lets you set the exact percentage or value cutoffs for each icon, and reverse the icon order if higher values should show a lower-ranked icon.
A formula-based rule can apply a Data Bar, Color Scale or Icon Set format too, not just a solid fill, giving you the precision of a custom formula with the readability of a built-in visual style.
Once a sheet has more than one conditional formatting rule, understanding how they interact matters.
Conditional Formatting > Manage Rules lists every rule on the sheet, in the order they're evaluated.
Rules are evaluated top to bottom in the Manage Rules list, drag a rule up or down to change its priority relative to the others.
Checking this box on a rule prevents any rules below it from also applying to the same cell, useful when two rules could otherwise conflict or visually stack on top of each other.
Without locking the column reference, a formula rule shifts incorrectly as it applies across each row, producing results that look almost right but check the wrong cells.
A formula rule is always evaluated relative to the top-left cell of the selection, not whichever cell happened to be active when you opened the dialog. Double-check what that top-left cell actually is before writing the formula.
Without checking Manage Rules, it's easy to end up with two conflicting rules on the same cells, where only the higher-priority one, or neither, visibly applies.
A rule applied to all of column A instead of a bounded range like A2:A500 forces Excel to evaluate the formula against far more cells than necessary, which can noticeably slow down a large workbook.
A red flag shouldn't need a formula behind it to show up.
Free 14-day trial. No credit card required.
A feature that automatically changes a cell's appearance, color, icon, or bar, based on its value or a formula, updating live as the underlying data changes.
Select the full range, create a New Rule > Use a formula, and write a formula referencing just the first row with the column locked, like =$B2="Overdue", then apply it to the whole selection.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, then write a formula that returns TRUE or FALSE based on the top-left cell of your selection.
It locks a column or row so it doesn't shift as the formula applies across your selection. Locking just the column (like $B2) lets a rule fill an entire row based on one column's value.
Use the built-in Highlight Cells Rules > Duplicate Values option, or write a formula like =COUNTIF($A$2:$A$100,A2)>1 for more control over exactly which range counts.
For weekends, use =WEEKDAY(A2,2)>5. For overdue dates, use =A2
Go to Home > Conditional Formatting > Manage Rules to see every rule, reorder them by dragging, and control which one takes priority with Stop If True.
The most common cause is a missing or misplaced $ in the formula, causing it to reference the wrong cell as it applies down or across the selection.
Yes, applying formula-based rules to an entire column (like A:A) instead of a bounded range can noticeably slow down a large workbook. Apply rules to a specific range or a Table instead.
Select the cells, go to Home > Conditional Formatting > Clear Rules, and choose to clear rules from the selected cells or the entire sheet.
Yes, open Conditional Formatting > Manage Rules, select the Color Scale rule, and click Edit Rule to change the colors and the value thresholds each color represents.
Every business starts with a spreadsheet. Updoot is where you scale past it, no conditional formatting formula required.
Start Your Free Trial →