“How Trump happened” in Excel [visualizations]

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:

• Is this a FIFA worldcup of late goals? Let’s ask Excel…
• A visual  tribute to Sachin Tendulkar, the legendary cricketer
• Closing the gaps in this gender equality chart
• How tax burden has changed over the years – Replicating NY Times chart in Excel