How to Colour-Code a Training Matrix

Define statuses before choosing colours

Agree plain-English meanings first. Typical operational statuses are current, due within 30/60/90 days, expired, missing and not required. If two sites use green for “booked on a course” while another uses green for “certificate in hand”, colour becomes noise. Write the rules on a legend sheet and ban ad-hoc fills on the live grid. Colour is a display layer; the underlying cell should still hold a date or an explicit status value.

Spreadsheet checklist for durable colour-coding

• Date cells stored as dates, not text months
• Separate status formula column or conditional rules driven by today()
• Distinct format for not required (for example grey text) versus missing (for example amber outline)
• Legend visible on every printed or PDF export
• Protected structure so casual editors cannot rewrite rules
• No merged cells across people or requirements
• Export tested in greyscale or with colour-blind-safe palettes where clients print in black and white

Build colour from formulas, then format

Manual colouring fails the first time someone updates a date and forgets to repaint. Drive formats from logic.

1. Enter real expiry dates and explicit missing / not-required markers.
2. Add a status formula that compares each date to today and your due-soon windows.
3. Apply conditional formatting to the status or date cells based on that logic.
4. Lock the rule ranges and document who may edit them.
5. Train users to change dates and markers only—never the fill colour directly.
6. When you outgrow Excel, migrate dates and statuses; do not migrate handmade colours as data.

Operational pitfalls of pretty grids

Filtered views often hide red rows; someone saves, and the next reader thinks the workforce is clean. Teach teams to clear filters before sharing. Avoid colouring entire rows green because one card is current while others are blank. For client packs, include the legend and prefer tools that calculate status server-side so emailed snapshots match the live rules. If you still need Excel, start from a template that already separates dates from display formatting.

Colour cannot replace evidence. A green cell without an attached certificate still fails many gate checks. Use colour to prioritise chasing, not to argue compliance. When moving to software, look for the same status vocabulary so site managers are not relearning what amber means.

Template limits

Colour-coding organises attention; it does not guarantee compliance or competence. FieldClear calculates status from assigned requirements and dates you maintain. This guide is not legal advice.