Tree Diagram template
also known as Hierarchy Diagram, Decision Tree Diagram, Why-Why Diagram, How-How Diagram,
Decision Tree Template, Critical To Quality Tree, Work Breakdown Structure, or Organization Chart Excel template
What is a Tree Diagram?
A tree diagram worksheet starts with a single item that branches (like a tree) into multiple items.
Use it to systematically direct your thinking to increasing levels of specific details.
Types of Tree Diagrams
Generic Tree Diagram template
The generic Tree Diagram template (TreeDiagram.xls) can be used to create
any of the following variations.
Some variations are popular or unique enough to have their own template.
Tip: If your diagram has complex arrows to illustrate complex relationships between tree elements, then sometimes it is better to use the flowchart template instead.
Critical To Quality Tree Diagram
Variations: Critical to Satisfaction, Critical to Process, Critical to Delivery, Critical to Cost, Critical to Schedule...
To ensure that the product or process characteristics that you measure and control will actually satisfy your customers' desires.
The Critical To Quality Tree Diagram is popular enough to have its own Excel template — CTree.xls.
And the Measurement Assessment Tree (MeasuresTree.xls) can then be used
to further refine the stratification of the specific measures for each sub-category
of customers and/or delivery channels.
You might also want to use the simple flowchart template to perform
Critical To Quality Analysis, and/or Cost of Quality Analysis.
also known as Five Whys
To identify root causes of a problem.
- Clearly define the problem you're seeking to solve
- Ask “why?” this problem happens or might happen.
Write each “cause” in the branch layer right beneath or beside the primary problem.
- Treat each cause at this layer as a problem statement for the next layer.
Ask “why?” this problem happens or might happen.
Write each “cause” in the next branch layer — using arrows to show relationships.
- Keep going — until each branch terminates with root causes which can then be systematically addressed
(often company policies, systems, or need for training).
Variations: Work Breakdown Structure, Job Analysis, or Task Analysis
Similar to Why-Why Diagram, but instead keep asking "How?"
Decision Tree template
Logic Diagram or Decision Tree Diagram template
A sequenced set of alternate paths that lead to a correct decision for what to do in different circumstances.
Often a flowchart is a better tool for creating a decision tree.
To identify potential problems and countermeasures in a complex project plan.
- Make a high-level tree diagram of your proposed plan of action.
Top cell is the project objective, next tier is main phases,
third tier has the most important tasks to accomplish the phases.
- For each third-level task — brainstorm what could go wrong.
Eliminate problems that are not likely to happen, or whose consequences are minor.
Create a fourth tier for the remaining problems.
Some tasks might not have significant problems.
Perhaps use a different type of shape for the Problems tier.
- Brainstorm possible countermeasures for each potential problem.
Create a fifth tier for countermeasures.
Perhaps use a different type of shape for the Countermeasures tier. (perhaps a cloud shape?)
- Determine the practicality of each countermeasure.
Use criteria such as cost, time, effectiveness, ease...
In the text box for each countermeasure — the first character is either “X” for impractical, or “O” for practical.
or Gozinto chart
To illustrate the sub-assemblies, parts, and materials used to manufacture a finished good item.
To refine desired product characteristics — when doing product design and development.
Tip: Also see the Characteristic Matrix that is often used when creating a PFMEA.
To illustrate the demographic breakdown of a survey population.
Organization Chart template
also known as Organizational Chart template or Hierarchy Diagram
To define organizational relationships — both formal and informal.
The OrgChart.xls template comes with the Kaizen bundle of templates.
Most people think of an Organization Chart as being different from a Tree Diagram,
but they both use the same underlying Excel Hierarchy Diagram, and the instructions for how to use both types of charts are identical.
Bookmark = how
How to use any
Tree Diagram Worksheet
that features an Excel hierarchy diagram
see the various types of Tree Diagrams (above)
Make sure your sound is on
Note: These instructions apply to any Systems2win template that
uses an Excel hierarchy diagram, including:
- the classic Organization Chart template
- the Six Sigma Critical to Quality Tree Diagram template
- the Six Sigma Measures Assessment Tree Diagram template
(that is used to further stratify data beyond the critical-to-quality tree)
Big differences in Excel versions:
Tree diagram templates work very differently
in Excel 2007 and higher vs. Excel 2003 or lower.
Be sure to follow the instructions for YOUR version of Excel.
Tip: If you're going to work much with hierarchy diagrams,
it's worth it to upgrade to Excel 2010 or higher.
Tip: Rather than sharing Excel files between users of different versions
of Excel — it is usually wiser to share in PDF format.
Getting Started with a Tree Diagram template
or any template that features an Excel Hierarchy Chart
Excel 2010 and higher — convert the diagram to SmartArt graphic
Double click anywhere on the diagram,
and select the radio button to Convert to SmartArt Graphic.
Tip: If you did ANYTHING to the chart before converting it to SmartArt Graphic —
you will need to delete it, and insert a new one. Insert > SmartArt > Hierarchy
Save your document in .xlsm format
Excel 2007 — insert a SmartArt chart
Insert > SmartArt > Hierarchy
Excel 2007 is not able to convert a 2003 org chart — so it simply deletes it.
So you simply insert a new fresh one.
Excel 2003 and lower — open the Organization Chart toolbar
Click anywhere on the diagram drawing space. The Organization Chart Toolbar should pop up.
If this toolbar ever gets closed (perhaps by mistake), you can bring it back...
right-click anywhere on the drawing space, and select Show Organization Chart Toolbar.
Bookmark = ResizeDrawingSpace
To change the size of your work space
You might need to do any or all of the following:
- Insert or Unhide the number of underlying rows or columns
First look to see if there are any hidden row or columns that can be unhidden.
If not, then select entire rows or columns where you want to insert,
then right-click > Insert
(Excel 2003: Insert > Rows or Columns.)
- Change the Print Area
If you simply Hide & Unhide preformatted rows & columns —
then you won't need to mess with the Print Area.
In the rare event that you need to change the Print Area — see training for how to change the Print Area.
- Resize the drawing space
To resize the drawing space
Excel 2007 and higher...
Simply drag the corners of the drawing space
Excel 2003 and lower...
Caution: Resizing the drawing space in Excel 2003 is the most unintuitive and bug-susceptible aspect of working with organization charts. We highly recommend that you:
a) play around with a test worksheet, and...
b) back up your live worksheet frequently
There are 3 ways to resize the drawing space using Excel 2003: (and you need to know how to use all 3)
- Organization Chart Toolbar > Layout > Fit Organization Chart to Contents
You will want to Fit Organization Chart to Contents fairly frequently as you create your chart. This is the only way we have found to eliminate the annoying space that keeps growing between the shapes on your chart and the edge of the drawing space.
Tip: You will lose most or all of your formatting whenever you Fit the Organization Chart to Contents. So:
Apply your final formatting as the very last thing that you do — after size and placement are finalized.
In order to be able to read your chart in the mean time, however, you may need to:
- use the other resizing techniques described next
- perhaps change the font size for the entire chart
(right-click any white space on the chart, then select Format Organization Chart > Font tab).
- Organization Chart Toolbar > Layout > Expand Organization Chart
This will increase the size of your entire chart by a small amount each time that you do it.
Tip: Notice that the margin between the shapes on your chart and the edge of the drawing space keep growing even faster than the chart expands. To get rid of this margin, run Organization Chart Toolbar > Layout > Fit Organization Chart to Contents.
- Organization Chart Toolbar > Layout > Scale Organization Chart
This is your power tool for resizing the drawing space.
Tip: To resize the drawing space — don't just drag the edges of the drawing space, or you will be in for some rude surprises. In the Organization Chart Toolbar, select Layout > Scale Organization Chart.
The edges of the drawing space can NOW be dragged.
to edit any Excel organization chart template or tree diagram template
- Insert and edit primary shapes (without worrying too much about colors, fonts, or other formatting)
- Apply formatting (as the last thing you do)
To edit text in shapes
Excel 2003 or lower:
Simply type text in a box
Excel 2007 and higher:
You can simply type text in a box,
but it is usually wiser to type your text next to the bullets
in the Type Your Text Here window
that appears to the side of the diagram whenever you click anywhere on the diagram.
If you foolishly choose to collapse the Type Your Text Here window,
you can toggle the arrow in the middle of the left border of the diagram to bring it back.
The text that you type next to the bullets will also appear in the textboxes.
Notice that in the Measures Assessment Tree Diagram template —
Bug: If there is not already any text in one of these top-row textboxes,
To add a shape or branch
Right-click a node > Add Shape >
(Excel 2003 and lower: Select a nearby node. Choose Insert Shape from the toolbar, then choose the type of node:
Subordinate, Coworker, or Assistant.)
To move or rearrange boxes
In the Type Your Text Here window... right-click > Promote or Demote
Tip: It is a really bad idea to just drag shapes around.
Excel 2010 and higher also has the option to Move Up or Down
which actually means “Move Left” or “Move Right” if that tier is organized horizontally rather than vertically.
Tip: Before using Demote, use Move Up or Down to position the node to the right of the node that you want to Demote beneath.
(Excel 2003 or lower: Systems2win preferred option: Quickly insert a new box, then copy the text from and delete the old box.)
Caution: It is difficult to move or rearrange the sequence of “co-worker” boxes — so think ahead to arrange
your upper level co-workers in your desired sequence.
Caution: If you are sure that you are completely done, and will never again run AutoLayout — then you can toggle
AutoLayout off, which will allow you to move and rearrange boxes.
(But if you ever toggle AutoLayout back on, all of your moving and rearranging will be lost.)
To resize a box
Resize the entire chart, and the box sizes will adjust automatically.
Or right-click any individual shape(s) > Size and Properties
(Excel 2003: Organization Chart Toolbar > Layout > AutoLayout.)
AutoLayout is a toggle. When you turn it off, you can resize boxes.
Caution: When you toggle AutoLayout back on, any new boxes that you insert will be the same size as the last box that you changed (at the same level). Systems2win recommends waiting to perform all final formatting functions as the very last thing
you do — after all shapes have been added and positioned, and the drawing space has been properly sized.
e.g. standard org chart format, left-hanging tabs, right-hanging tabs, etc.
With the diagram selected, select the SmartArt Tools Design tab
(that appears in your Ribbon menu only when a diagram is selected)
then select your preferred type of Layout, which should almost always be Organization Chart.
Using the Layout dropdown list in the Create Graphic section of the Design toolbar... (see illustration)... each branch can have its own layout.
For example, one branch might be left-hanging, and another right-hanging.
(Excel 2003 or lower: Organization Chart Toolbar > Layout,
then choose a layout option.)
Tip: If your tree diagram has complex arrows to illustrate
complex relationships between tree elements,
then sometimes it is better to use the flowchart template instead.
To format colors, fonts, and shapes
Tip: It is wise to wait until your organization chart is fully laid out before applying formatting to individual shapes
This is especially important in Excel 2003 because when you resize your drawing space, it is not uncommon for your formatting to be overruled.
It is still a good idea in Excel 2007 and higher, because there are also other bugs that cause formatting to be lost.
With the diagram selected, select the SmartArt Tools Design tab and/or Format tab
(that appears in your Ribbon menu only when a diagram is selected)
then have fun trying out all kinds of different styles and formats.
You can also right-click any individual shape(s) > Format Shape
Excel 2003 Option 1: Use the Format Painter ( on the Standard Toolbar) to apply a style that is already on your template
Tip: If you want a consistent “corporate style” for an organization chart template...
then personalize your template with:
- Your desired AutoShape formats (boxes and lines are called AutoShapes)
- A personalized instruction text box on the Sample worksheet
And whenever you customize any template, be sure to make an entry into your Customization Log to make upgrades easy.
Tip: If you somehow lose some desired formatting on the worksheet you are working on, simply open another blank copy of your personalized template, then use the Format Painter to apply the desired format.
Excel 2003 Option 2: Define your own desired formatting
Select the shapes that you want to format.
(To choose multiple shapes, either hold down the Control button as you left-click, or use
Organization Chart Toolbar > Select)
To call up the Format AutoShape window, hover your mouse around the outside edge of any one of your selected shapes until your mouse cursor turns into a four-sided cross arrow. Then right-click and select Format AutoShape.
The Format AutoShape window will pop up with options for how to format your selected shapes.
Tip: If only the font tab is available for formatting, then you were hovering over the middle of the shape, rather than the edge of the shape when you right-clicked.
Excel 2003 Option 3: Select a pre-defined style (NOT recommended)
Caution: Many formatting options are restricted if you start with any pre-defined style other than Default. For this reason, Systems2win recommends always using the Default style, and then applying your own desired formatting choices to the various AutoShapes.
Caution: When you apply a style to any element on your chart, it will change the format of every element.
To apply a style...
In the toolbar, select the AutoFormat icon that looks like this.
Choose a style that you like, then select Apply. (Systems2win recommends using ONLY the Default style.)
To format connecting lines
Right-click any individual line(s) > Size and Properties
(Excel 2003 and lower: In the toolbar, choose Select > All Connecting Lines. Then right-click on any connecting line, and select Format AutoShape. The Format AutoShape window will pop up with options for how to format your connecting lines.)
Tip: If your tree diagram has complex arrows to illustrate complex relationships between tree elements, then...
- you can add extra lines and arrows as the very last thing you do — right before saving and/or printing
- or use the flowchart template instead
Text boxes, pictures, and AutoShapes
within the drawing space
In Excel 2003 and lower...
Text boxes, pictures, and AutoShapes within the drawing space can cause your worksheet to become unstable.
Tip: Keep these elements off to the side of your drawing space until you are ready to do your final formatting.
If you identify any other tips that you feel would be helpful to include in this documentation,
please use our Feedback Form
I have a question that’s not answered here
Try these other resources:
- Tips for Microsoft Office Drawings
- Tips for working with Systems2win Excel Worksheets
- Systems2win Portal training
- Contact Systems2win
to get answers to your questions, or to share your ideas for how to make a template better.