Strategic Talent Mapping: A Step-by-Step Guide to Crafting a Nine-Box Matrix in Excel

In the pulsating heart of every organization lies a kaleidoscope of talent, each member a unique hue contributing to the vibrant tapestry of success. Picture this: a bustling office where every individual’s potential is not just acknowledged but strategically cultivated. Enter the realm of the Nine-Box Matrix, a powerful tool that elevates talent management from a mere concept to a living, breathing reality.

I will give you a simple tutorial to create a Nine Box Matrix using — once again Ms. Excel. So, make sure your Excel is ready to roll.

Recall that in the previous article on the Nine-Box Matrix, we discussed two main things: Employee potential and Employee performance. So, the first thing we have to do is create a table for employee potential.

Rate employee potential and categorize them into 3 levels: low, mid and high potential. The easier way to analyze is to put a number for those three levels. We should to make the measurements as objective as we can by making them into quantitative data.

Then, create an employee potential matrix. It contains: employee name, function/ position, indicators or characteristics that measure employee potential. Here are some examples that show high employee potential:

1. Demonstrated Leadership Skills: An employee who consistently exhibits leadership qualities, even in their current role, may have the potential for leadership positions in the future. This includes qualities like taking initiative, motivating others, and leading by example.
2. Fast Learner: Employees who quickly grasp new concepts, adapt to changes, and apply knowledge effectively may have high potential for growth. They can easily acquire new skills and knowledge, making them valuable assets for the organization.
3. High Emotional Intelligence (EQ): Individuals with a high EQ can understand and manage their emotions and the emotions of others. They tend to excel in roles that involve interpersonal relationships, team collaboration, and leadership.

Fill each sell in the matrix with the assessment result of the potential measurement. A crucial component of succession planning and talent management is evaluating employee potential. So, make sure you have evaluated employee potential effectively.

This is optional, but it is okay if we or the company have set a minimum score or level for the standard potential of all employees. Setting a minimum level for potential for all employees can have both advantages and challenges. Ultimately, the decision to set a minimum level for potential depends on the organizational culture, the nature of the work, and the strategic goals. Balancing consistency with flexibility and recognizing the unique qualities of each employee is key to a successful potential assessment approach.

I made it in three stages, namely, current minimum level, required minimum level, and gap (current minus required). For example, the current min. level is level 2. Required min level is the number of employees that must match the current min level.

Calculate the total score by using the average of all potential scores. From the total score, group it according to the score grade. You can determine the score grade in this way:

=IF(P7>=97, "A+", IF(P7>=93, "A", IF(P7>=90, "A-", IF(P7>=87, "B+", IF(P7>=83, "B", IF(P7>=80, "B-", IF(P7>=77, "C+", IF(P7>=73, "C", IF(P7>=70, "C-", IF(P7>=67, "D+", IF(P7>=65, "D", "D-")))))))))))

P7= Cell of score total

The final result here is from the total score and score grade, which is grouped into high or 3, mid or 2, and low or 1 levels. You can determine the final result in this way:

=IF(OR(Q7="A+", Q7="A", Q7="A-", Q7="B+"), 3, IF(OR(Q7="B-", Q7="B", Q7="C+", Q7="C"), 2, IF(OR(Q7="C-", Q7="D+", Q7="D", Q7="D-"), 1, "")))

Q7 = Cell of score grade

Second thing, categorize based on employee performance and devide into 3 levels: low, mid and high performance. In this sample, I used KPIs to measure employee performance.

The one above is a simple example of KPIs for sales staff. Here is an explanation of each term:

1. Key Result: The key result is the specific outcome or achievement that is expected as a result of meeting or exceeding the target score for a particular KPI. It provides a clear indication of success or accomplishment related to the performance indicator.
2. Weight: The weight score represents the relative importance or priority assigned to a specific KPI within the overall set of performance indicators. It reflects the significance of a particular KPI in contributing to the strategic objectives of the organization.
3. Target: The target score represents the desired or expected level of performance for a specific KPI. It is the benchmark or goal that an individual, team, or the organization aims to achieve within a specified period.
4. Actual: The actual score represents the real or achieved performance level for a specific KPI during a given period. It is the measurable result obtained through performance monitoring and evaluation.
5. Max/Min: Max means the greater the actual value compared to the target, the higher the KPI score. Conversely, Min means the smaller the actual value compared to the target, the higher the KPI score.
6. Score: The KPI score.

After both data are in hand — potential level and performance level, then we can know the position of the employee in nine positions. By utilising the Vlookup function in Excel, the result can be obtained automatically.

Performance Level =VLOOKUP(D2, $J$1:$L$4, 2, FALSE)
Potential Level= =VLOOKUP(C2, $J$1:$L$4, 3, FALSE)
9 Box Rating =VLOOKUP(E2&"-"&F2, $J$6:$K$15, 2, FALSE)

D2 = Peformance Score
$J$1:$L$4 = Table 1
C2 = Potential Score
E2 = Peformance Level
F2 = Potential Level
$J$6:$K$15 = Table 2

The one above is the result in table form, but you can present the result in another form. Like the one below.

This way, your boss can see the results with a single glance. And all is done!