AccountancyCBSE Class 12 · Spreadsheet

Conditional Formatting in a Spreadsheet: Class 12 Rules, Steps and Accounting Uses

Updated 15 October 2026 · Fact-checked

Conditional formatting changes a cell's appearance (fill colour, font, borders, data bars, colour scales or icons) automatically when its value meets a rule. It is applied from Home → Conditional Formatting, using highlight-cell rules, top/bottom rules, data bars, colour scales, icon sets or a formula that returns TRUE or FALSE. It makes exceptions in accounting data stand out instantly.

Only cells meeting the rule (Days > 30) turn red automatically, and they change colour again if the values change.

Illustration drawn for QuestionBuddy.

Understand Conditional Formatting

Idea. Ordinary formatting is fixed; conditional formatting depends on the cell's value and updates automatically when the value changes. It draws attention to what matters: overdue debtors, losses, items below reorder level.

Path. Select the range → Home tab → Styles group → Conditional Formatting → choose a rule type → set the condition and format → OK.

Rule types.
1. Highlight Cells Rules: greater than, less than, between, equal to, text that contains, a date occurring, duplicate values.
2. Top/Bottom Rules: top 10 items or top 10%, bottom items, above or below average.
3. Data Bars: a bar inside each cell whose length shows the value relative to others.
4. Colour Scales: two- or three-colour shading from low to high values.
5. Icon Sets: arrows, flags or traffic lights for value bands.
6. New Rule → Use a formula: any formula returning TRUE or FALSE, for example =＄D2>30 to shade a whole debtor row if days outstanding exceed 30 (the ＄ fixes the column so every cell in the row tests D).

Managing rules. Conditional Formatting → Manage Rules shows, edits, reorders and deletes rules; Clear Rules removes them from a selection or the whole sheet. When several rules apply, the higher rule in the list takes priority.

Accounting uses. Highlight negative profits in red; flag trade receivables overdue beyond the credit period; show stock below reorder level; compare branch sales with data bars; mark duplicate invoice numbers.

Conditional Formatting: key facts

Path: 1.Home
2.Conditional Formatting
3.rule type
4.condition
5.format
Rule types: Highlight Cells; Top/Bottom; Data Bars; Colour Scales; Icon Sets; Formula
Row-level formula rule: =＄D2>30 applied to A2:F50; Column fixed, row relative.

How to answer Conditional Formatting questions

Conditional formatting questions ask for steps, the right rule type, or the formula behind a custom rule.

1Decide what should stand out (values above, below, between, top, duplicates, bands).
2Pick the matching rule type; use a formula rule if the test involves another cell or a whole row.
3Select the correct range before applying the rule.
4In formula rules, write the formula for the top-left cell and use ＄ for the column you test.
5Choose a clear format (fill colour, font) and confirm.
6Use Manage Rules to check order and edit.

Conditional Formatting in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Flagging overdue debtors

Suppose a wholesaler keeps a debtors' sheet with customer names in A2:A60, amounts in B and days outstanding in D. It allows 30 days' credit and wants every overdue customer's full row in light red so collections staff can spot them.

Which conditional formatting rule should be used and how?

Try it first, then show the solutionSolution

Select A2:F60 (the whole table).
Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
Formula: =＄D2>30; Format: light red fill; OK.

Answer: A formula rule =＄D2>30 applied to A2:F60 shades each row whose days outstanding exceed 30. The ＄ keeps every cell in the row checking column D, and the colour updates by itself as days change.

Memory hook: Let the sheet raise its own red flags.

Tricks to remember Conditional Formatting

Short memory aids. Say each one aloud once, then use it on a question the same day.

HTDCI + F
Highlight, Top/Bottom, Data bars, Colour scales, Icon sets, plus Formula.
Use it when: Listing rule types.
Fix the column, free the row
For whole-row highlighting, use ＄ before the column letter only.
Use it when: Formula rules.
Home, not Data
Formatting lives on the Home tab; validation lives on the Data tab.
Use it when: Menu path questions.

Conditional Formatting: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Which feature? · 1 mark · MCQBoard level

A feature that automatically changes the colour of a cell when its value falls below a set limit is:
(a) data validation
(b) conditional formatting
(c) pivot table
(d) goal seek

Try it first, then show the solutionSolution

Formatting that depends on the cell's value is conditional formatting.

Answer: (b) conditional formatting

Example 2: Steps for a highlight rule · 3 marks · Short answerBoard level

Write the steps to show in red all net profit figures in C2:C13 that are less than zero. (3 marks)

Try it first, then show the solutionSolution

Select C2:C13.
Home → Conditional Formatting → Highlight Cells Rules → Less Than.
Enter 0, choose 'Red Text' or a custom red format, click OK.

Answer: As listed.

Example 3: Rule types for three needs · 3 marks · Short answerHarder board level

Which conditional formatting option suits each need: (i) show the top 5 salespersons; (ii) show relative size of monthly sales inside cells; (iii) mark duplicate invoice numbers? (3 marks)

Try it first, then show the solutionSolution

(i) Top/Bottom Rules → Top 10 Items, set to 5.
(ii) Data Bars.
(iii) Highlight Cells Rules → Duplicate Values.

Answer: (i) Top/Bottom rule; (ii) Data Bars; (iii) Duplicate Values.

Example 4: Stock below reorder level · 3 marks · Short answerHarder board level

Stock in hand is in column C and reorder level in column D (rows 2 to 40). Explain how to highlight items whose stock is below their own reorder level. (3 marks)

Try it first, then show the solutionSolution

The comparison is with another cell in the same row, so use a formula rule.
Select C2:C40 → Conditional Formatting → New Rule → Use a formula: =C2<D2.
Choose a fill colour; Excel adjusts C2 and D2 for each row.

Answer: Formula rule =C2<D2 on C2:C40 with a highlight format.

Example 5: Conditional formatting and data · 1 mark · Assertion-reasonHOTS / competency

Assertion (A): Conditional formatting changes the values stored in the cells it formats.
Reason (R): Conditional formatting changes only the appearance of cells that meet a rule.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.

Try it first, then show the solutionSolution

Values are untouched; only formats change. A is false, R is true.

Answer: (d) A is false, but R is true.

Where students lose marks in Conditional Formatting

The slips examiners see most often, and what to write instead.

Mistake: Wrong tab
Mixing with data validation.
Wrong working
Data → Conditional Formatting.
Right working
Home → Conditional Formatting.
Do this: Formatting is on Home.
Usually costs: 1/2 mark.
Mistake: Missing ＄ in a whole-row rule
Relative column shifts across the row.
Wrong working
=D2>30 applied to A2:F60.
Right working
=＄D2>30.
Do this: Fix the tested column.
Usually costs: 1 mark.
Mistake: Selecting the range after the rule
Skipping step one.
Wrong working
Rule applied to a single cell only.
Right working
Select the full range first.
Do this: Always select, then format.
Usually costs: 1/2 mark.
Mistake: Confusing with data validation
Both use rules.
Wrong working
'Conditional formatting stops wrong entries.'
Right working
Validation restricts entries; conditional formatting highlights values.
Do this: Restrict vs highlight.
Usually costs: 1 mark.

Exam tips

Write the menu path in arrows: Home → Conditional Formatting → ...
Name the exact rule type in case questions.
For formula rules, show the formula with ＄ where needed.
Conditional formatting appeared as 4-mark items in recent sample papers; expect steps and uses.

Practice questions on Conditional Formatting

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
Conditional formatting is available on the:
(a) Home tab
(b) Data tab
(c) Formulas tab
(d) Review tab
Hint
Styles group.
Answer
(a) Home tab
Q2 · 1 mark · MCQBoard level
Which option shows a bar inside each cell proportional to its value?
(a) Icon sets
(b) Colour scales
(c) Data bars
(d) Sparklines
Hint
Bar.
Answer
(c) Data bars
Q3 · 3 marks · Short answerHarder board level
State three uses of conditional formatting in accounting spreadsheets. (3 marks)
Hint
Exceptions.
Answer
Highlighting losses or negative balances; flagging overdue debtors beyond the credit period; marking stock below reorder level (also duplicate invoice numbers, top performing branches).
Q4 · 3 marks · Short answerHarder board level
Distinguish between data validation and conditional formatting. (3 marks)
Hint
Before vs after entry.
Answer
Data validation controls what may be entered, rejecting or warning about invalid data (Data tab). Conditional formatting accepts all data but changes the appearance of cells meeting a rule, to highlight them (Home tab). Validation prevents errors; formatting reveals patterns and exceptions.
Q5 · 3 marks · Short answerHarder board level
How would you shade sales above the average of B2:B13 in green? (3 marks)
Hint
Top/Bottom rules.
Answer
Select B2:B13 → Home → Conditional Formatting → Top/Bottom Rules → Above Average → choose Green Fill → OK.
Q6 · 1 mark · Assertion-reasonHarder board level
Assertion (A): If the value of a conditionally formatted cell changes, its format updates automatically.
Reason (R): The rule is re-evaluated whenever the cell value changes.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.
Hint
Dynamic.
Answer
(a) Both A and R are true, and R is the correct explanation of A.
Q7 · 4 marks · Short answerHOTS / competency
Suppose a teacher's marks sheet must show the whole row of any student with attendance (column E) below 75% in yellow. Write the rule and explain the role of ＄. (4 marks)
Hint
Formula rule on the whole table.
Answer
Select A2:G41 → Conditional Formatting → New Rule → Use a formula: =＄E2<75% → yellow fill. ＄E fixes the column so every cell in a row tests that row's attendance; the row number stays relative so each row tests its own value.
Q8 · 3 marks · Short answerHOTS / competency
Two rules apply to the same cells: red fill if value < 0 and yellow fill if value < 1000. A value of −500 shows yellow. Explain why and how to fix it. (3 marks)
Hint
Rule order.
Answer
Both rules are true for −500, and the yellow rule is higher in the list, so it takes priority. In Manage Rules, move the red (< 0) rule above the yellow rule (or tick Stop If True on it) so negative values show red.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.Name the six kinds of conditional formatting rules.Show answer
Highlight cells, top/bottom, data bars, colour scales, icon sets, formula.
2.Does conditional formatting change cell values?Show answer
No, only appearance.
3.Where do you edit or reorder rules?Show answer
Conditional Formatting → Manage Rules.
4.Which rule finds duplicate invoice numbers?Show answer
Highlight Cells Rules → Duplicate Values.
5.Formula to shade a row if column D exceeds 30?Show answer
=＄D2>30.

Conditional Formatting: frequently asked questions

What is conditional formatting in Class 12?

A spreadsheet feature that automatically formats cells meeting specified conditions, such as highlighting values below zero.

How is conditional formatting different from data validation?

Validation restricts entries; conditional formatting highlights existing values without stopping any entry.

Can conditional formatting use formulas?

Yes. 'Use a formula to determine which cells to format' applies any formula returning TRUE or FALSE.

How do I remove conditional formatting?

Home → Conditional Formatting → Clear Rules, from the selected cells or the entire sheet.