Why can a spreadsheet cell change colour without anyone manually recolouring it? Conditional formatting evaluates a rule against a cell or row and applies a visible format when the rule is met. The important skill is not making a colourful table: it is choosing the correct test, applying it to the intended range and verifying that the highlighted data really satisfy the condition.
Ministry of Education V3.0 Reference 371 explains conditional formatting in the 2027 Singapore–Cambridge SEC G3 Computing K349 syllabus, Module 3.1.3. This guide teaches rule design, numerical thresholds, text conditions, mixed and absolute references, multiple rule conflicts, blanks, comparison with ordinary formulas, and checking the underlying information. The scenarios are original and fictional; each shows what can be concluded from a highlight and what cannot.
This independent eduKateSG reference is not an official MOE, SEAB or Cambridge publication. The official 2027 K349 syllabus is the authority for assessment scope. Menu wording can differ between spreadsheet applications and releases. Use invented records in practice; do not upload genuine learner marks or private records to an unsuitable service. Accessibility and clear labels matter as much as colour.
1. The official syllabus boundary
Module 3.1.3 asks students to use Conditional Formatting to update cell formatting automatically according to one or more rules. Module 3.1.1 separately requires relative, absolute and mixed references to behave correctly when formulas are copied. The 2027 laboratory assessment includes spreadsheets, so students need to understand both the visual effect and the logic behind it.
A formatting rule is not a complete data-validation or data-cleaning system. It highlights information but does not automatically prove that every record is correct. The learning goal is to choose and test a defensible condition, apply it consistently, and recognise when a visual signal could mislead a reader.
2. The simplest rule compares a numerical value
Imagine an invented inventory column containing quantities 3, 5, 8 and 2. A rule marking values below five should highlight 3 and 2, but not 5 or 8. The exact boundary matters: ‘below five’ excludes five, while ‘five or fewer’ includes it.
Before using the interface, calculate the expected highlighted rows by hand. This gives an independent answer against which to compare what the spreadsheet displays. If the workbook highlights the row with five for a ‘less than five’ rule, the condition or referenced cell may have been set incorrectly.
3. Formatting follows the current data
When a referenced value changes from 3 to 6, a less-than-five highlighting rule should stop matching it. This automatic re-evaluation is why conditional formatting is useful for dynamic reports. A manually applied fill colour, in contrast, may remain regardless of whether the value still satisfies the intended condition.
A reader should therefore distinguish ordinary visual formatting from rule-controlled formatting. A cell that looks red is not necessarily an automatic alert. When reviewing a workbook, inspect the conditional formatting rule and the actual numeric value rather than relying on its appearance.
4. Apply the rule to the intended range
Suppose quantities are in C2:C11. A formatting rule applied to C2:C11 will affect those cells; applying it accidentally to C3:C11 omits the first record. A good setup checks the exact selected range before defining a rule.
This sounds like a simple interface step, but it is a common source of hidden errors. A formula or condition may be correct while the workbook excludes one row or includes a total row that should not be classified. Write the intended range alongside the criterion, then check the first and last affected cells.
5. A strict comparison differs from an inclusive one
The condition C2<5 is true for quantity 4 but false for quantity 5. The condition C2<=5 would be true for both. A programme describing items with fewer than five units must not silently change the threshold to include exactly five.
For revision, test three values: one below, one exactly at the threshold and one above. A student who can predict all three outcomes understands the condition better than someone who merely watches one sample cell turn colour.
6. A formula-based rule can inspect another column
An entire row of a fictional inventory might need highlighting when its quantity in column C is low. If the formatting range begins at A2 and spans A2:D11, a rule based on =$C2<5 can evaluate column C for each corresponding row, provided the rule is configured relative to the top-left cell.
The dollar sign before C fixes the referenced column, while the row number remains relative. That lets the condition read C2, C3, C4 and so on as the row changes. A poorly anchored rule might test the wrong field for cells across the row. Its appearance may be consistent without being correct.
7. Relative and absolute references are not decoration
The reference C2 is relative in both its column and row, $C2 fixes the column only, C$2 fixes the row only, and $C$2 fixes both. These forms can have different effects when a formula is copied or a conditional formatting formula is applied across multiple cells.
Select the reference according to the task. If each row should be judged by its own quantity, fixing row two is likely a mistake. If every cell should compare against one fixed threshold stored in F1, an appropriate absolute reference to $F$1 can keep that threshold steady.
8. A fixed threshold can come from a cell
A rule may compare a quantity with a threshold stored in F1, such as =$C2<$F$1 for a row-based format. If F1 contains five, the rule flags quantities below five. Changing F1 to seven changes which rows satisfy the comparison without editing each rule individually.
This makes the workbook easier to maintain but adds a dependency. A reader should know that the alert threshold comes from F1, check that it is valid and confirm that the reference is fixed when required. An apparently mysterious colour change may simply reflect an updated threshold.
9. Blank cells need deliberate interpretation
An empty quantity field can behave differently from a measured quantity of zero. Some spreadsheet comparison contexts may treat a blank as zero, producing an unwanted alert. When the requirement is to highlight only present numerical values, the condition should explicitly guard against blanks.
For example, a suitable formula-style condition can include AND(C2<>””,C2<5), adjusted for the selected range and application. The precise behaviour depends on source-cell types and application settings, so test a blank cell as a separate case. Do not assume every unfilled record should be classified as a real zero quantity.
10. Conditional formatting does not repair wrong data
Suppose a clerk accidentally types 300 instead of 3. A rule flagging only quantities below five will not highlight that record. The absence of a highlight does not prove the value was entered accurately; it merely indicates that the specified condition did not match.
This distinction between detection rules and trustworthy input is fundamental. A separate data-validation requirement might restrict allowed quantities, while a review process can check suspicious entries. A conditional formatting feature should not be portrayed as a substitute for source verification.
11. Multiple rules can overlap
A workbook might flag quantities below five in one colour and values above twenty in another. Those rules do not overlap for an ordinary numeric value. But a different pair, such as values below ten and below five, both match a quantity of three.
When rules overlap, precedence and formatting settings can affect the displayed result. Check the rule-management interface and explicitly test a value satisfying more than one rule. The correct visual outcome depends on the particular rules and application behaviour rather than a universal assumption that the last rule always wins.
12. Text alerts depend on the exact requirement
A fictional status column may contain ‘Pending’, ‘Approved’ and ‘Closed’. A rule could identify an exact status or text containing a particular word. These conditions differ: a cell containing ‘Pending review’ includes the word ‘Pending’ but is not equal to the single text value ‘Pending’.
If status labels are manually entered, spelling, capitalisation and stray spaces can produce inconsistent results. A student’s explanation should specify whether the condition tests an exact value, a substring or another property. Appearance does not replace a clear definition of the data.
13. Formatting can reveal trends without calculating them
A heatmap-style colour scale can make numerical differences easier to scan, while threshold rules identify values meeting a defined condition. These are different visual approaches. A scale expresses relative magnitude within a range; a binary rule describes whether a predicate is satisfied.
A colour scale should not be confused with evidence that a particular value is safe, good or bad. Relative position depends on the dataset and scale settings. A small classroom table can change its visual spread dramatically when an extreme value is added, even if most original values stay unchanged.
14. Do not rely solely on colour
Readers with colour-vision differences, grayscale printing or poor screen contrast may not perceive a red–green distinction reliably. Pair conditional formatting with unambiguous headings, readable numerical values and, where appropriate, text labels or other cues.
The educational purpose of a format is to assist interpretation. A colourful dashboard that obscures text or uses weak contrast may be less effective than a simpler one. Students should evaluate whether a reader can understand the condition without relying entirely on hue.
15. Copying formulas and applying formatting are different actions
A cell formula calculates a value; a conditional formatting rule determines the appearance of selected cells based on a condition. A copied formula may need relative or absolute references to produce a numerical result, while a formatting formula needs references that track the intended row or comparison cell.
Students sometimes adjust a cell’s value just to make the colour look right, which reverses the relationship between evidence and presentation. Keep the actual data as the source, inspect the logical test, and use a known expected result to judge whether the formatting is correct.
16. A short audit catches most classroom errors
Check the range, the exact operator, the threshold, the reference anchoring, and three cases: below, at and above the boundary. Then test a blank, text entered instead of a number and a value changed after the rule was created.
This small audit provides stronger evidence than a single screenshot. It also tells a tutor where an error begins: at the source value, the rule expression, the range, or the display. The learner should explain that a visual cue is a computed consequence of the data and condition, not the data themselves.
Worksheet: fictional inventory
A2:D11 = rows with item, category, quantity, note
C2:C11 = numeric quantities
F1 = target threshold (5)
Highlight quantity cells: C2:C11 with C2<5
Highlight whole rows: A2:D11 using =$C2<$F$1
Avoid blank quantity cells: =AND($C2<>"",$C2<$F$1)
Expected if C2=3, C3=5, C4=8, C5=2:
Rows 2 and 5 match a strict below-five test.A twelve-stage Rule–Range–Reference checking protocol
This independent teaching protocol is not an official assessment rubric. It gives a learner a reliable sequence for creating or reviewing spreadsheet conditional formatting. It begins with the meaning of the data and ends with a contrasting test that can reveal a hidden reference, range or boundary error.
Stage 1 — Describe the data field
State what the highlighted value means, including its units and whether blank or text values are allowed.
Stage 2 — State the condition in plain language
Write a sentence such as ‘highlight quantities strictly below five’, then translate it into the appropriate comparison.
Stage 3 — Identify the comparison operator
Distinguish less than from less than or equal to; the exact threshold is usually a meaningful boundary.
Stage 4 — Select the application range
Check the first and last selected cells or rows. The rule cannot affect a record excluded from its range.
Stage 5 — Locate any threshold cell
If the condition uses a value from F1, verify its contents and whether it should remain fixed.
Stage 6 — Choose relative or absolute references
For row-based conditions use a formula such as =$C2<$F$1 only when the applied range and task make that reference pattern appropriate.
Stage 7 — Test the first row manually
Read the first row’s quantity and calculate the Boolean condition on paper before trusting the colour.
Stage 8 — Test a middle and final row
Confirm that the condition shifts with row numbers and does not accidentally keep comparing every row against C2.
Stage 9 — Test an exact boundary
If the threshold is five, a value equal to five should not satisfy a strict less-than-five condition.
Stage 10 — Test blanks and incorrect types
Distinguish a missing quantity from a genuine zero; inspect whether special handling is required.
Stage 11 — Check simultaneous rules
If more than one rule can match, inspect the rule order and actual formatting result under the chosen application.
Stage 12 — Explain the limitation
State that a highlight reports a rule match; it does not prove the underlying data are complete, accurate or ethically interpreted.
Twelve original conditional-formatting worked cases
Worked case 1 — A strict restock threshold
With quantities 3, 5 and 8, a rule ‘less than 5’ matches only 3. Exactly five is not less than five. This is a boundary question before it is a colour-selection problem.
Worked case 2 — An inclusive threshold
The rule ‘less than or equal to five’ matches both 3 and 5 from the same sample. One extra equality sign changes the intended result at the cutoff.
Worked case 3 — A changed quantity
A cell originally containing 3 matches a below-five rule. After its value changes to 6, the rule should stop matching automatically; a manually applied fill colour might not.
Worked case 4 — One missed row in the range
A correct rule set for C3:C11 fails to evaluate C2, even if C2 contains a qualifying quantity. The problem is not the formula but the incomplete application range.
Worked case 5 — Using a fixed column across rows
For a row range A2:D11, the formula =$C2<5 bases each row’s highlight on column C. The column stays fixed while the row reference can advance with the applied rows.
Worked case 6 — Accidentally fixing row two
A rule based on =$C$2<5 can compare every row with C2 rather than its own quantity. The result may colour all rows the same way, even when they contain different values.
Worked case 7 — A threshold stored in F1
With F1 set to 5, the condition =$C2<$F$1 uses a fixed comparison value. Changing F1 to 7 changes the matching set without manually editing every data cell.
Worked case 8 — A blank treated as zero
A blank quantity may meet a numerical less-than threshold in a spreadsheet evaluation context. If blank means missing data, a separate nonblank test is needed to avoid misrepresenting absence as a measured low stock.
Worked case 9 — An input of text instead of a quantity
The source cell contains ‘few’ rather than a number. Its appearance under a numerical rule is not reliable evidence of a valid quantity. Data format requirements should be checked separately.
Worked case 10 — Two thresholds overlap
Rules ‘less than 10’ and ‘less than 5’ both match quantity 3. The visible formatting may depend on rule priority and properties, so the workbook needs a deliberate decision about overlapping formats.
Worked case 11 — Manual formatting masquerades as a rule
A student colours a cell manually and changes its value from 3 to 9. The colour can remain. Checking the conditional formatting manager distinguishes a true automatic rule from ordinary formatting.
Worked case 12 — The highlighted source is inaccurate
A mistyped value 300 instead of 3 fails a below-five test, but this does not prove the inventory is healthy. Conditional formatting can evaluate wrong data correctly and still support an unsuitable decision.
Twelve additional interpretation cases
Worked case 13 — An exact status is not a substring
A rule identifying the exact text ‘Pending’ need not match ‘Pending review’. A rule checking whether text contains ‘Pending’ could match both. The correct choice depends on what the fictional workflow means by its status labels.
Worked case 14 — Case differs across imported records
A column has ‘Approved’, ‘approved’ and ‘APPROVED’. If the workflow requires one canonical label, inconsistent casing should be normalised or checked at data entry rather than assuming an unspecified formatting condition will solve the underlying problem.
Worked case 15 — A source contains an unexpected space
A status cell reads ‘ Pending’ with an initial space. A literal exact-match test against ‘Pending’ may behave differently from a rule designed to search for contained text. The first step is to inspect the source, not to change the colour blindly.
Worked case 16 — A total row is included accidentally
The formatting range covers C2:C12, but row 12 contains a summary rather than another product. A condition applied to that total could make the dashboard appear to contain a suspicious extra item. Select the actual data region.
Worked case 17 — Two reference types behave differently
The formula $C2<5 keeps the quantity column fixed as the applied cell changes across a row. C$2 instead fixes the source row. Those are different instructions, even though both contain one dollar sign.
Worked case 18 — A rule compares against the wrong threshold
The correct limit is in F1, but the rule refers to F2. This can silently change the alert set after another value is entered. A fixed reference is valuable only when it points at the intended source.
Worked case 19 — A value is negative unexpectedly
The quantity field contains -2. A strict less-than-five rule highlights it, but a negative inventory quantity may indicate invalid data rather than genuine low stock. Highlighting and validation should remain separate concerns.
Worked case 20 — The colour conflicts with accessibility
A red-green scale is difficult for some readers and may fail on monochrome printing. Strong contrast, values, headings and explanatory text can communicate the same rule without requiring the reader to distinguish colour alone.
Worked case 21 — A higher number changes the colour scale
In a gradient scale, adding a new extreme can alter colours assigned to existing values despite those values remaining unchanged. Relative colour mapping should not be interpreted as a fixed threshold guarantee.
Worked case 22 — A manual fill hides a rule change
A user manually paints a qualifying cell, then changes the source value. Depending on formatting interactions, the display may become confusing. Review both the conditional rule and ordinary cell formatting to understand what is shown.
Worked case 23 — A sorted table changes row positions
After sorting records, each row’s quantity is now attached to a different item. A rule with properly relative row logic should continue evaluating the quantities in their current rows; a rule anchored to an unrelated fixed record may not.
Worked case 24 — A perfect display hides missing information
All visible quantities satisfy the alert rules, yet one item has no entry at all. Conditional formatting cannot evaluate records absent from the table. A completeness check is separate from classifying existing records.
Connected investigations: what the student must be able to explain
A fictional library materials tracker
Imagine a school library table with columns for item code, category, number on hand and a comment. The inventory manager wants to see rows whose quantity is below five. Before creating a formatting rule, students write the expected results for four synthetic quantities: 0, 3, 5 and 9. Only zero and three meet the strict threshold, assuming zero is a valid measured quantity.
Now introduce one blank quantity cell and one value entered as text. The question changes: the spreadsheet must distinguish missing or malformed data from a valid numerical count. A good learner can describe two requirements, one for low stock and another for data quality. Neither should silently replace the other.
How a row formula follows data
If the applied range begins at A2 and extends through D11, using =$C2<5 can test the value in C2 for the first row, C3 for the next, and so on. The dollar sign fixes C as the reference column, while the row adjusts. The rule is useful because all cells of a row respond to one relevant quantity.
A student should be able to explain why =$C$2<5 would be unsuitable for this particular purpose. That absolute reference reads one cell no matter which row is being displayed. The wrong rule may still create a convincing block of colour, which is why an independent comparison of expected rows is indispensable.
Why a spreadsheet rule is not evidence of causation
Suppose a chart makes every low inventory value red. The rule communicates that a current numeric condition was met. It cannot explain why stock is low: demand, delivery delays, incomplete data, or entry errors could all contribute. That causal analysis requires other evidence beyond a formatting predicate.
This distinction matters far beyond the lab examination. Dashboards are tools for detecting patterns and prompting questions, not automatic explanations. A responsible reader examines the relevant figures and context before taking action, rather than treating a colour as a complete recommendation.
Rule priority needs a deliberate design
Imagine two conditions: low inventory below five and missing data with an empty source cell. If one rule makes missing values grey and another accidentally treats blanks as zero and makes them red, the formatting system may show a misleading result depending on priority and style settings.
A well-designed worksheet separates the cases. The student checks which rules could both be true, how the application resolves conflicting formats, and whether the output aligns with the intended message. The benefit of rule management is logical clarity, not simply adding more colour.
Test the full applied range
A correct formula tested on C2 does not guarantee that the intended rule works in C11. The bottom row may fall outside the application range, or a relative reference may shift unexpectedly. A structured check looks at first, middle and last rows, plus exact-threshold and blank cases.
The strongest evidence is not an attractive screenshot but a short test table: source value, expected match, observed match and any discrepancy. If a mismatch occurs, inspect the range, operator, references and source-cell type. This is the same backtracking habit students use when debugging Python.
A realistic lab assessment strategy
Create the rule only after the source data and intended condition are clear. Apply it to the correct range, test one known qualifying value and one known nonqualifying value, then inspect an exact boundary. Save the workbook in the required place and reopen it where permitted to verify the rule survives.
A student can practise this workflow with fictitious quantities in a small spreadsheet. The target is a reliable rule and evidence of correct behaviour, not the highest number of formatting effects. A simple, defensible workbook is better than a decorative one whose logic cannot be explained.
Frequently asked questions
Does conditional formatting change a cell’s data?
No. It controls the appearance of cells according to a rule; it does not itself repair, validate or replace the underlying values.
Does a strict below-five rule include five?
No. A less-than condition excludes the boundary value five.
Can the format update when values change?
Yes. The rule is reevaluated according to the spreadsheet environment when its relevant inputs change.
Why use $C2 in a row-based rule?
It fixes the quantity column while allowing the row number to change, when that is the intended reference pattern.
Should I fix the threshold reference?
If every row must compare with one fixed threshold cell, an appropriate absolute reference keeps that source stable.
Are blank and zero identical?
Not conceptually. A blank may mean missing information, whereas zero may be a valid measured quantity.
Why test overlapping rules?
Two conditions can match one value, and formatting priority may determine what the reader sees.
Does a red highlight prove something is wrong?
Only that the specified formatting condition matched; the significance and data accuracy still require interpretation.
How should the rule be tested?
Check the first, middle and last rows, plus values below, at and above the threshold and a blank or malformed input.
Is colour alone enough?
No. Use clear labels, readable numbers and sufficient contrast for readers who cannot distinguish the chosen colours.
Official K349 source and connected eduKateSG references
Read the official 2027 SEC G3 Computing K349 syllabus, especially 3.1.1 and 3.1.3. Related learning: Reference 311 — Spreadsheet Formulas and Lookups, Reference 366 — Spreadsheet Text and Dates, the MOE V3.0 Library and the immutable Clementi instructional standard.
Editorial review: 29 October 2026, Singapore. All inventory data and thresholds are invented teaching examples. The transferable habit is to articulate the rule, apply it consistently and check the source values before interpreting a highlight.