Tutorials›Excel›Conditional Formatting
🟢 Free Demo
Excel TutorialTopic 17 of 31

Conditional Formatting

Automatically colour, highlight, and visualise cells based on their values — no manual formatting needed.

✅ What You Will Learn

›How to apply built-in Conditional Formatting rules
›How to highlight cells above or below a threshold
›How to use data bars, colour scales, and icon sets
›How to write custom formula-based rules
›How to manage, edit, and remove Conditional Formatting rules

Conditional Formatting automatically changes the appearance of cells based on their values. Instead of manually colouring cells that exceed a target, you define a rule once and Excel applies it to the entire range — including any new data added later.

The built-in rules cover the most common needs: highlight cells greater/less than a value, top/bottom N%, above/below average, duplicate values, and date rules. For anything more specific, you can write a formula-based rule — giving you full flexibility to format any cell based on any condition in the worksheet.

Data Bars, Colour Scales, and Icon Sets are special visual formats that create mini-charts inside cells — a data bar proportional to the value, a colour gradient from red to green, or icons like arrows and stars — making large tables scannable at a glance.

Examples

Example 1Highlight cells below target — red for below, green for at or above
Steps:
1. Select the sales amount range (e.g. C2:C100)
2. Home tab → Conditional Formatting → Highlight Cell Rules → Less Than
3. Enter the target value (e.g. 20000) → choose "Light Red Fill"
4. Add a second rule: Greater Than or Equal To 20000 → "Green Fill"

Or use a single rule with a formula:
Home → Conditional Formatting → New Rule → Use Formula
Formula: =C2<$F$1     (where F1 contains the target)
Format: Red fill
💡

Formula-based rules apply to the selected range. The formula must reference the top-left cell of the selection. The $ signs lock the target cell reference across all rows.

Example 2Icon sets — show up/down/neutral arrows for monthly change
Steps:
1. Calculate % change in column D: =(C2-B2)/B2
2. Select D2:D100
3. Home → Conditional Formatting → Icon Sets → 3 Arrows (Coloured)
4. Click "Manage Rules" → Edit Rule to set:
   Green arrow: value >= 5% (number, percent)
   Yellow arrow: value >= -5%
   Red arrow: below -5%
OUTPUT
D column shows ↑ green for growth above 5%, → yellow for flat, ↓ red for decline over 5%
💡

Change the icon set type (Arrows, Flags, Traffic Lights, Stars, Ratings) to match your reporting context. Traffic lights are intuitive for status dashboards.

📌 Key Points to Remember

  • ✓Conditional Formatting is applied in rule priority order — first matching rule wins
  • ✓Formula-based rules reference the top-left cell of the selection; $ locks only what you want locked
  • ✓Rules can format entire rows: select full rows, write formula referencing the key column ($A2 — lock column, free row)
  • ✓Avoid applying Conditional Formatting to entire columns (A:A) — it slows Excel on large datasets
  • ✓Manage Rules (Home → Conditional Formatting → Manage Rules) shows all rules and lets you reorder priority

🏢 Real-World Application

MIS analysts apply Conditional Formatting to exception reports — every row where actual falls below budget is highlighted red, making it instantly visible to managers scanning hundreds of rows. Operations teams use colour scales on delivery time data to immediately identify which warehouses have the fastest and slowest dispatch times without reading every number.

⚠️ Common Mistakes to Avoid

WRONGFormula rule applying to wrong cells — entire selection turns one colour
FIXThe formula must reference the top-left cell of your selection. If you selected C2:C100, the formula should reference C2, not C1 or an absolute reference like $C$2.
WRONGToo many Conditional Formatting rules on the same range — hard to manage and slows the file
FIXConsolidate rules where possible. Use one formula that combines conditions: =AND(C2<target, D2="North") instead of two separate rules.
WRONGCopying cells with Conditional Formatting to a new location — the rules copy but ranges conflict
FIXAfter pasting, check Manage Rules and update the "Applies to" range to the correct new location.
✏️Test Yourself

You apply a Conditional Formatting rule with formula =B2>100 to the range B2:B20. Which cells will be formatted when B5 contains 150?

❓ Frequently Asked Questions

How do I highlight an entire row based on one column value?

Select the entire row range (e.g. A2:F100). Create a formula rule using a mixed reference that locks the column: =$C2="Overdue". The $ before C locks the column but lets the row shift for each row.

Can I apply Conditional Formatting based on another cell value?

Yes — formula-based rules can reference any cell. =C2>$G$1 formats C2 when it exceeds whatever is in G1. Change G1 and the formatting updates automatically across all cells.

How do I copy Conditional Formatting to another range without copying cell content?

Copy the source cell, select the destination range, Paste Special (Ctrl+Alt+V) → Formats only. This pastes only the formatting (including Conditional Formatting rules) without the cell values.

✏️ Practice Exercise

Create a performance scorecard table with 15 employees, their department, monthly sales, target, and % achievement. Apply: (1) Red/green fill highlighting for below/above target in the achievement column, (2) Data bars in the Sales column, (3) An icon set (traffic lights) based on achievement %, (4) Highlight the entire row yellow for employees in the "South" region using a formula-based rule.

← PreviousDashboard DesignNext →Data Validation
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.