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:
13 Responses to “Gantt Box Chart Tutorial & Template – Download and Try today”
Hi Chandoo
As one of your students I have followed your detailed example through with great success. However, Excel is acting in an unexpected way and I wonder if you could take a look?
http://cid-95d070c79aef808e.office.live.com/self.aspx/.Public/Gantt%20Box%20Chart.xlsm
On my version, I have to type 40239 (Which equates to 2 Mar 2010) to get the chart to display 31 May 2010 (which should be 40329)!!??
Have I done something wrong or is Excel acting up?
Thx
Oli
PS Your example file in 2007 displays correctly.
Hi,
I like this idea a lot, but I agree the name is a little drab.
As an American I may just be seeing things, but to me the combination of lines and bars on your chart looks like a bunch of cricket bats.
Maybe you could work that into a catchier name. 🙂
Cheers!
Here is some code I use to keep the axis synched.
It may be useful to some of your readers
It is based on a comment I saw on Daily Dose of Excel.
Function SynchGanttAxis(Cname, lower, upper)
'Sets the X min and X max for Category axis
Application.Volatile
On Error Resume Next
'
'Top Horizontal Axis
With ActiveSheet.Shapes(Cname).Chart.Axes(xlCategory, 1)
.MinimumScale = lower
.MaximumScale = upper
End With
'Bottom Horizontal Axis
With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 2)
.MinimumScale = lower
.MaximumScale = upper
End With
End Function
Function SynchVerticalAxis(Cname, lower, upper)
Application.Volatile
On Error Resume Next
' Excel 2007 only
'Right hand vertical axis
With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 1)
.MinimumScale = 0
.MaximumScale = upper
End With
End Function
@Oli.. Can you check your file again.. I see 40329...
@Dave: Even I saw things.. the bars actually looked like lollipops. How about calling this lollipop chart - now that would be yummy and goes along the tradition of naming charts after eatables (bar, pie, donut...)
@Bob: Superb stuff... thanks for sharing 🙂
Hi Chandoo
This looks really good and I think it can also be applied to show project phases / milestones.
Question: Thinking further could this be amended to display a project lifecycle (Idea through to Implementation say 7 phases) on one bar / row? Just imagine 20 projects within a programme all on one chart one bar each showing their respective lifecycle stages i.e. on one page.
Idea: As the Gantt Box Chart this is quite intensive to set up re formatting etc how about the added extra of once you have completed this to "Save as template" i.e. saves the formatting and layout of the chart as a template so you can apply to future charts. Simple to do and will save the time formatting etc again and again and again.
Therefore tip: Click on your chart demo and then click on Save As template icon (2007) - edit file name and click on save. Ready to use / apply via Templates in Change Chart Type window.
Thanks and be very interested if the lifecycle question can be resolved
Mike
How embarrassing.
I was obviously suffering from numerical dyslexia. I was one of those days.
@Mike H: You can easily make this chart to work like a generic project lifecycle plan chart. All you have to do is,
1. in a separate sheet define the steps of lifecycle and various dates in a table (with 5 columns for each of the projects you have).
2. now use a control cell to input the project name you want to show in the chart
3. based on the input, use OFFSET Formulas to get the correct data
4. Rest is same as the tutorial above
For more info on the dynamic charting visit http://chandoo.org/wp/tag/dynamic-charts/ and http://chandoo.org/wp?s=OFFSET
Your solution is really smart but in the en Excel isn't meant to do stuff like this. I, as a former PM, always thought is was frustrating that you had to do stuff like this for something simple like a Gantt chart. So I built Tom's Planner. And would like to plug it here. I think it really solves the problem you are trying to solve in the most efficient way. Check out http://www.tomsplanner.com for a free account or play around with the demo.
Hi there,
Chandoo - this is really a very nice and helpfull chart - I adopted it, so I can report a forecast or the delay of a certain task (coming from my role as an auditor for projects).
One topic I´m currently struggeling with: I do have a project lasting for lets say 12 month. For a management reporting, I want to have kind of snapshot, lets say one month back and 2 month in the future. I tried with the offset formula, but failed. Any idea?
Thx
Lopi
[...] Ein viel geliebter Klassiker ist die Erstellung von GANTT-Diagrammen mit Excel. Wir hatten das Thema wiederholt schon hier. Chandoo.org hat sich mal wieder mit einer neuen Variante hervorgetan: Das GANTT-Box-Chart. [...]
[...] [...]
Hi Chandoo - fantastic xls. One thing I can't figure out how to do is adjust the alignment of the vertical axis. I would like to left align so that I could indent to represent sub tasks. Can that be done? Or is there a better way?
I've been trying to work out if there's a way to show weekends on the graph. The closest thing I've got is to add them on a secondary axis, but then I haven't been able to keep both axis lined up together! Any ideas?
Following on from this - is it possible to show things like holidays?