Step 1: Gather data about performance and potential of your staff

In a blank spreadsheet, gather the data about your staff. At the very least, you want staff name, performance and potential. Feel free to add things like department or team names.

This is how I structured my data:

It is all in a table named talent. 

Step 2: Create the talent map 9 box grid visual

You can do it in two ways. If you have Excel 365 and access to dynamic array functions like FILTER(), you can use formulas to generate the 3×3 talent map grid.

Else, you can use Pivot Tables to generate the talent map grid.

Formula approach for Talent Map

Note: this method requires Excel 365 with dynamic array functions.

Set up a 3×3 grid in a blank sheet. 

In the top left cell, use the below formula.

=IFERROR(TEXTJOIN(“,”&CHAR(10),TRUE, SORT(FILTER(talent[Candidate],(talent[Performance]=E$8)*(talent[Potential]=$D5)))),””)

The above formula assumes E$8 has the performance rating value and $D5 has potential.

How does this formula work?

We are using FILTER() function to filter down the talent table to all candidates who have given performance (E8) and potential (D5) values

SORT() function sorts these names in alphabetical order.

Finally TEXTJOIN() is used to combine all names to one big text with comma & new line character between names. 

• Learn more about FILTER & SORT functions.
• Learn more about TEXTJOIN function

Pivot Table approach for Talent Map

Note: this method requires Excel 2013 or above versions

Here is a quick demo of the Pivot Table based 9 box talent map grid.

To create this,

• Insert a pivot table from your talent data. During the insert pivot step, enable “add this to data model” option, as depicted below.

Read more about data model based pivot tables.

• Now add “performance” to column labels and “potential” to row labels area of the pivot.
• We need to show the names of people instead of count in the values area. This can be done by using measures. 
• Right click on the table in fields list area and select “Add measure” option.
  • Name of the measure: List of names
  • Definition: =CONCATENATEX(‘talent mapping’, [Candidate],”, “,[Candidate],asc)

Add this measure to values area of the pivot and your 9 box talent map will be ready.

What is this measure doing?

This measure is concatenating all the staff names that have given performance and potential levels. It is also sorting such names in ascending order.