Excel create organization chart

This Excel tutorial explains how to create organization chart in Excel.

Different ways to create organization chart

Some companies use HR systems that have function to auto generate real time organization chart, some develop in house system to create organization chart automatically by pulling the HR database,  some companies draw the data from HR system and do it half manually using software (such as Visio), but most companies just do it all manually in Excel / Powerpoint.

In Microsoft Visio, you can import an excel file (just need employee name, manager name) to auto generate the full organization chart, Excel does not that function, you need to type the name in each box manually. There are people who build such Excel add-ins to sell for money but I would rather buy a Visio license.

Excel create organization chart

In Excel 2013, navigate to INSERT > SmartArt

Excel create organization chart 01

 

Select Hierarchy on the left, and then choose an organization chart you want. In this example, I choose Name and Title Organization Chart because I want to name the organization, also this type of organization has horizontal layout, meaning organization of the same level would be arranged horizontally.

Excel create organization chart 02

 

Now the chart is inserted with default layout.

Excel create organization chart 03

 

Right click on the chart > Show Text Pane
This is where we change the contents of the organization, and where we change the organization structure. Avoid changing the organization structure by dragging the box directly, because the adjustment will not be reset even if you change to a different layout design.

Excel create organization chart 04

 

Delete everything in Text Pane

Excel create organization chart 05

 

Type the positions in Text Pane as below. Three are three buttons you you have to know:

  • Press Enter to start a new row of the same level
  • Press Tab to change the organization to one level down
  • Press Shift+Tab to change the organization to one level up

Excel create organization chart 06

 

Continue to build a complex organization structure as you need

Excel create organization chart 07

 

You can change the format in the DESIGN and FORMAT tab

Excel create organization chart 08

 

Now lets try to change the IT Officer and IT Assistant organization to a vertical layout.
Click on the IT Manager box, navigate to DESIGN tab > Layout > Left Hanging

Excel create organization chart 09

 

The two positions under IT Manager are arranged vertically. I personally prefer the horizontal layout because it is clearer, but most people would do vertical layout because it saves the space.
You can select Standard to change the layout back to horizontal.

Excel create organization chart 10

Instead of converting the layout to vertical each by each, you can change the chart type to Organization Chart to change the whole chart to vertical layout.

 

Outbound References

https://support.office.com/en-us/article/Create-an-organization-chart-21ADA00D-82E6-4340-9033-439AC2843C37#__toc261349752

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *