16 min read

Conditional Formatting

Highlight cells greater than a value. Colour scales for quarterly spending.

Rules That Colour Cells Automatically

Conditional formatting applies colour when a cell meets a rule — without manual highlighting every quarter. Governors need to spot quarters over £15,000 at a glance. Science Q4 in E4 (19000) should stand out on NorthgateBudget.xlsx without editing each cell by hand.

Select B2:E6 (quarterly amounts only). Home \to Styles \to Conditional Formatting \to Highlight Cells Rules \to Greater Than\dots Type 15000 in the box. Pick a light red fill or green fill preset. Click OK . Expected result: Science Q4 (£19,000) and other cells above 15000 are highlighted. Rules update when values change — raise a budget in E4 and the colour appears without redoing the rule.

Select B2:E6, not A1:K6 . Department names in column A are text — a "greater than 15000" rule on A2 would not behave usefully. Conditional formatting does not change values or formulas. =SUM(B2:E2) still runs on the numbers underneath the fill colour.

To start over: Home \to Conditional Formatting \to Clear Rules \to Clear Rules from Selected Cells . If you copy B2:E6 to another sheet, rules may copy with the range — check the new location before sharing the file.

Color Scales on B2:E6 shade from low to high spend — useful for internal review. Top 10% highlights the highest quarters across departments. For governor printouts, a simple Greater Than rule on 15000 is enough. Clear busy colour scales before PDF export if the table looks too loud on paper.

Practice

Where do you start Conditional Formatting?

Highlight Over £15,000

Select B2:E6 (quarterly amounts only). Home \to Styles \to Conditional Formatting \to Highlight Cells Rules \to Greater Than\dots Type 15000 in the box. Pick a light red fill or green fill preset. Click OK . Expected result: Science Q4 (£19,000) and other cells above 15000 are highlighted. Rules update when values change — raise a budget in E4 and the colour appears without redoing the rule.

Other Rules You Might Try

Color Scales on B2:E6 shade from low to high spend — useful for internal review. Top 10% highlights the highest quarters across departments. For governor printouts, a simple Greater Than rule on 15000 is enough. Clear busy colour scales before PDF export if the table looks too loud on paper.

Practice

Where do you start Conditional Formatting?