How to Create a Competency Matrix in Excel

Prepare definitions before formatting cells

Interview supervisors and technical leads about the tasks people must perform, not the courses they have sat. Write short task statements that an assessor can observe. Agree a rating scale with plain-English anchors—for example learning, supervised, independent, can coach others—and ban free-text colours that mean different things to different managers. Decide whether agency staff appear in the same workbook and how anonymity or access is controlled.

Spreadsheet structure checklist

• People sheet: unique ID, name, role, manager, start date
• Task dictionary: task ID, description, role applicability, review frequency
• Rating scale sheet: code, meaning, examples of evidence
• Assessment grid: person × task with rating, assessor, date, next review
• Separate training sheet or export link for certificates and expiries
• Change log: who edited ratings and why
• Protected cells for scale definitions so casual edits cannot rewrite meanings

Build and populate in order

Resist the urge to colour a giant empty grid first. Definitions first, then people, then assessments.

1. Draft and approve the task dictionary for one pilot department or trade.
2. Lock the rating scale and train assessors on the anchors.
3. Import the people list and map primary roles.
4. Schedule assessments; record ratings only after observation or evidence review.
5. Filter for overdue reviews and critical tasks rated below the deployment threshold your process sets.
6. Export a read-only snapshot for the project or audit that requested it.

Keep Excel usable as the workforce grows

Excel works for small teams and pilots. Pain appears when several editors overwrite ratings, when filters hide rows before a save, and when competency scores are accidentally emailed as training evidence. Use data validation for ratings, separate permissions where possible, and a single owner for structural changes. If you also track SMSTS, CSCS and plant cards, maintain those on a training matrix template rather than adding date columns beside every competency task—the update rhythms differ.

Plan the exit criteria from pure spreadsheets: number of workers, number of concurrent editors, need for evidence attachments, or client demand for audit trails. Many organisations keep competency assessments in Excel or HR tools while moving training expiry into purpose-built software. That hybrid is healthier than one overloaded workbook. Review the task dictionary when methods change; obsolete tasks produce false confidence just like expired cards. Schedule a quarterly calibration meeting so assessors compare examples of each rating and reduce drift between sites or contracts.

Template limits

An Excel competency template organises judgements; it does not make them correct. FieldClear focuses on training requirements and expiry rather than competency ratings. Neither spreadsheets nor software guarantee compliance or competence. This guide is not legal advice.