How to Draw a Decision Tree in Excel
Microsoft Office 2013 includes a dedicated program, Visio, for designing decision trees and other types of flow charts. Visio isn't included in regular Office packages, however, so the task often requires manual layout in another program, such as Word or Excel. The main advantage to using Excel is the ability to link data on the tree to existing cells, which updates the tree automatically when you alter the attached spreadsheet.
Open or create a spreadsheet containing any raw data you need to make your decision tree, such as the potential costs involved in the process you're considering.
Switch to the "Insert" tab, click "Text," "Text Box" and "Horizontal Text Box." Click and drag to draw a box and then type the first piece of your tree, such as the question. If your Excel window is large enough, the "Text Box" button appears directly in the Text section of the Insert tab.
Add additional text boxes and fill in each step of your decision-making process.
Enter a starting condition in a cell near your first text box, such as the amount of money you're starting with.
Create equations to work out the final results for each potential result on the tree. Start each equation with an equals sign, use cell locations to reference existing data and modify the data with mathematical operators. You can also insert any of Excel's functions for more advanced formulas.
Click "Illustrations" and then "Shapes" on the Insert tab to choose a connecting line to tie the text boxes together. In large Excel windows, "Shapes" appears directly in the Illustrations section of the Insert tab. Both the "Curved Arrow Connector" and "Elbow Arrow Connector" work well for decision trees.
Click on one of the dots on a text box's border and drag to another text box to insert a connector. By using the dots around the text box, the connector attaches to the text boxes perfectly. Dots appear only when you place the mouse near a text box.
Reselect the same line style on the Format tab, which appears after you create your first line, to quickly prepare another connector. Draw the line and then repeat to add the rest of the lines in the tree. You need to reselect the line style prior to drawing each connector.
Select a text box, click the "Edit Shape" button on the Format tab and choose "Change Shape" to change a box from a rectangle to another shape. Excel includes a section of flowchart shapes that work well with decision trees.
Tips & Warnings
- To update the decision tree with new data, change the figures in your original spreadsheet rather than altering the results on the tree directly. Using formulas in the results makes the results update automatically when you change the spreadsheet data.
- The easiest way to copy an Excel decision tree into other programs is by taking a screenshot. Press "Print Screen" to copy the screen to the clipboard. Paste the image into Paint or another image editing program and crop the image.
- If you need to work with the tree in a program where a screenshot won't suffice, hold "Ctrl" and click each text box and line. Choose "Group" in the Arrange section of the Format tab. Copy the entire tree by pressing "Ctrl-C" and then paste it in another program using "Ctrl-V." Afterwards, type in the numbers by hand.