How to make a Yes No decision tree in Excel

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.

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.

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.

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.

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.

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 workbook. Play 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

• Mapping spread of obesity in USA
• Earth vs. Venus cosmic dance (pictured aside)
• Mapping up & down trends in a time series
• Narrating story of change
• Network chart to map relationships between people
• More advanced charts

How do you make a yes or no response in Excel?

How do I create a decision table in Excel?

How do I create a decision flowchart in Excel?

How do you create a decision matrix in Excel?