How to Use Conditional Formatting in Excel

Introduction

You have a revenue report with dozens of rows and need to spot which reps missed their target. Scrolling through every number is slow and you'll miss things. Conditional formatting applies colors, icons, or font changes to cells automatically based on rules you set.

The Dataset

Here's a quarterly sales table we'll use throughout:

	A	B	C	D
3	Rep	Region	Revenue	Target
4	Sarah	East	9200	8000
5	James	West	5400	8000
6	Maria	East	11300	8000
7	David	West	7800	8000
8	Priya	South	8000	8000
9	Marcus	South	6100	8000

Three reps hit or exceeded their target, three fell short. We want that to be visible without reading every number.

Highlight Cells Rules

Select the revenue range (C4:C9), then go to Home > Conditional Formatting > Highlight Cells Rules. You'll see options like Greater Than, Less Than, Between, and Equal To.

To highlight every revenue figure below 8000 in red:

Select C4:C9.
Click Home > Conditional Formatting > Highlight Cells Rules > Less Than.
Enter 8000 in the value field.
Choose Light Red Fill with Dark Red Text from the dropdown (or pick a custom format).
Click OK.

Cells C5 (5400), C7 (7800), and C9 (6100) turn red immediately. C8 stays unformatted because 8000 is not less than 8000.

Rep	Revenue	Formatted?
Sarah	9200	No
James	5400	Red
Maria	11300	No
David	7800	Red
Priya	8000	No
Marcus	6100	Red

The formatting updates automatically. If James closes a deal and his revenue jumps to 8500, the red fill disappears on its own.

Text-based rules work the same way. Select the Region column (B4:B9), choose Text That Contains, and type "East" to highlight all East region entries.

Top/Bottom Rules

Sometimes you don't care about a fixed threshold. You just need to see who's at the top or bottom of the list.

Select C4:C9, then go to Home > Conditional Formatting > Top/Bottom Rules. The options include:

Top 10 Items / Bottom 10 Items: Highlights the top or bottom N values. You can change the number from 10 to whatever you need.
Above Average / Below Average: Highlights values above or below the average of the selected range.

The average revenue across all six reps is 7967. Choosing Below Average highlights James (5400), David (7800), and Marcus (6100). These are the three values pulling the team average down.

Key takeaway: Top/Bottom rules adjust automatically when the data changes. If Marcus lands a big quarter and jumps to 12000, the below-average set recalculates and his cell loses the formatting. You never have to update the rule manually.

Color Scales and Data Bars

Highlight rules either format a cell or don't. Color scales and data bars show relative differences across the entire range.

Select C4:C9, then go to Home > Conditional Formatting > Color Scales. The default green-yellow-red scale assigns green to the highest value, red to the lowest, and blends the colors for everything in between.

Rep	Revenue	Color
Sarah	9200	Light green
James	5400	Red
Maria	11300	Dark green
David	7800	Yellow-orange
Priya	8000	Yellow-green
Marcus	6100	Orange-red

Maria's cell is the darkest green, James is red, and everyone else falls in between. You can read the ranking from the colors without comparing numbers.

Data bars draw a horizontal bar inside each cell instead of changing the color. Longer bars mean higher values. Select the range and choose Home > Conditional Formatting > Data Bars.

Both recalculate automatically when values in the range change. If you insert a row within the formatted range, Excel expands the rule to include it. Typing data below the range won't be covered unless you update the rule or use an Excel Table.

Formula-Based Rules

The built-in rules handle fixed thresholds and rankings. Formula-based rules let you write your own condition, which means you can use the same logic you'd put in an IF formula to control formatting.

To highlight entire rows where Revenue falls below Target:

Select the full data range A4:D9 (not just the revenue column, because you want the entire row highlighted).
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter the formula: =$C4<$D4
Click Format, pick a fill color (like light red), and click OK.

The dollar sign before C and D locks the columns so the rule always compares Revenue to Target regardless of which column it's evaluating. The row number (4) is left unlocked so it shifts down for each row.

Rep	Region	Revenue	Target	Row Highlighted?
Sarah	East	9200	8000	No
James	West	5400	8000	Yes
Maria	East	11300	8000	No
David	West	7800	8000	Yes
Priya	South	8000	8000	No
Marcus	South	6100	8000	Yes

The entire row lights up, not just the revenue cell.

You can use any formula that returns TRUE or FALSE. A few practical examples:

Goal	Formula
Revenue below target	`=$C4<$D4`
East region rows	`=$B4="East"`
Revenue below target AND East region	`=AND($C4<$D4, $B4="East")`
Duplicate names	`=COUNTIF($A$4:$A$9, $A4)>1`

The COUNTIF formula highlights any name that appears more than once, so you can spot duplicates without running the Remove Duplicates tool.

Managing Rules

As you add rules, they stack up. To see everything in one place, go to Home > Conditional Formatting > Manage Rules. The Rules Manager shows every rule applied to the current selection or the entire sheet.

How the Rules Manager works:

Rules apply in order. If two rules apply to the same cell, the one higher in the list takes priority. You can drag rules up or down to change the order.
Stop If True. Check this box on a rule to prevent any rules below it from applying to cells that match. This is useful when you have multiple tiers (like green for above target, yellow for close, red for far below) and don't want them overlapping.
Editing and deleting. Double-click a rule to modify its formula or formatting. Select a rule and click Delete to remove it.

If your sheet feels sluggish, check the Rules Manager for rules that cover large ranges. Each rule forces Excel to evaluate every cell in its range on every recalculation. Removing unused rules or narrowing the range speeds things up.

Conclusion

Highlight rules and color scales cover the standard cases. Formula-based rules handle anything more specific, using the same condition logic you'd write in an IF or AND formula.

For spotting and removing duplicate entries, see our guide on removing duplicates in Excel. To control data entry with dropdown menus, check out our guide on drop-down lists.