By the end of this lesson you will be able to use conditional formatting to change the display format of a cell (or a range of cells) depending on the contents of that cell.
What is Conditional Formatting?
Conditional formatting is a feature in spreadsheet software (e.g., Microsoft Excel, Google Sheets) that automatically applies formatting such as colour, font style, borders or data bars when a cell meets a specified condition.
It helps to highlight important data at a glance.
It can be used for error checking, trend spotting, and creating visual reports.
Common Uses in IGCSE Context
Marking scores that are above or below a pass mark.
Highlighting duplicate entries in a list.
Showing cells that contain dates that are overdue.
Colour‑coding profit and loss figures.
How Conditional Formatting Works
The software evaluates each cell against one or more rules. If the rule evaluates to true, the associated formatting is applied.
Typical rule types include:
Cell value comparisons (e.g., \$A1>100\$).
Text contains / does not contain.
Date is before / after a given date.
Duplicate values.
Custom formulas (advanced).
Step‑by‑Step Procedure (Excel 365/2019)
Select the cell or range you want to format.
Go to the Home tab → Conditional Formatting.
Choose a rule type:
Highlight Cells Rules – for simple comparisons.
Top/Bottom Rules – for top 10%, etc.
Data Bars, Colour Scales, Icon Sets – for visual gradients.
New Rule – to create a custom formula.
Define the condition (e.g., “greater than”, “equal to”, “contains”).
Select the formatting style (font colour, fill colour, border, etc.).
Click OK**. The formatting appears instantly.
Step‑by‑Step Procedure (Google Sheets)
Select the cell or range.
Menu: Format → Conditional formatting.
In the sidebar, under “Format cells if…”, choose a condition.
Enter the value or formula (e.g., =A1<=50).
Choose the formatting style.
Click Done.
Example 1 – Highlight Pass/Fail Scores
Suppose column B contains exam scores out of 100. Pass mark is 50.
Select range B2:B20.
Create a rule: Cell value >= 50 → format with green fill.
Create a second rule: Cell value < 50 → format with red fill.
Example 2 – Flag Overdue Dates
Column C contains due dates. Highlight any date earlier than today.
Rule formula (Excel): =C2<TODAY()
Apply a light orange fill to draw attention.
Example 3 – Using a Custom Formula
Highlight rows where the total sales (column D) are greater than the average sales of the whole column.
Formula (applied to range \$A\$2:\$E\$20):
\$\text{=\$D2 > A \cdot ERAGE(\$D\$2:\$D\$20)}\$\$
Set a bold font and a blue background for the entire row.
Suggested diagram: Screenshot of the Conditional Formatting dialog showing rule creation steps.
Summary Table of Common Rules
Rule Type
Condition Example
Typical Use
Suggested Formatting
Cell \cdot alue – Greater Than
\$A1 > 100\$
Show high sales figures
Green fill
Cell \cdot alue – Less Than
\$B2 < 0\$
Identify losses
Red font
Text Contains
“Pending”
Mark unfinished tasks
Yellow fill
Date Before Today
=C3<TODAY()
Over‑due deadlines
Orange fill
Duplicate \cdot alues
(built‑in duplicate rule)
Find repeated IDs
Light blue fill
Custom Formula
=D2>A \cdot ERAGE(\$D\$2:\$D\$20)
Highlight above‑average results
Bold text, blue fill
Practice Activities
Open a new workbook and enter the data set shown below (use any numbers you like). Apply conditional formatting to colour‑code cells that are:
Above the class average (green).
Below the class average (red).
In a list of product codes, use a rule to highlight any duplicate codes.
Create a custom formula that highlights an entire row when the value in column E (profit) is negative.
Using Google Sheets, set up a rule that flags any date in column F that is more than 30 days in the past with a grey background.
Key Points to Remember
Conditional formatting does not change the underlying data, only its appearance.
Rules are evaluated in the order they appear; later rules can override earlier ones if “Stop If True” is not set.
Use absolute references ($) in custom formulas when you want the condition to refer to a fixed cell or range.
Too many overlapping rules can slow down large workbooks – keep rules as simple as possible.
Exam Tips for IGCSE ICT 0417
Read the question carefully – note whether the task asks for a specific rule (e.g., “highlight values > 75”) or a general description of how to set it up.
When writing a short answer, mention the three steps: select range, define condition, choose formatting.
If a diagram is required, sketch a simple screenshot of the Conditional Formatting menu and label the key buttons.
Remember the terminology: “rule”, “condition”, “formatting style”.