Understanding "New Rules" in Excel’s Conditional Formatting

Conditional Formatting allows you to highlight cells or ranges based on specific criteria, making your data more readable and enabling quick visual analysis. The "New Rule" option provides granular control over when and how formatting is applied.

1. Format All Cells Based on Their Values

This rule applies formatting (Data Bars, Color Scales, or Icon Sets) based on each cell’s numeric value in the selected range.

Example – Monthly Sales Data

Month	Sales ($)
Jan	50,000
Feb	75,000
Mar	30,000
Apr	90,000
May	60,000

How to Apply:

1.     Select the sales figures (B2:B6).

2.     Go to Conditional Formatting > New Rule.

3.     Choose Format all cells based on their values.

4.     Select a format style (e.g., Data Bar or Color Scale).

2. Format Only Cells That Contain

Use this rule to highlight cells containing specific text, numbers, dates, or blanks.

Example 1 – Highlight Text ("Pending")

Task	Status
Report	Completed
Review	Pending
Design	In Progress
Feedback	Pending

How to Apply:

1.     Select the ‘Status’ column (B2:B5).

2.     Choose Format only cells that contain.

3.     Select Specific Text → containing → Pending.

4.     Set a fill color (e.g., yellow).

Example 2 – Highlight Scores > 80

Student Name	Score
Alice	85
Bob	72
Charlie	91
Diana	78

How to Apply:

1.     Select scores (B2:B5).

2.     Choose Cell Value → greater than → 80.

3.     Apply a fill color (e.g., light green).

3. Format Only Top or Bottom Ranked Values

This rule highlights top or bottom values (e.g., Top 10, Bottom 10%, Above/Below Average).

Example 1 – Top 3 Sales

Product	Sales
A	1200
B	800
C	1500
D	950
E	1100
F	700

How to Apply:

1.     Select sales (B2:B7).

2.     Choose Format only top or bottom ranked values.

3.     Select Top → 3 items.

4.     Apply a gold fill.

Example 2 – Below Average Scores

Student Name	Score
Alice	85
Bob	60
Charlie	90
Diana	70
Eva	55

How to Apply:

1.     Select scores (B2:B6).

2.     Choose Below Average.

3.     Apply a light red fill.

4. Format Only Unique or Duplicate Values

Use this rule to detect duplicates or unique entries.

Example – Duplicate Employee IDs

Employee ID
1001
1002
1003
1001
1004
1003

How to Apply:

1.     Select IDs (A2:A7).

2.     Choose Format only unique or duplicate values.

3.     Select Duplicate.

4.     Apply a light orange fill.

5. Use a Formula to Determine Which Cells to Format

This is the most powerful and flexible rule type. It allows custom formulas that return TRUE or FALSE.

Example – Highlight Rows with Negative Profit

Product	Sales	Cost	Profit
A	100	80	20
B	150	160	-10
C	200	150	50
D	120	130	-10

How to Apply:

1.     Select the range (A2:D5) starting from the top-left cell.

2.     Choose Use a formula to determine which cells to format.

3.     Enter:

4.      =$D2<0

5.     Apply a light red fill.

Explanation:

• $D2 locks the column (Profit) while allowing row numbers to adjust (D3, D4, etc.).