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:
15 Responses to ““How Trump happened” in Excel [visualizations]”
Like it!
Very impressive!
I created a graph which I think helps tell the story...it's not perfect so hopefully someone can improve it:
http://postimg.org/image/ros0do7z3/
Some points:
1) Why show "NO" responses? You can infer % of "NO" responses from the % that respond "YES". Showing only "YES" reduces clutter.
2) It's important to see the 3 responses for each question as closely as possible so you can quickly compare them and see any major disparities. I used data labels in a bar chart and hid the bars. A scatter plot would work better but I couldn't get it the way I wanted.
3) The only gridline you need is the 50%. It helps to quickly see which respondents were over and under 50%. The data labels are slightly off compared to the 50% gridline...I didn't know how to center the data labels over the end of the invisible bars.
Just my perspective:
1. Of the original, I like the simple bar chart the best - but, for me, it is a little too small of a scale and the 3 candidates are separated too far to get a good quick idea of what is happening.
2. I like the conditional formatted squares, but would like to see all three candidates side by side on each issue. I think it would then be much more meaningful.
3. I like the direction of Jason Morin the best, but I would offer a bar chart as my favorite way with just the 50% gridline. You can see all three side by side in each category all at once with the scale being large enough to quickly distinguish differences.
I would show an example, but do not see how to upload the file.
My only critique of the bar chart vis is that the Yes's should be on the right, No's should be to the left. This a cultural reflection that we most associate positive values with the right and negatives with the left. Outside of that, I think it delivers information quickly and effectively, inline with data visualization research.
I like Jason's approach and Tufteian simplicity. But I don't think it does a better job than the bar charts. The change requires a legend to let us know what we're looking at. I found I kept having to look at the colors to recall exactly what was being presented. Reading the bar charts across does a much better job (for me) of comparing disparities. There's little to no storage in our short term memory required to interpret the information in the bar chart format.
Finally, consider for a moment the strong "No" value for establishment conservatives in the question, "do you consider yourself 'very conservative'?" Without the No values being presented, we would not be able to immediately interpret its extent. I think the No values are useful here.
Jordan,
I agree with you about the need to repeatedly look at the legend to see which color corresponds to which group. My thought is that rather than some arbitrary symbol for each group, you do something like a letter or abbreviation. T = Trump, SC = Social Conservative, EST = Establishment. Unfortunately that is beyond my Excel charting skills.
I like that idea!
I looked closer at Jason Morin's posting for the image and went to the posting.org website - so, here is the image of my bar chart.
http://postimg.org/image/yis3xvbud/
If the direct link does not work, you may need to copy the above URL address into your browser.
Paul,
At the end your bar chart is the most informative to me. But without the instructive discussion I would not have noticed the beauty of simplicity and the easiness to understand. So also thanks for Chandoo and Jason.
I like your bar chart and the reworks by the others. I was lost on your highlights, Chandoo. At first glance, I thought you were comparing the splits to each other and saying if Trump's split swings 20% in a direction from the others. After downloading the file I see that you are highlighting when the voters are split by more than 20%. I think visualizing differences between candidates splits would have been more valuable since several of the splits (e.g., issue 1 & 2) are nearly identical in visual. Taking trump as the focal point and 20% difference between either of the two, this would highlight Issues 3, 4, 6, 8, and 11. Clearly this is different than the highlighted 2, 4, 5, 6, 9, and 11... food for thought.
Visualisation, a science and an art … and a purpose.
Chandoo’s excellent exercise, again, showed the possibilities and limitations of using visualisation to get information across to your audience. And that, oh so often, people in decision making positions -aka ‘managers’ - confuse something being visually appealing and cater for them being click-happy; but actually, are better served with something communicating information.
Simplicity, although sometimes considered boring and pedestrian, trumps the fancy interactivity BI software often sells as analysis.
Chandoo is spot-on, useful is what triggers reasoning, or better said, seeing what you are looking for. Thank you Jordan, for pointing to that interesting idiosyncrasy that Establishment Conservatives do not consider themselves as ‘very conservative’. That is what good visualisation is about. Telling a story and making you think about it.
Chandoo,
Great work! I agree that the simpler viz is better but I love the creativity of the other two. I would love more in depth explanation of how to create that bubble plot.
Thanks again for sharing!
@ Chandoo
What I found interesting on the WSJ chart is the eyecatching effects of the moving chart. I completely agree with you on the misleading parts of the chart.
I can also agree with you on the informative part of a chart is much better given to the target users on both the chart you created and on Jasons chart.
But sometimes we have an audience, where the first step is to create interest and secondly to secure that the full message is received by the target group.
At the moment my target group needs to have an eyecatcher and after that I will send the message.
I see the possibility of creating an eyecatcher and finish with the great overview that you both have created.
Number 2 is really cool 🙂
.Kudos! God Bless You CHandoo!