How to Build a Skills Matrix in Excel for Your Team

A skills matrix in Excel shows who on your team can do what: people in rows, skills in columns, and a rating in every cell. With a drop-down for the ratings, a color scale and two formulas (COUNTIF and AVERAGEIFS), it becomes a live picture of strengths, gaps and training needs. This guide is for team leads, HR and operations staff who want a matrix they can build in under an hour and keep up to date.

What a skills matrix is#

A skills matrix (also called a competency matrix) is a table that rates each person's level in each skill. It answers three questions quickly:

• Who can do this task? Useful for planning work and cover during holidays.
• Where are we thin? A skill only one person knows well is a risk.
• What should we train next? Low scores across a team point to training needs.

Keep it about skills for the work, not a performance review. The ratings are only as good as the way they're collected, so agree on the scale with the team first.

Step 1: Lay out the skills matrix in Excel#

Put names down column A, a grouping column such as Team in column B, and one skill per column from C onwards:

Freeze the top row and the name column so they stay visible as the matrix grows: click C2, then View > Freeze Panes > Freeze Panes. Keep one rating per cell and no merged cells. That keeps every formula below simple.

Step 2: Choose a rating scale and lock it with a drop-down#

Use a short number scale, because formulas can count and average numbers. A 0 to 4 scale works well:

Put this key on the same sheet so everyone reads the levels the same way. Then stop typos with a rule:

1. Select C2:E8.
2. Go to Data > Data Validation.
3. Under Allow, choose List and type 0,1,2,3,4 in Source.
4. Click OK.

Each cell now has a drop-down, and Excel rejects anything else, such as "3+" or "good". The guide to adding a drop-down list in Excel covers lists that come from a range of cells.

Step 3: Turn it into a heat map with conditional formatting#

A color scale shows strengths and gaps without reading a single number:

1. Select C2:E8.
2. Go to Home > Conditional Formatting > Color Scales.
3. Pick a 3-color scale, such as red–yellow–green.

Low ratings now show in red and high ratings in green. If you'd rather highlight only the gaps, use Highlight Cells Rules > Less Than and enter 2. The colors are rules, so they update the moment a rating changes. For more rule types, see conditional formatting in Excel.

Step 4: Count skill coverage with COUNTIF#

COUNTIF counts the cells that meet one condition. To count people who are strong (3 or more) in Excel:

=COUNTIF(C2:C8,">=3")

The result is 4: Amara, Chen, Eli and Goran. Put a coverage row under the matrix and fill it right with Ctrl+R (Mac: ⌘R):

The formulas are =COUNTIF(C2:C8,">=3"), =COUNTIF(C2:C8,4) and =COUNTIF(C2:C8,"<2"). The operator goes inside the quotes with the number. The COUNTIF function guide and the article on how to use COUNTIF with criteria explain more patterns.

Step 5: Compare teams with AVERAGEIFS#

AVERAGEIFS averages the numbers that meet one or more conditions. To get the Finance team's average Excel rating:

=AVERAGEIFS(C2:C8,B2:B8,"Finance")

The result is 3.5 ((3 + 4 + 3 + 4) ÷ 4). For Support it is 1.0 ((1 + 2 + 0) ÷ 3). That gap is a clear training signal. The range to average comes first in AVERAGEIFS, and you can add more pairs, such as a location column. With one condition, AVERAGEIF works too, but its argument order is different; see how AVERAGEIF works. The AVERAGEIFS function guide lists every argument.

To see each person's overall level, add a column F with =AVERAGE(C2:E2) and fill down. Amara's average is 2.0.

Spot training gaps and single points of failure#

The summary rows turn into decisions:

• Single points of failure. Only two people are strong in SQL (Chen and Goran), and only Goran is an expert. If both are away, nobody covers advanced SQL work. Flag skills where =COUNTIF(D2:D8,">=3") is below 2.
• Training needs. Three people rate below 2 in SQL. That's a bigger group for a course than any other skill here.
• Mentors. People with a 4 are natural trainers for people with 1 or 2 in the same skill.

Add a date column for when each person was last rated, and review the matrix every quarter or after training. A matrix that's never updated stops being useful fast. If you manage the training itself in Excel, the training tracker guide pairs well with this one, and Excel skills for HR jobs lists the other functions HR teams use most.

Practise it#

Every summary in a skills matrix is a conditional count or average, and the heat map is conditional formatting. Practise COUNTIF in a live spreadsheet, then practise AVERAGEIF, MAXIFS and MINIFS and practise conditional formatting rules. You type each formula and it checks it instantly. Or count the strong Excel users in the exercise on this page.