Visual Colourful Skill Matrix

**Jay_hl** · 11-12-2012, 02:56 PM

Hi Forum

I am looking for a way to produce a colourful skill matrix which shows current skill level, skill gaps, and any over qualification within a team. A bit like a competency profile!!

I would like to enter against each Person and Skill, a target value, and a current (actual value), and would like Excel display in a visually appealing way!

I have mocked up what I would like to re create (with sample data)! so hoping someone can help please!

Thanks

Visual Skill Matrix.xlsx

**ChemistB** · 11-12-2012, 05:25 PM

My solution assumes you have a lot of spare time on your hands.

In the attachment, I moved your example to the bottom and replaced it with my formulas on top.

I brought the target values up into the first column of each set of skills using an array formula
=INDEX($F$9:$F$20,MATCH($A3&B$2,$A$9:$A$20&$B$9:$B$20,0))
copied down within each skill square.
Did the same with actual skill levels in the second columns
=INDEX($J$9:$J$20,MATCH($A3&B$2,$A$9:$A$20&$B$9:$B$20,0))

Then in Skill 1 square for example,
Select the cells B3:E5, Conditional formatting> New Rule> Formula
=AND(COLUMN(A1)<=$C3, COLUMN(A1)<=$B3)
Format font and fill as light green

New rule> Formula
=AND(COLUMN(A1)>$C3, COLUMN(A1)<= $B3)
Format font and fill as orange

New Rule > Formula
=AND(COLUMN(A1)<=$C3, COLUMN(A1)>$B3)
format font and fill as light purple

Repeat for other squares adjusting formulas as necessary. for example, Skill 2, Green
=AND(COLUMN(A1)<=$H3, COLUMN(A1)<=$G3)

**Jay_hl** · 11-12-2012, 05:52 PM

Thanks for your time looking at this ChemistB.

I can see that it is quite involved and time consuming. As you can probably guess this is only a very small simple sample of the view I am looking to produce (100 people and 40 skills), so am hoping that there is a solution that it a little more scalable. If not there will be a lot of late nights producing this!

Thanks again,

Jay