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.

    Yours,
    Chandoo
  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

    Hui...

  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

    Messages:
    2
    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

    Joe.


    Ratings / Criteria:

    Performance
    1. Weak performer
    2. Solid performer
    3. Strong performer
    Potential
    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
    Readiness
    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

    Messages:
    9,343
    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.
    upload_2016-6-2_16-36-47.png

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

    Attached Files:

  3. Joe Pineapples

    Joe Pineapples New Member

    Messages:
    2
    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

    Messages:
    1
    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()

    Reagrds,
    Rachna
  5. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    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 Active Member

    Messages:
    612
    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

    Messages:
    9,343
    Right on! :cool:

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

    Assessment Corporate New Member

    Messages:
    1
    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

    Messages:
    9,343
    Glad you liked it. The names are shown using custom data labels.
    https://chandoo.org/wp/2010/05/05/change-data-labels-in-charts/

    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.

Share This Page