You can easily understand the structure of any firm from its organization chart.
Almost every organization has some sort of organizational chart and did you know that there is an easy way to Create Organizational Charts in Excel?
An Organizational chart is a graphical illustration that conveys the connection between positions in a firm.
This chart helps to explain:
- The various positions in an organization
- The reporting and command flow in an organization
- The relationship between the various positions in an organization
- What role needs to be filled up in an organization
- The job responsibilities of individuals etc.
Sometimes, an organizational chart can be referred to as an “org chart” or “organization charts”.
There are three commonly used types of org charts:
- Hierarchical Org Chart – This refers to an Org Chart where the chain of command flows from the top (Manager or CEO) down (low-level employees). The structure looks like a pyramid.
- Matrix Org Chart – This refers to an org chart where teams report to more than one individual. The structure looks like a grid
- Flat Org Chart – This is also called a horizontal Org chart. It refers to an org chart with only very few levels of management between the top and bottom.
SmartArt is a graphic feature used to present a visual representation of your information and ideas.
Its graphical functionality offers a wide range of layouts (like pyramids, cycles, block-list, and more).
These layouts can be used to describe processes, interactions, dependencies, a chain of events, hierarchy, relationships, and so many other phenomena.
There are around 15 hierarchy layouts offered by SmartArt, namely
- Organization chart
- Picture organization chart
- Name and Title organization chart
- Half cycle organization chart
- Cycle Picture Hierarchy
- Horizontal organization chart
- Hierarchy
- Labeled hierarchy
- Table hierarchy
- Horizontal multi-level hierarchy
- Architecture layout
- Horizontal labeled hierarchy
- Hierarchy List
- Horizontal hierarchy
- Lined list
- Hierarchy list
However, the Organization chart happens to be the most basic and widely used layout
SmartArt is present in Microsoft Office applications such as MS Word, MS Excel, MS PowerPoint, and MS outlook.
In this tutorial, I will show you how to create an Org Chart Afresh and the use of a list in Excel.
But, before we proceed, it is important to define some terminologies that may be used during the tutorial.
- Node – this refers to the boxes used in building the org chart.
Here’s How to Create an Organizational Chart in Excel:
Creating an Org Chart Afresh
The image below shows a three-layer company chart for a firm called Helix.
Our task is to replicate the image using excel.
To create this org chart simply follow the steps outlined below:
Step 1: Click on the Insert tab
Step 2: Navigate to the illustration group and click on the SmartArt button
A SmartArt graphics window pops up.
The SmartArt categories are displayed on the left plane.
To show the subdivisions of each category on the right plane, simply click on the category name.
Step3: Click on the Hierarchy category from the list on the right
Step 4: Select the Organization Chart layout icon from the displayed options on the right.
Step 5: Click OK
Step 6: A basic org chart that can be edited is created.
To label a position or enter a text into any block, simply click inside the block.
You will notice that the text cursor begins to blink inside the block.
At this point, you can proceed to type in the title.
Step 7: Click the topmost block and type in ‘CEO’
You may have noticed that the structure of the template we are using is slightly different from what we wish to build.
Let’s quickly identify some of the difference
- There is an intersect or assistant node between the first and second layers of the org chart template
- There is a third layer in our intended org chart. But this is not present in the template.
So, how can we edit the template such that it fits our intended design?
Firstly, we need to delete the assistant node, then proceed to add a third layer to the template.
To remove a block from the chart, simply
Step 8: Select the identified intersecting block
Step 9: Press the delete button on your keyboard
The template automatically gets adjusted after deleting
Step 10: Enter the titles for the three vice president nodes in the second layer
Vice President Finance
Vice President Manufacturing
Vice President Human Resources
We need to add another layer to the hierarchy (just beneath the Vice presidents). To do this we have to:
Step 11: Click on the first position under which you wish to add an assistant node (in our example, this is the Vice President Finance Node).
Step 12: Click on the SmartArt tab
Step 13: Navigate to the Create Graphic group
Step 14: Click on the drop-down arrow beside the Add Shape button
Step 15: Select the Add Assistant option
An assistant node is added beneath the selected node.
Because there are two assistant nodes under the selected node, we will repeat steps 9 to 13.
Step 16: Click on the first Assistant node and type in ‘Chief Accountant ‘
Step 17: Click on the second Assistant node and type in ‘Finance Analyst’
Great! Your chart will look like this
Now repeat the same process for the Assistant nodes under the Vice President Manufacturing and Vice President Human Resources positions respectively.
This is what our final Org Chart looks like
Creating an Org Chart in Excel using a List
Sometimes the org chart for companies may contain tens or hundreds of positions.
In such a scenario, using the first method may not be practical.
This is where the creation of a list comes to play.
It is more practical to create a list of all positions and then uses the org text pane to edit it to the requirement.
Using our initial sample from Helix, we will replicate the chart using the List method.
So, let’s get started!
Step 1: In your worksheet, manually type in a list of all positions or titles of the org chart.
Note:
- You can only type a title per cell
- Ensure the list is typed along a column
- Type in the titles hierarchically such that each title has its subordinates (that is, the title that reports to it) listed under it. In our example, we will notice that the Plant Manager and Maintenance manager both report to the Vice president of manufacturing. Hence, we added these two titles just beneath the Vice president manufacturing
Step 2: Click on the Insert tab
Step 3: Navigate to the illustration group and click on the SmartArt button
A SmartArt graphics window pops up. The SmartArt categories are displayed on the left plane. To show the subdivisions of each category on the right plane, simply click on the category name.
Step4: Click on the Hierarchy category from the list on the right
Step 5: Select the Organization Chart layout icon from the displayed options on the right.
Step 6: Click OK
Step 7: A basic org chart that can be edited is created. This org chart has a text pane attached at the left
Note: If you cannot see the Text Pane, simply:
- Click on the SmartArt Design tab
- Navigate to the Create Graphic group
- Click on Text Pane. You will notice that the hidden text pane gets published
Alternatively,
- Click on the little arrow that is placed at the left edge of the Org Chart. You will discover that the text pane is published
Step 8: Click on the text pane and select the default bullets displayed
Step 9: Delete all the selected bullets by pressing the backspace key on your keyboard
You will notice that as you delete the bullets its corresponding node gets removed from the chart and you are left with just one single bullet point in the text pane and one large node in the org chart.
Step 10: Select the listed range from your sheet. In our example, this will be C1:C10
Step 11: Right-click on the selected range, a menu box pops up
Step 12: Select Copy to copy the title list. Alternatively, you can press CTRL + C on your keyboard to copy the list
Step 13: Navigate to the text pane and click on the single bullet
Step14: Right-click (a menu box pops up)
Step 15: Click on the Paste option. Alternatively, you can press CTRL + V on your keyboard to paste the copied title list)
If you observe, you will notice that all the titles of our list have been displayed as a separate node in a single layer of the org chart
To align the nodes to our desired sample, we can employ two methods.
- Using the Promote or Demote button
- Indenting from the text pane
Method 1: Using the Promote or Demote Button
In this method, we simply select each node and promote/demote them until it reaches the level required.
To achieve this, follow the steps below
- Select the node you wish to demote (in our example, this is ‘Vice President Finance)
- Click on the SmartArt tab
- Navigate to the Create Graphic group and select the Demote button
You will observe that the Vice president node is automatically placed under the CEO
- Next, select the Chief Accountant node
- Click on the demote button twice.
You will observe that the Chief Accountant node is automatically placed under the Vice President Account node
- Next, select the Finance Analyst node
- Click on the demote button twice.
You will observe that the Chief Accountant node is automatically placed under the Vice President Account node. In addition, the text on the text pane is indented to reflect the demote/promote action.
Now repeat the same process for the Assistant nodes under the Vice President Manufacturing and Vice President Human Resources positions respectively.
This is what our final Org Chart looks like
To change the structure of the org chart such that it is the same as our sample image we will simply,
- Select all nodes in the org chart
- Click on the SmartArt tab
- Navigate to the Create Graphic group and select the Layout button
A dropdown box appears
- Select the standard option
Your org chart should look like this:
Method 2: Indenting from the text pane
As the name implies, we will be indenting the titles from the text pane. This will trigger the realignment of the respective nodes to their right position.
To achieve this, follow the steps below
- Click on the Vice President Finance
- Press the tabs key on your keyboard (this will indent the node)
Next,
- Click on the Chief Accountant
- Press the tabs key twice on your keyboard (this will indent the node to the third hierarchy)
Next,
- Click on the Finance Analyst
- Press the tabs key twice on your keyboard (this will indent the node to the third hierarchy)
Now repeat the same process for the Vice President Manufacturing and Vice President Human Resources titles as well as their respective assistant nodes.
Formatting the Org Chart in Excel
After building the org chart, we can proceed by formatting it to fit our preferences. Some of the formatting actions that can be carried out include (but are not limited to):
- Change font size, color, or styles
- Change background color of the nodes
- Change the shape of individual nodes
- Rotate the shape or change the outline color
- Carry out any other type of formatting that is possible in excel graphic
To do any of these formatting activities, simply click on the Format tabs and navigate through the groups and features.
In addition, you can easily
- Select a preset style to change the effect of the org chart. To do this, click on the SmartArt tab and navigate through the SmartArt styles.
Changing the Node Order
Changing the order of the nodes within the same hierarchy can also be carried out. To do this,
- Select the Node you wish to move in the org chart
- Click on the SmartArt tabs
- Navigate to the Create Graphic group
- Select
- Move Up to move the selection forward in the sequence
- Move down to move the selection backward in the sequence
- Right to Left to change the orientation of the level (that is, to reverse the direction)
Changing the Org Chart Layout
There are several org chart layouts present in Excel. You can readily choose your layout before you commence the org chart building from the ‘Choose a SmartArt Graphic’ window. However, if we decide to change the layout after we have concluded the building we can simply:
- Select the built org chart
- Click on the SmartArt tabs
- Navigate to the Layout group
- Select the preferred layout of your choice
You will notice that the data and hierarchy of the built org chart automatically reflect the selected layout.
Converting to Shape
The org chart can also be converted to a shape so that any node can easily be moved, resized, or deleted independently of the other nodes. To do this,
- Select the entire built org chart
- Click on the SmartArt tabs
- Navigate to the Reset group
- Click on the Convert to Shape button.
The org chart automatically gets changed to a shape.
Conclusion
In this tutorial, we have learned how to build an org chart using two methods in excel.
The SmartArt graphics feature gives us enough flexibility to tweak the org chart to our requirements.
