During last week, an alert reader of our blog, Jørgen emailed me a link to “How Trump happened“. It is an interactive visualization by Wall Street Journal. Jørgen asked me if we could replicate the visualization in Excel. My response: “Making a new chart in Excel? Hell yeah!”
First let’s take a look at the WSJ visualization:
You may go to WSJ’s How Trump happened page or see a quick video below. Make sure you are seeing the WSJ link on a computer or tablet. On mobiles it changes to a bar chart.
If you have trouble watching the video, click here.
As you can see, the visualization starts with one hundred voters in each group and shows how they are divided by various issues. It is a very interesting piece of story telling. That said, I am not a fan of it for below reasons:
- Misleading: The visualization suggests that some of the voters who said “YES” for one issue are saying “NO” to another. For example, 40% Trump voters have >$75,000 income. But when you go next issue (Do you have college degree?), you see 2 % voters moving to NO group. This suggests that out of 40% who have >$75k income, only 2% do not have college degree. But this is not true. The groups having $75k income and college degree may be completely different (or 100% overlapped).
- Time consuming: We need too much time to digest 10 issues at hand. What more, We are unable to compare issues vs. candidates because of we can’t see everything in one go.
- Poorly named: Last but not least, the visualization is wrongly titled. It doesn’t really explain how Trump happened? It never mentions what motivated the voters to side with Trump, whether Trump’s own campaign promises / manifesto align with the issues these voters worry about. For most of the issues, there are no significant differences between 3 groups of candidates. So how a voter decided to go with Trump is never explained.
Okay, so how do we create this in Excel?
We can’t. At least, I can’t make a 100% replica of the WSJ chart in Excel. So I went with next closest approximation. Here is the basic approach:
- We create a bubble chart with 3 bubbles – top, bottom and move.
- Top bubble shows how many people answered YES for a particular issue
- Bottom bubble shows how many answered NO
- Move bubble shows people moving from one group to another as we switch between issues.
- We fill the bubbles with people shapes and tile them.
- Whenever we switch to a new issue,
- We calculate the new top & bottom bubble sizes
- We figure out the move bubble size and movement direction (ie top to bottom or bottom to top?)
- In case of top to bottom movement,
- Use VBA to gradually reduce the top bubble while increasing the move bubble
- Change the move bubble’s y value from top to bottom
- Increase the bottom bubble size while reducing the move bubble size gradually
- Do the opposite in case of bottom to top movement.
- Use a slicer to capture issue selection and trigger animation VBA.
Here is a quick demo of this approach:
Watch this quick video. Click here if you can’t see it.
An alternative visualization – Trump Tower chart
Let me confess a thing. I don’t like the bubble chart approach. It feels clumsy and complex. So I wanted to try something different. How about using two ranges of cells and simply filling them up based on how many people said YES and NO. When we switch to a different issue, we simply move the filled cells from one range to another.
I call this approach, the trump tower chart.
First, take a look at it:
How is the Trump Tower constructed?
Oh, simple. We just go to the bank, take a $ 100 mn loan, go to city council and convince them to allocate acres of land, construct a big, luxurious building, sell the condos for insane prices and bag the profit.
I am kidding. Don’t rush to the bank. We can use Excel to make the chart. Here is the approach in a nut shell.
- Create two ranges of cells: top & bottom each with 100 cells
- Using conditional formatting, fill up the top range with number of people saying YES and bottom range with number of people saying NO.
- When user switches to a new issue, using VBA:
- Calculate the new Top & Bottom sizes
- Calculate the direction of movement
- If voters are going from top to bottom, for each voter moving:
- Reduce the top range size by 1
- Create moving illusion by filling up blank space between ranges
- Increase bottom range size by 1
- Do the opposite if we are going from bottom to top
- Set up a scroll bar to enable issue selection. Link the scrollbar to Animate VBA macro
Isn’t there a better way to visualize this data?
Let’s be honest. The original WSJ chart and both our interactive + animated replicas are not the ideal way to understand this data. These are complex – both to create and read. As we always say, simplicity trumps. Or as Trump says, “Let’s make charting great again”. So let me present a chart that is amazingly clear and very easy to make.
A bar chart will do:
As you can guess, a simple bar chart is enough to understand this data. Should you wish to highlight polarizing issues, you can use conditional formatting to highlight them. See below image:
Download “How Trump happened” Excel workbook:
Click here to download the how trump happened workbook. It contains all three visualizations. Please enable macros to enjoy them. Examine the code.
So which one is your favorite?
While I had a lot of fun building the bubble chart & Trump tower versions, I think the bar chart is most useful version.
What about you? Which chart do you like most? How would you visualize this data? Please share your thoughts and suggestions in the comments area.
Build your charting muscle…
Visual story telling is a very compelling medium. Learn how to build awesome charts using Excel. Check out below tutorials and examples:
27 Responses to “9 Box grid for talent mapping – HR for Excel – Template & Explanation”
Great stuff! I can understand how to add a slicer to the pivot table, but how do you implement the departmental selector on the 'Filter' formula scheme?
Just saw this on your Youtube channel, and it’s areat idea...!
An easy way to overcome the "ugliness" of pivot tables and get it to look nice (in the format of the Output sheet), would be to simply build a sheet with the nice map at the top, a pivot underneath it and a slicer next to formatted map and then reference each of the 9 cells in the formatted map to the “related” cell in the Pivot.
Keep up the good work!
/Claus
Thanks Claus. That is a great idea 🙂
Hi Chandoo,
This is great! Curious how to make additional columns operate the same as the Department column (ex. have a "manager column") that would allow you to sort a 9 box by manager, area, or team in addition to department?
Feel free to email me if needed! mfry01@minnetronixmedical.com
Happy New Year
Madison Fry
I am curious about the smae thing. I would like to populate the 9 box with other views as well by adding additional columns. IE., I would like to add location, region, etc. Thank you.
This is great, thank you!
How can i see the whole data set of all the teams in the output table. Need a formula that will pick up all the employees
Hello,
Love the template. Thank you. Question - the drop down to pick a department on the Output tab does not seem to work on the downloadable template. Am I doing something incorrectly?
Thank you!
Hi Heather... Thank you. I am using Excel 365 to make the calculations. If you are using an older version of Excel, then the drop-down filter won't work.
Hi
I was able to follow your 9 box grid and modified based my needs. However, you tutorial did not show how to you create the filter for the "Pick a department. Can you kindly share how to create that filter that updated the grid. Thank you.
I am working on this project but I am struggling with the data validation for the department. I copy the worksheets data entry and output as the managers want to see different tabs for each managers.
I updated the source reference for each tab but It does not update the grid based on the new source. The list was updated but it does not populate the grid based on the performance and potential listed.
In addition the hyperlink Update Data and View Talent Map no longer works. Can you please help me.
I keep getting this error message in the pivot table:
This formula is invalid or incomplete: 'The expression is not valid or appears to be incomplete. Please review and correct the expression.
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 14, ‘.
Hi, I used your 9-box excel template with excel 365. First off, thank you so very much. It is incredibly helpful!! My only question is that the boxes aren't big enough for all of the employees (specifically the middle which we call 'Core Employee'). Is there a way to make the boxes larger? Even though it is in excel, I am not able to increase row height (like I normally do in a speadsheet). Any ideas? Thanks again, Jody
Hi Chandoo,
Thanks for the great content. Re. 9 box grid, pls advise how do I increase the size of the box to accommodate more names?
Hi Chandoo,
I figured it out. Excel 365 has the format row height on the ribbon. Thank you
Merci Chandoo pour le modèle proposé,
j'ai une question et un souhait est il possible de développer davantage ce modèle en insérant la photo de chaque employé.
Hi Chandoo!
Great tutorial and tool, thank you! Your tutorial didn't include how to create additional filters on the "Output" tab. Could you please share how you did it?
Can this be done exactly in google sheets?
Hi Chandoo,
Thanks for the video it was really helpful. Is there any way to multi select the dropdown to display multiple or all departments rather than just one at once?
Hello Prish
I have Microsoft 365 and I am struggling to make the boxes larger/unable to increase row height; any idea how you made this work? Specifically in the Output tab where the map is?
Many thanks
Hello Jody, I have Microsoft 365 and I am struggling to make the boxes larger/unable to increase row height; any idea how you made this work? Specifically in the Output tab where the map is? Many thanks
Is there a way to change the 9 box wording descriptions, i.e. Work Horses, to our own internal langauge?
You can edit the file. The descriptions are textboxes.
Hi Chandoo, this is awesome and has worked perfectly. Due to a big organisation the 9 box grid on the output file is too small. I tried adjusting using the row/width ribbon under the format ribbon however it doesn't seem to work. Is there an easier way to adjust this?
Thanks!
When I drag the formula, it doesn't work, and the order I use with the data changes. In the beginning, the order is it is " candidates," " potential," and " performance," but when it goes to another column, it is " Potential," Performance," and "Candidates."Can you help me? Thank you very much, sending love from vietnam
Hi- I am working on the 9 grid project and I am trying to expand the box since I have over 100 names on a few of the columns. How do I do that?
Hi, Thank you this is great stuff and really useful.
As well as department as demonstrated on your clip, how can I display all candidates on the grid at once?
Many thanks in advance