Excel Conditional Formatting: Highlight Rules, Data Bars & Heat Maps

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Calculate each café's % of target and add three highlight rules to Brightwater Coffee's Q3 store report: big sellers in green, near-target stores in yellow, refit notes in orange.
• Find the line a Top 3 rule and an average rule use, then colour Brightwater's three best-selling products and every product with a below-average margin.
• Count the real customers behind Brightwater's 12 loyalty-card sign-ups, colour the repeated emails and spot the stores with only one sign-up.
• Total each café's half-year sales and average wait time, and turn both store × month grids into heat maps – one where high is good, one where high is bad.
• Total Brightwater's online-shop revenue, work out each product's share and add data bars to both columns – without letting the total spoil them.
• Test the overdue formula in a helper column, turn it into a whole-row formula rule, add every-other-row banding and total what is late.
• Resolve overlapping rules with one status formula – Red beats Amber beats Green – and colour the statuses of Brightwater's store scorecard.
• Finish Brightwater's half-year KPI table: the change from July to December, a sparkline per store, red for the stores that shrank, and a sorted bar chart of the change.