How to create an organizational chart template using Excel VBA programming?
The first part of the tutorial is a step-by-step guide. With its help, you will build your hierarchical org chart template in Excel using employee data. The second part of the guide will be about a VBA solution. The org chart maker tool is quick, easy, and completely free. Speed up the process using an organizational chart add-in with only a button click.
Table of contents:
- What is an Org Chart?
- How to Build Organizational Charts in Excel
- Create an Org Chart Template using a free Add-in
What is an Org Chart? How to use it?
The organizational charts (alias org chart) help build a visual overview of the department’s structure. If you are in HR, your main task is creating and updating the org chart. Managers and business unit leaders need a regularly updated organization chart. In addition, a well-made chart helps the stakeholders to build a presentation about the company structure.
How to Build Organizational Charts in Excel
#1. Insert a SmartArt (Shape)
First, create a new blank Worksheet. Then, go to the ribbon and click the Insert tab. Select the Illustration Group and insert a SmartArt in your Excel worksheet. You can use a built-in org chart template. Choose from the built-in Smartart styles and pick the preferred layout!
#2. Add text to Shape
Now, you have selected your org chart template. Click into any shape and enter the text that you want to display. On the left side, you can see the raw list with the default name. Fill the textbox with the names or positions.
#3. Customize the organizational chart hierarchy
Let’s see the organizational chart layout! Select the shape first if you want to change the org chart structure. After that, you can use the Create Graphic Group buttons under the SmartArt Tools Design tab. But first, let us see the main controls:
- Promote: Increase the level of the selected shapes. This option is most helpful if you are using the Text Pane. (Move the member up in the org chart)
- Demote: Decrease the level of the selected shape or bullet.
- Move up: Move the selection forward but keep the current level
- Move down: Move the selected shape backward in the sequence but keep the current level
#4. Add or Remove Shapes to modify the Chart
You may need to expand your org chart. In the example, you need to insert a new employee under Finance. For instance, you need to hire an assistant. Click on the existing shape, in this case, Finance. Select the SmartArt Tools Design tab and click the ‘Add Shape’ button. Enter the name of the new position using the Text Pane. That is all!
#5. Format your Org Chart
If you have to change the design of your organizational chart, there are some options to do that. With a few clicks, you can change the SmartArt graphic. Furthermore, the size of the shapes must be adjusted manually. These options are available under the SmartArt Tools Design Tab.
If you want to modify the style (add an outline or change font style), go to the Format Tab and change the default values.
How to Create an Org Chart Template using Free Add-in
You can save time and boost productivity using the VBA-powered organizational chart builder. This video explains how the chart builder works.
Installing an Excel add-in is a simple task.
In the example, we’ve built a sample organizational structure. The figure below shows that we must supply the base data to draw the template in what order. Please look at this carefully because a properly arranged list is important.
- Place your data in a blank new Worksheet
- Select the data table
- Click on the Org Chart button!
In the picture below, we will show you a five-level structure. First, the managers are on the top level of the organizational chart. Every manager has one or more members (as can be observed in real life). Next, there are employees on the structure’s second to fifth levels; the relation’s nature can be 1:1 or 1:N.
Just a few words about relations: you don’t have to consider anything complicated. The relation 1:1 means that one employee belongs to one manager. The relation 1:N means that one manager can be responsible for more employees.
The add-in is fully compatible with Microsoft Excel and SmartArt. You can also use the built-in format shape options! Click the export button, and Word or PowerPoint will open the org chart automatically. You can change the layout and add or remove members. If you are using Word, the method is the same: Export the org chart and edit the structure in Word.
Additional resources: