Conditional Formatting and Statements

Advanced Conditional Formatting

Learning Outcome

Learners have already studied:

Cell Styles for consistent formatting

Basic Conditional Formatting rules:

IF statements for logical decisionS

Advanced Conditional Formatting builds on this by:

Using formulas instead of fixed values

Automatically adjusting when data changes

Visualizing patterns, not just individual values

Think about how you use highlighters in your notebook.

You don’t recheck everything every time.

Your eyes immediately know where to look.

Advanced Conditional Formatting does the same thing for Excel.

It teaches Excel:

What is important?

What needs attention

What is doing well or poorly

Basic Conditional Formatting

answers simple questions:

• Is this due today?

• Is this value above average?

• Is this product performing better than others?

To answer these questions, Excel uses:

What Is Advanced Conditional Formatting?

Advanced Conditional Formatting uses:

to automatically format cells based on complex conditions.

It helps users:

Visualize large datasets quickly

​​Identify trends and outliers

Track deadlines and performance 

Focus only on what matters

Formula-Based Conditional Formatting

Think of Custom Rules in a Game

Instead of fixed rules, you decide:

What It Does

• Uses formulas like =B2>50000

• Can refer to other cells

• Updates automatically when data changes

Example Use Case:

Highlight sales greater than target

Highlight tasks due today

Highlight completed vs pending status

Icon Sets – Status at a Glance

Imagine: Traffic Lights

What Icon Sets Do:

Use arrows, symbols, or traffic lights

Quickly show performance levels

Ideal for dashboards and reports

Example:

Sales > 50,000

Sales between 25,000–50,000

Sales < 25,000

Color Scales – Seeing Patterns, Not Just Numbers

Think of Temperature Maps

Red

Blue

Hot

Cold

What Color Scales Do:

Heat map

• Apply gradient colors

• Show high, medium, and low values

• Help identify trends and clusters
   

Used for:

Data Bars – Visual Comparison Inside Cells

Data bars:

Appear inside cells

Show relative value lengths

Allow comparison without charts

Applying Advanced Conditional Formatting

Steps:-

Select the data range

Go to Home → Conditional Formatting

• New Rule (for formulas)

 Practical Scenario 1: Highlight Today’s Due Orders

Goal: Highlight all orders due today

Formula Used

=D4=TODAY()

Result:

• All matching dates highlighted automatically

• Changes daily without manual updates

Practical Scenario 2: Highlight Upcoming Deadlines (Next 30 Days)

Formula

=AND(TODAY()<=D1, D1<=TODAY()+30)

Use case:

• Project deadlines
   

• Task tracking
   

• Attendance or schedules

Multiple Rules and Rule Priority

Excel allows multiple rules on the same data

Rules are processed top to bottom

Priority can be adjusted

Performance Considerations

Large ranges + complex formulas = slower files

Use Conditional Formatting only where needed

Prefer simple formulas when possible

Summary

Which feature uses arrows or symbols to show performance?

Which feature uses arrows or symbols to show performance?