Easy Excel: Build Probability Tree Tutorial

Microsoft Excel, a versatile tool known for its data analysis capabilities, also allows users to create probability trees—a graphical representation of possible outcomes and their respective probabilities. For those new to Excel or probability trees, this comprehensive guide will walk you through the process, from basics to advanced techniques.

This article assumes a basic understanding of Excel and probability concepts. If you're unfamiliar with the latter, we recommend brushing up on your knowledge before starting.

the inside of an office with chairs and desks in front of it that says how to make a decision tree in excel

Creating the Probability Tree Structure

Before we delve into the specifics, let's understand the basic structure of a probability tree. It consists of branches representing events and nodes representing decisions or outcomes. The tree grows from the top (root) downwards, with each event's possible outcomes branching out.

How to Draw a Decision Tree in Excel | Techwalla

Now, let's create the tree's skeleton in Excel:

Step 1: Set Up the Events

two diagrams showing different types of food and their functions in the diagram, including hamburgers

List your events in columns: the first event at the top, the second below it, and so on. For instance, if you're predicting the outcome of a coin flip followed by a dice roll, list 'Heads' and 'Tails' in the first column and '1', '2', '3', '4', '5', '6' in the second column.

Your initial Excel layout should look like this:

Events	1	2	3	4	5	6
Heads
Tails

Step 2: Insert Branches for Each Outcome

In the row below each event, insert the outcomes of that event. Continuing our example, under 'Heads', branch out '1', '2', '3', '4', '5', '6'. And under 'Tails', do the same.

Now, you should see all possible outcomes:

Events	1	2	3	4	5	6
Heads
Tails
	1	2	3	4	5	6
	1	2	3	4	5	6

Adding Probabilities to Your Tree

Now that you have your tree's structure, it's time to add probabilities to your branches. In our coin flip and dice roll example, each outcome from a fair coin flip and roll has a probability of 1/6 or 0.1667 (rounded to three decimal places).

Coin & Dice Probability: Using A Tree Diagram (video lessons, examples and solutions)

How to MAKE and USE Decision Tree Analysis in Excel

Probability - Part Two : Compound Probability

Probability Calculations From Tree Diagrams | NRICH

Tree Diagrams (A) Worksheet | Printable Maths Worksheets

Probability, Combinations, and Tree Diagrams using Ice Cream and Toppings

Let's compute these probabilities in Excel:

Step 1: Assign Probabilities

In the first blank cell after each outcome, enter the event's probability. For example, under 'Heads' and '1', enter "=1/6". Excel will automatically convert this to a decimal.

Your sheet should now look like this:

Events	1	2	3	4	5	6
Heads	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667
Tails	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667

Step 2: Verify Your Probabilities

Add a new row at the bottom, labeling it 'Total'. Then, in each column, sum up the probabilities using the SUM function. This should give you a total of 1, indicating that you've correctly assigned probabilities.

For instance, in cell A10, enter "=SUM(A2:A9)", then drag this formula across to G10. It should display '1' in each column, confirming the validity of your probabilities.

More Complex Scenarios

So far, we've dealt with independent events. However, life is full of dependent events. To handle these, you'll need to delve into conditional probability and use Excel's conditional statements, IF and VLOOKUP.

For brevity's sake, we'll leave these advanced techniques for another guide. But remember, the principles of structure and probability assignment remain the same.

Pro Tip: To make your tree more visually appealing, you can use Excel's AutoShape tools to create a tree symbol instead of using columns.

And there you have it! Your very own probability tree in Excel.