spot_img

Organization Chart Excel

MS Excel or Microsoft Excel is one of the most powerful spreadsheet software tools and helps to organize or synthesize data using different diagramming capabilities. It has many inbuilt tools which can be beneficial in visualizing spreadsheet data, and excel also provides various charts to represent the data. Organization charts are among the most popular Excel charts that can improve a spreadsheet’s visual appearance and overall effectiveness.

Organization Chart Excel

This article discusses the brief introduction of an organization chart and the step-by-step tutorial to create an organization chart in Excel. With this article, we will learn to create the desired hierarchical organization chart for the given data.

What is an Organization Chart in Excel?

An organization chart visualizes the reporting relationships in any company, organization, or other entity. It helps represent the organization structure or hierarchy in Excel using the department’s structure or employees’ data. The organization chart makes it easier to visually define data for a large organizational structure in a familiar tree.

The first modern organization chart was created by an engineer named Daniel McCallum and an artist named George Holt Henshaw. They built it to depict the company/organization structure of the New York and Erie Railroad by each location. The term gained popularity in the early 20th century, peaked in the 1960s, and is still used today.

Apart from the obvious use as visualization of reporting structure, the organization chart is also used in many other situations. Some such essential situations include the following:

  • To plan and evaluate restructuring efforts
  • To plan cross-department projects
  • To evaluate functions or roles of employees in an organization, department, or a small division.
  • To track managers’ or employees’ workloads to distribute tasks equally

Note: The organization chart is also referred to as an organizational chart and org chart (shortened form).

How to create an organization chart in Excel?

Creating an organization chart is somehow different than creating other typical Excel charts. Unlike other popular Excel charts, the organization chart is not located under the Charts section. Instead, we need to create this chart type in Excel using pre-defined SmartArt Graphics.

Steps to Insert an Organization Chart in Excel

To use an organization chart within the Excel sheet, we must perform the following steps:

  • First, we need to select the Excel sheet in a spreadsheet in which we want to insert/ create an organization chart. Next, we need to navigate to the Insert tab and click the SmartArt option, as shown below:
    Organization Chart Excel
  • In the next window, we need to select the Hierarchy option from the left-hand side list. This will display various chart structures. After that, we need to select the first tile from the right-hand side of the window. Lastly, we must click the OK button.
    Organization Chart Excel
  • After selecting the first tile and clicking on the OK button, the basic organization chart with default formatting will be instantly inserted into an active Excel sheet. It will look like the image below:
    Organization Chart Excel
    As in the above image, Excel displays two panes: a text pane on the left side and a SmartArt graphics pane on the right side.
    Organization Chart Excel
    The left side panel includes a well-aligned bullet list data that typically works as input data for the SmartArt graphics on the right pane. Therefore, when we update text values in the left pane, it will automatically update the graphical representation of an organization chart on the right pane. However, we can also edit the text directly on the right-side pane without modifying the data in the left pane.
  • Lastly, we need to supply the data, and our organizational chart will be updated accordingly. We can save it as an Excel file or an Excel template as per our requirements.

Example: Creating an Organization Chart for given Hierarchy

It is essential to implement the above steps in an example to understand the process better. Suppose that we have given the following hierarchy to create an organization chart:

Vice President > Manager > Engineer 1, Engineer 2

Let us now create an organization chart for the given hierarchy by following the steps below:

  • As discussed in the above procedure, we need first to go to Insert >> SmartArt >> Hierarchy >> Organization Chart. This will insert the following organization chart with default formatting:
    Organization Chart Excel
  • After the default organization chart has been inserted into the sheet, we need to modify the text in the left pane. We need to click on the first [Text] and type the role according to the given hierarchy, i.e., Vice President.
    After we modify the text in the first box, Excel will reflect the corresponding text in the graphics on the right-side pane, as shown below:
    Organization Chart Excel
    This way, we insert the top-most role in our example organization chart.
  • Next, we need to insert the desired text in the second text box, the second bullet, or the sub-bullet in the left pane. In our case, we need to edit the text and enter ‘Manager’ according to the given hierarchy. This will add a role of manager under the Vice President, as shown in the following image:
    Organization Chart Excel
  • After that, we need to insert the text for engineers. Since we have two engineers under the manager, we need to create new bullets in the left pane. For this, we must select the text ‘Manager’, press Enter key, and click on the Demote option under the Design tab.
    Organization Chart Excel
    This will create a new sub-bullet under the Manager, as shown below:
    Organization Chart Excel
  • We need to edit the text under the Manager and type Engineer 1 in the left pane.
    Organization Chart Excel
    Later, we must press the Enter key after entering the text, which will insert another sub-bullet under the Manager.
    Organization Chart Excel
    Since we have two engineers according to the given hierarchy, we must enter Engineer 2 in the newly created sub-bullet.
    Organization Chart Excel
  • Since we have already drawn an organizational chart for the given hierarchy, we must delete the template’s remaining textboxes or placeholders.
    Organization Chart Excel
    For this, we need to select the undesired boxes from the right pane and press the Delete key on the keyboard. After deleting the remaining text boxes, our organization chart for the given hierarchy looks like this:
    Organization Chart Excel

After the desired organization chart is inserted, it is good to customize the chart elements to make it look more attractive. However, it is not compulsory, and it only depends on the user’s choice.

Customizing the Organization Chart in Excel

Customizing the organization chart in Excel is different than customizing other Excel charts. However, there are multiple formatting and styling options, and most of the customizations can be directly made from the Design and Format tabs.

Let us know the most common customizations used in an organization chart:

Adding images in an Organization Chart

Excel allows us to insert relevant pictures in an organization chart to make it more informative. This can help display pictures of corresponding employees while presenting the company’s structure in an organization chart.

  • First, we need to go to the Insert tab and click the SmartArt.
    Organization Chart Excel
  • Next, we need to select the Hierarchy and select the second tile named ‘Picture Organization Chart’. Lastly, we must click the OK button
    Organization Chart Excel
  • We need to click the picture icon and select the desired one from the device to insert a picture.
    In addition, we can edit texts in graphics, as we discussed earlier in this article.

Likewise, we can insert other organization charts, such as ‘Name and Title Organization Chart, Half Circle Organization Chart, Circle Organization Chart, Horizontal Organization Chart, etc.

Note: Adding pictures in an organization chart is not available in Excel 2010 and lower versions.

Adding/ deleting boxes in an Organization Chart

Depending on the given hierarchy length, we may need to insert or remove boxes from the organization chart. Excel allows us to manage the number of desired boxes or shapes accordingly.

  • First, we need to select the existing box or shape to connect a new box. Next, we need to go to the Design tab and click the Add Shape option. Based on the requirements, we can further select the option from the list.
    Organization Chart Excel
  • To delete the desired box, we need to select the specific box and press the Delete key on the keyboard.

Changing a Solid Line to a Dotted Line in an Organization Chart

To include extra creativity or improve appearance, we can change the style of lines between the two boxes by using the following steps:

  • First, we need to select the SmartArt graphics whose line we wish to change. After that, we need to press a right-click on the desired line and select the Format Shape option from the shortcut menu.
    Organization Chart Excel
  • Lastly, we must click Line or Line Style and select the ‘Dash’ option to make the selected line dotted.
    Organization Chart Excel
    We can also try out another dash styling accordingly.

Changing a hanging layout in an Organization Chart

Changing the hanging layout typically affects the position of all boxes below the selected box. However, we can use other hierarchy layouts to create an organizational chart with different layouts. Besides, the hanging layout option is only available for the organization chart layouts.

  • First, we need to select a specific box that we wish to use the hanging layout option.
  • Next, we need to go to the Design tab, click the Layout drop-down icon and select the desired option from the list.
    Organization Chart Excel

Changing colors in an Organization Chart

Excel has some pre-defined color combinations for an organization chart. After the chart has been inserted, we can change the colors accordingly using the steps below:

  • First, we need to select the SmartArt graphic that we wish to apply in different color combinations.
  • Next, we need to go to the Design tab and click the option ‘Change Colors’. It will display various color combinations, and we can select the desired one from the list.
    Organization Chart Excel

Changing colors of boxes in an Organization Chart

If the default color combinations are not good enough to suit our needs, we can also apply a specific color to each box in an organization box accordingly.

  • First, we need to select the specific box and press the right-click. Next, we need to select the Format Shape option from the list.
    Organization Chart Excel
  • In the next window, we need to select the Fill option and click the Solid Fill.
    Organization Chart Excel
  • After that, we need to select the ‘Color’ and then click the desired color.

Adding SmartArt style effects in an Organization Chart

Excel also offers some unique and attractive SmartArt style effects for an organization chart. We can select the desired styling, and it is applied automatically to an entire graphics.

  • First, we need to select the SmartArt graphics that we wish to apply style effects.
  • Next, we need to go to the Design tab and select the desired one under the SmartArt Styles. To see all the effects, we can click the ‘More’ button.
    Organization Chart Excel

Advantages of using the Organization Chart

Organization charts can be an advantage over other Excel charts in several ways. For example, new employees can easily know the structure of departments, managers can explain their functions & the actions of their employees within the company, or people can evaluate the functions of different positions before and after a department reorganization, etc. Additionally, these charts can be helpful in mapping departments and groups beyond large corporations, such as members of a PTA or a family tree.

Disadvantages of using the Organization Chart

The organization chart is not always useful in Excel, and it is not considered effective while creating budgets, tracking informal communications, mapping out the complexity of daily tasks, etc. Besides, if the company often hires temporary or contract-based employees, keeping the organization chart up to date can be difficult. It is better to create an employee list to track employees or their data in such a case.


Next TopicExcel box plot

spot_img
Previous articleMS Excel Grade Formula
Next articleTreemap in Excel