Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

Posted on October 11th, 2016 in Charts and Graphs , Pivot Tables & Charts , VBA Macros - 3 comments

It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.

Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.

decision-tree-trump-hillary-nytimes

My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?

But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.

So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.

Demo of interactive decision tree chart in Excel

First, take a look at our Trump vs Hillary chart.

interactive-decision-tree-visualization

How to create a decision tree visualization in Excel – Tutorial

 

1. Arrange decision and outcome data

In a table (or range) list various decision and outcome combinations. For our case of Trump vs. Hillary in 10 swing states, there will be 2^10 outcomes (1024). Arrange this data in a format like below.

raw-data-decision-tree

2. Calculate the outcome

Based on each of the decision combinations, calculate the outcome and add it as a column to your table. Alternatively, you can also type or import the outcome data (along with decision combinations)

3. Create a pivot table from your data

Since we are going to use slicers for user interaction, we need to create a pivot table from all this data.

Add all the decision variables and outcome to row labels area. Rearrange the pivot in tabular layout. Disable sub-totals and grand totals.

pivot-table-settings-decision-tree

4. Add slicers

Go to Insert > slicer and select all the decision parameters. In our case, we will pick all the 10 state names.

Once all the slicers are inserted, format them.

  • Set up slicer labels in multiple columns
  • Adjust their size
  • Apply a custom style if you prefer.
  • Keep the headers on the slicers for now. We will remove them at a later stage.

Related: Comprehensive guide to slicers – what, how, where, when and why

5. Calculate % of outcomes for each candidate

Now that we have slicers, whenever you make a selection, the pivot table will be filtered. Calculate number of outcomes favoring each candidate and use that to make a stacked bar chart.

stacked-bar-chart-decision-outcomes

6. Add bells & whistles

You can add a few bells and whistles to this pretty slicer controlled stacked bar chart even prettier.

  • Add messages that display %s (or confidence levels etc.) for each outcome.
  • Display the outcome once it is certain (a la head shot of Hillary or Trump)

Related: Display shapes & images in Excel charts

So there you go. Your interactive decision tree visualization is ready.

Oh, last but not least – resetting all slicers

This is the only place we need to open the hood of Excel and mess with internal wiring. Just add a simple macro to reset all slicers in the workbook. Then assign this macro to a text box with the text “Reset all” on it.


Sub resetSlicers()
    'Reset all slicers
    
    Dim sC As SlicerCache
    
    For Each sC In ActiveWorkbook.SlicerCaches
        sC.ClearManualFilter
    Next sC
    
End Sub

Download decision tree visualization workbook

Click here to download decision tree visualization example workbookPlay with the slicers to find outcome of 2016 US election. Copy the ideas to your model / dashboard to showcase outcomes based on user inputs.

Note: this workbook has VBA. Enable macros to enjoy the reset button.

How do you visualize decision trees

As I said earlier, making decision trees in Excel is tricky if not hard. If you have Power BI, you can use R scripts to make a decision tree. But if you are stuck with Excel, creating a dynamic tree like structure is tricky. That is why, I went with the stacked bar chart approach.

What about you? How would you visualize various scenarios and outcomes in Excel? Please share your thoughts and implementations in the comments section.

Want more? Check out these awesome Excel charts

Here are few more inspiring Excel charts for you.

 

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

3 Responses to “Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]”

  1. Diarmuid says:

    This is awesome!

    I do a table like this every year to figure out who's going to win my March Madness pool. One suggestion for another bell / whistle: you can work out and show the 'must-win' states for any configuration (e.g. based on your selections so far, Trump must win Florida to win).

    You can even take it further and say what's the state that would shift the odds most for a candidate before it becomes 'must-win' - e.g. if Trump has 39 paths, and 38 of them run through Florida, you can see that's his most important state.

    I'm in the middle of writing up a post on my own blog on a fun election visualization (that I think you'll enjoy) - will hopefully get it up today!

  2. Mohan Krishna B R says:

    Hi Chandoo, I need similar kind of data tree visualization in excel in terms of compensation & benefits since I work in payroll team. Can show mininum, median & maximum CTC for each grade & unit. Please help me out,

  3. B-art in XL says:

    Hi Chandoo, I learned a lot from your awesome site, and I hope to inspire you with this little trick to generate dynamic decision trees in Excel.
    1) Create your data with all combination of your condition fields, and at least one extra field
    2) add a pivot table to your data, and add the condition fields in the ROW labels, and add the extra field in the pivot "values" area
    3) insert a bar chart on this pivot table data ( essential to do it on the pivot table !!)
    4) Select X-axis -> Format axis -> Axis options -> Axis labels = High
    And there it is your decision tree structure
    5) Add slicers on your decision fields, play with them and see your decision tree structure change
    6) For reasons of clarity, you can add text boxes to the chart and repeat the decision fields. Position the textboxes on the right of the x-axis labels.
    Remark : I use Excel 2010 at work and this trick does work for above example of 1.024 combinations, small advice use letters "Y" and "N" instead of "Yes" and "No", because you will have to enlarge the chart already a lot to have everything visible - LOL

    PS I will send you an example workbook because a picture tells more than a thousand words

Leave a Reply