Be able to use conditional formatting to change the display format depending on the contents of a cell

Spreadsheets – Conditional Formatting (Cambridge IGCSE ICT 0417)

Learning Objective

By the end of this lesson you will be able to apply conditional formatting to change the visual appearance of a cell or a range of cells based on its contents, and to understand how this feature integrates with the other spreadsheet requirements of the IGCSE ICT syllabus.

Where Conditional Formatting Fits in the Syllabus

What Is Conditional Formatting?

• Automatically changes a cell’s visual style (fill colour, font colour, borders, data bars, icon sets, etc.) when a specified condition is true.
• It does not change the underlying data – only its appearance.
• Key benefits for IGCSE tasks:

  • Quickly spot errors or out‑of‑range values.
  • Emphasise key results such as pass/fail, profit/loss, overdue dates.
  • Provide visual cues that can be reflected in charts, reports, or web pages.

Typical Rule Types

1. Cell‑value comparisons (e.g., =A2>100).
2. Text contains / does not contain.
3. Date before / after a given date.
4. Duplicate values.
5. Custom formulas (most flexible – allows any function).

Using Functions in Conditional Formatting (Syllabus 20.1)

Any worksheet function can be used inside a custom formula. Below are the most common functions required for the IGCSE and a short example of each.

Absolute, Mixed and Relative References

• Absolute reference – \$A\$1: both column and row stay fixed when the rule is applied to other cells.
• Mixed reference – \$A1 (column fixed) or A\$1 (row fixed): useful when you want the rule to look at a specific column or row for every cell in the range.
• Relative reference – A1: changes both column and row as the rule is evaluated for each cell.
• In the example “Highlight an entire row when the sales figure in column D exceeds the column average”, the formula = \$D2 > AVERAGE(\$D\$2:\$D\$20) uses a mixed reference (\$D2) so the column stays D while the row adjusts for each cell in the selected range.

Rule Priority, “Stop If True”, and Re‑ordering

• Rules are evaluated top‑to‑bottom in the Conditional Formatting Manager.
• If two rules could apply, the first rule that evaluates to true is used unless you enable Stop If True, which prevents later rules from overriding it.
• To change the order:

  1. Open Home → Conditional Formatting → Manage Rules (Excel) or the sidebar “Conditional format rules” (Google Sheets).
  2. Select a rule and use the up/down arrows (or drag‑and‑drop in Google Sheets) to reposition it.
  3. Check the Stop If True box if you want the rule to be final.

• Performance tip: keep the number of overlapping rules low, use absolute references where possible, and delete any rules that are no longer needed.

Full Spreadsheet Workflow (Syllabus 20.2)

1. Import or enter data (e.g., CSV, manual entry).
2. Data validation – restrict entries (whole numbers 0‑100, dates not earlier than 01‑01‑2020, etc.).
3. Conditional formatting – highlight errors, out‑of‑range values, trends.
4. Sort & filter – use “Expand the selection” so formatting moves with the data.
5. Summarise – subtotals, totals, or simple pivot‑like calculations (e.g., =SUMIF).
6. Present – create charts, export to PDF, copy as picture, or save as a web page.

Step‑by‑Step Procedure (Microsoft Excel 365/2019)

1. Select the cell or range you wish to format.
2. Home ► Conditional Formatting.
3. Choose a rule type:

   • Highlight Cells Rules – simple comparisons.
   • Top/Bottom Rules – top 10 % etc.
   • Data Bars, Colour Scales, Icon Sets – visual gradients.
   • New Rule – for custom formulas (most flexible).

4. Define the condition (e.g., “greater than 100”, “contains ‘Pending’”, or enter a custom formula).
5. Pick a formatting style (fill colour, font colour, border, data bar, icon).
6. Click OK. The formatting appears instantly.
7. To edit order or enable Stop If True:

   1. Home ► Conditional Formatting ► Manage Rules.
   2. Use the up/down arrows to re‑order.
   3. Check the Stop If True box for the required rule.
   4. Click Apply then OK.

Step‑by‑Step Procedure (Google Sheets)

1. Select the cell or range.
2. Menu: Format → Conditional formatting.
3. In the sidebar, under “Format cells if…”, choose a preset condition or select “Custom formula is”.
4. Enter the value or formula (e.g., =A2<=50).
5. Choose the formatting style (fill colour, bold, etc.).
6. Click Done.
7. To add more rules, click “Add another rule”. Drag the rules up or down to set priority.
8. Google Sheets does not have a “Stop If True” option – the first true rule in the list is applied automatically.

Linking Conditional Formatting to Charts & Other Presentations

• Excel: When a chart is created from a range that contains colour‑scaled or icon‑set formatting, the series adopts those colours automatically. Updating the rule updates the chart instantly.
• Google Sheets: Colour scales and icon sets affect only the cells; charts use the default colour palette. To match colours, manually set the series colours after creating the chart.
• Exporting as an image: Select the formatted range → Copy → Paste Special → Picture. The picture can be inserted into a Word document, PowerPoint slide, or PDF (Paper 2 requirement).
• Web page export (Paper 3): File ► Save As ► “Web Page (.html, .htm)”. The HTML table retains the cell background colours, useful for the website‑authoring component.

File Management & Presentation Checklist (Syllabus 15)

1. Save the workbook in a format that preserves conditional formatting: .xlsx (Excel) or .ods (OpenDocument).
2. Before submission, protect the sheet:

   • Review ► Protect Sheet (set a password if required).
   • Allow only “Select unlocked cells” for students during an exam.

3. Check that all required formatting appears correctly in Print Preview.
4. When exporting to PDF, use “File ► Export ► Create PDF/XPS Document” – colours, borders, and data bars are retained.

e‑Safety & Data Security Checklist (Syllabus 8.2/8.3)

• Remove any hidden rows, columns, or sheets that contain formulas you do not wish to share.
• Protect the workbook or specific sheets with a password to prevent accidental changes to rules.
• Clear any unnecessary formulas that could reveal sensitive calculations before sharing the file.
• When uploading to a cloud service, ensure the sharing permissions are set to “View only” unless editing is required.

Worked Examples

Example 1 – Highlight Pass/Fail Scores

Column B contains exam scores (0‑100). Pass mark = 50.

1. Select B2:B20.
2. Rule 1: Cell Value ≥ 50 → green fill.
3. Rule 2: Cell Value < 50 → red fill.
4. In the Manage Rules window, ensure the “Pass” rule is above the “Fail” rule (or tick “Stop If True” on the Pass rule).

Example 2 – Flag Over‑due Dates

Column C holds due dates. Any date earlier than today should be highlighted.

• Excel formula: =C2<TODAY()
• Google Sheets formula: =C2<TODAY()
• Formatting: light orange fill, bold font.

Example 3 – Custom Formula – Above‑Average Sales (Uses Syllabus 20.1 Functions)

Highlight an entire row when the sales figure in column D exceeds the column average.

1. Select the whole table, e.g. \$A\$2:\$E\$20.
2. Home ► Conditional Formatting ► New Rule ► “Use a formula to determine which cells to format”.
3. Enter the formula: = \$D2 > AVERAGE(\$D\$2:\$D$20)
4. Choose a formatting style – bold text, blue background.
5. Because the column reference is absolute ($D) and the row is relative (2), the rule checks the D‑column value for each row.

Example 4 – Duplicate Product Codes

Column A contains 20 product codes. Highlight any code that appears more than once.

• Rule type: Highlight Cells Rules → Duplicate Values.
• Formatting: light blue fill.
• Useful for data‑validation checks in syllabus 20.2.

Example 5 – Colour Scale for Monthly Expenses (Link to Chart)

Column E lists monthly expenses. Apply a three‑colour scale to show low (green) → medium (yellow) → high (red) values, then create a column chart.

1. Select E2:E13.
2. Home ► Conditional Formatting ► Colour Scales → Green‑Yellow‑Red.
3. Insert ► Column Chart. In Excel the bars adopt the same green‑yellow‑red gradient because the chart is linked to the formatted range.

Summary Table of Common Rules

Practice Activities

1. Create a data set of 15 student scores (any numbers). Apply conditional formatting to colour‑code cells that are:

   • Above the class average – green fill.
   • Below the class average – red fill.

2. Enter a list of 20 product codes. Use the built‑in “Duplicate Values” rule to highlight any codes that appear more than once.
3. In a table with columns A–E, set a custom formula that highlights the entire row when the profit value in column E is negative (= $E2<0).
4. In Google Sheets, create a rule that flags any date in column F that is more than 30 days in the past with a grey background (=F2<TODAY()-30).
5. After completing the above, insert a column chart from the score column (Example 1). Observe how the pass/fail colours are reflected in the chart (Excel) or note that you must colour the series manually (Google Sheets).
6. Export the formatted table as a PDF and as a web page. Verify that the colour‑coding is retained in both outputs.

Key Points to Remember

• Conditional formatting changes appearance only – the data itself is unchanged.
• Rules are evaluated from top to bottom; use Stop If True to stop later rules from overriding earlier ones.
• Use absolute (\$A\$1) and mixed (\$A1, A\$1) references correctly in custom formulas.
• Keep overlapping rules to a minimum to avoid performance problems in large workbooks.
• Formatting is retained when the workbook is saved, exported to PDF, printed, or saved as a web page – essential for the final report or website authoring tasks.
• Combine conditional formatting with data validation for robust error‑checking.
• Protect the sheet and clear unnecessary formulas before sharing to meet e‑safety requirements.

Exam Tips for IGCSE ICT 0417

• Read the question carefully – decide whether you need to create a rule, describe the steps, or explain the purpose of a rule.
• When answering a short‑answer question, include the three core steps:

  1. Select the range.
  2. Define the condition (or enter the formula).
  3. Choose the formatting style.

• If a diagram is required, sketch a simple screenshot of the Conditional Formatting menu, label the key buttons (e.g., “Highlight Cells Rules”, “New Rule”, “Manage Rules”).
• Use the correct terminology: rule, condition, formatting style, rule priority, Stop If True.
• Remember to mention that formatting is retained when the workbook is exported to PDF or printed if the question asks about presenting the spreadsheet.
• For questions involving charts, note the difference between Excel (colour inheritance) and Google Sheets (manual colour assignment).
• Include a brief comment on protecting the sheet or removing hidden formulas when the question relates to data security or e‑safety.