1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

9 box grid - talent chart

Discussion in 'Discuss Data Visualizations and Charting' started by Joe Pineapples, Jun 2, 2016.

  1. Joe Pineapples

    Joe Pineapples New Member

    Hi - I have been an avid follower for a while and am always impressed by the simplicity and elegance of the solutions here.

    I am currently struggling with an HR challenge - how to present the findings of a talent bench review for up to 150 people.

    Employees are rated on Performance, Potential and Readiness, each with three criteria. I have included the criteria below.

    Currently the team have a 9-box grid in PowerPoint (am I allowed to say that here ?) that is updated manually by placing a small circle (a blob) and the name of the individuals in each of the nine quadrants. The grid axes are Performance and Potential. Readiness is represented by colour coding the blob Red/Amber/Green. Example attached

    I have searched and whilst I can find excel templates that help with a two dimensional 9-box grid in excel to show Performance/Potential, many require manual intervention to shift people up/down/left/right within each of the nine boxes, or are fixed based on the number of people that can be shown, or are generally clunky. Unfortunately I wasn't able to come across any that show the third dimension (Readiness).

    Q: Is there a way to do this in a scatter chart (or other option) that would dynamically adjust the positioning depending on the number of individuals included in the review and also apply a colour code to represent Readiness. The colour coding is a nice to have - I could live without it.

    Many thanks in advance


    Ratings / Criteria:

    1. Weak performer
    2. Solid performer
    3. Strong performer
    1. Current role only or possible bad fit
    2. Good fit at current level, lateral move, or upward 1 level
    3. Upward mobility more than 1 level
    1. Needs greater than 12 months to develop to next move
    2. Should develop in current role for more than 12 months before next move
    3. Can take next development step within next 12 months

    Attached Files:

  2. Luke M

    Luke M Excel Ninja

    Hi Joe, and welcome to the forum! :awesome:

    Here's an idea to get you started. Basic idea is to create a XY chart, one series for each color of dots that you want. To get the spacing, I have it add a small decimal to initial value, to "offset" from true 1, 2, or 3 value.

    Still rough, but hopefully you can see the basic idea.

    Attached Files:

  3. Joe Pineapples

    Joe Pineapples New Member

    Thank you so much for the fast response. My apologies for not getting back sooner - unfortunately the day after I posted I came down with a nasty bug and have been off work ever since. First day back today.

    Allow me to catch up on a mountain of work and I will take a good long look at the sheet in particular how you have calculated the offsets.

    Thanks again
  4. Rachna Sinha

    Rachna Sinha New Member

    Hi Luke,
    Greetings Of the day!
    I am new to the forum and just saw this post under 9 Box model. I am also creating a similar grid for the company where I am working. kindly explain this part of your set formula:B5-1+ROW()*1/15,NA()

  5. GraH - Guido

    GraH - Guido Well-Known Member

    I believe Luke explained it in his first response:
    "To get the spacing, I have it add a small decimal to initial value, to "offset" from true 1, 2, or 3 value."
    So the names do not overlap on the chart.
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Chart seems very useful for many. And elegant offset tip. I could have used it a couple of years ago where I had a similar problem. Thx, Luke.
  7. Luke M

    Luke M Excel Ninja

    Right on! :cool:

    Glad you liked the post. :)
  8. Assessment Corporate

    Assessment Corporate New Member

    Can you help explain how to make the graph? I tried to duplicate the table and graph that you make with more data but I cant make the graph. How to display each name in graph?
  9. Luke M

    Luke M Excel Ninja

    Glad you liked it. The names are shown using custom data labels.

    So, once you've calculated the position using XY coordinates, I have the chart show data labels. There's add-ins to do this, or you can do it manually by single-clicking twice on a data label, then going to formula bar and putting in the correct cell reference.
    Thomas Kuriakose likes this.
  10. Angus

    Angus New Member

    Hi All.

    Thanks for sending this through. I am getting into trouble with the grid for any information below 10 records. On the chart it just shows the result i.e. 2.9333 for row 11.

    Is there an inbuilt restriction on the number of records?
  11. Amanda Williams

    Amanda Williams New Member

    Hi All

    I am struggling to adapt this to what I need as my excel skills aren't great.

    What I need is a 16 box version with 'Potential' on the horizontal axis labelled P1 to P4 and 'Performance' on the vertical access labelled A to D. I would then like the employees names to appear in the relevant box on the grid.

    I also need to go one step further and be able to filter by job title.

    Please help!

    Kind Regards
  12. vletm

    vletm Excel Ninja


Share This Page