What is conditional formatting in Excel?

Conditional formatting automatically changes cell formatting when values meet rules, such as duplicates, overdue dates, high numbers, or formula-based conditions.

Can conditional formatting highlight an entire row?

Yes. Use a formula-based rule with a locked column reference, such as =$D2="Late", and apply it to the full row range.

Why is conditional formatting applying to the wrong cells?

The Applies to range or relative references in the formula are usually wrong. Check both in Manage Rules.

Can conditional formatting slow down Excel?

Yes. Many overlapping rules across large ranges can slow workbooks. Keep ranges tight and remove duplicate rules.

Tutorials - 2026-05-12

Conditional Formatting in Excel: Complete Guide

Learn conditional formatting in Excel for highlighting duplicates, overdue dates, top values, low stock, status colors, and formula-based rules.

What Conditional Formatting Does

Conditional formatting changes the appearance of cells based on rules. It helps you quickly see exceptions, trends, risks, and important values without manually scanning the spreadsheet. Common uses include: Highlighting overdue dates Finding duplicate values Coloring high and low numbers Flagging missing data Creating status colors Building simple heat maps

Highlight Values Greater Than a Number

Select the range, then go to: Home -> Conditional Formatting -> Highlight Cells Rules -> Greater Than Example: highlight sales above 10000 or expenses above budget. This is useful for dashboards and monthly reports because the formatting updates automatically when values change.

Highlight Duplicates

To find duplicate entries: Home -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values Use this for invoice numbers, customer IDs, employee IDs, product codes, and imported lists. If you need to remove duplicates after reviewing them, use the Remove Duplicates command on the Data tab.

Format Overdue Dates

Use a formula rule to highlight dates before today: \\\ =A2<TODAY() \\\ Apply the rule to the date column. This works well for due dates, renewal dates, follow-up dates, and project deadlines.

Use Formula-Based Conditional Formatting

Formula rules are the most powerful option. For example, highlight an entire row when the status is Late: \\\ =$D2="Late" \\\ The dollar sign locks the status column while allowing the row number to change.

Best Practices

Use simple color rules that people can understand quickly Avoid too many colors on one sheet Keep formulas short and test them on one row first Use consistent colors across reports Document important rules in a notes section

Related Guides

Absolute Reference in Excel IF function COUNTIF function Excel Dashboard Guide

Rule Examples That Work in Real Reports

Goal Formula rule Highlight overdue open tasks =AND($D2< TODAY(),$E2<>"Closed") Highlight missing required fields =$B2="" Highlight rows above target =$F2>=$G2 Highlight duplicates =COUNTIF($A:$A,$A2)>1 Use formula-based rules when the highlight depends on more than the current cell.

Expert Rule Management

Conditional formatting problems often come from overlapping rules. Open Manage Rules and check: Applies to range Rule order Stop If True behavior Relative vs absolute references Duplicate rules created by copy and paste For dashboards, use color sparingly. Too many rules make reports harder to scan and slower to maintain.

Related Guides

Use Absolute Reference in Excel to understand locked references in formatting formulas, and COUNTIF for duplicate rules.

Eight copy-paste rule templates

Overdue: \

Frequently asked questions

What is conditional formatting in Excel? Conditional formatting automatically changes cell formatting when values meet rules, such as duplicates, overdue dates, high numbers, or formula-based conditions.
Can conditional formatting highlight an entire row? Yes. Use a formula-based rule with a locked column reference, such as =$D2="Late", and apply it to the full row range.
Why is conditional formatting applying to the wrong cells? The Applies to range or relative references in the formula are usually wrong. Check both in Manage Rules.
Can conditional formatting slow down Excel? Yes. Many overlapping rules across large ranges can slow workbooks. Keep ranges tight and remove duplicate rules.