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
Here are few more inspiring Excel charts for you.
- 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
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?