Excel Organization Chart

Here is an unusual use of column chart, as an  excel organization chart. It is pretty easy to make and look very nice. You can use this chart as a classy alternative to classic organization charts. Check the article for tutorial.

Building an excel organization chart is an easy task. All you need to do is to arrange source data in a certain way, so when you insert the chart, it will be in a certain shape.

Start with writing positions from higher to lower, with drilling down to the lowest each time. For example, if your organizational structure looks like the first picture, you should arrange your list just as the same but inside one column (second picture).

Excel Organization Chart 1

Then add a number from biggest to smallest next to each position as shown below. The trick here is to repeat numbers of a position for each column that has a value for a lower position. You can track the relation between positions and their numbers from the picture.

Excel Organization Chart 2

When finished with data arrangement, select all data and insert a clustered column chart. Your initial chart will look like the one below:

Excel Organization Chart 3

Delete chart legend (the part below the chart which reads Series1, Series2, …) and click Switch Row/Column button from chart tools, design tab.

Excel Organization Chart 4

Your chart should look like the picture below at this step:

Excel Organization Chart 5

Now double click on a data series and set Series Overlap option to 100% and Gap Width option to 0%. You can see the arranged options below:

Excel Organization Chart 6

Now your chart is supposed to start looking like the picture of the finished chart.

Excel Organization Chart 7

Double click on the vertical axis and set Maximum bound to 100. Then click anywhere on the sheet and again on the axis and delete it. Do the same for horizontal axis too. Also change the title text as “Organization Chart” while on it.

Now your chart should be ready for re-coloring.

Excel Organization Chart 8

For a good looking chart, I can advise you to pick a color for each branch and use colors from lighter to darker with same level of tones used for same level positions (Notice that I arranged colors on the data table too for easier modifications if needed).

Excel Organization Chart 9

Here is our excel organization chart, colored based on the colors on the data table.

Excel Organization Chart 10

At this point, only thing left is data labels. This can get a little boring but final result is worth it. Click on left most column first and add a label. Excel will add a default label which is pretty useless for our purpose.

Excel Organization Chart 11

Double-click on this this label and change options as shown below:

Excel Organization Chart 12

Your label should fit right into the column now:

Excel Organization Chart 13

Do this for all columns of the same level. When finished, your excel organization chart should look like this:

Excel Organization Chart 14

This part is a bit annoying, since all remaining series except CEO have multiple columns. So when you added labels for a manager or above, there will be multiple labels for each position.

Click on light green part and add labels. This is how it will look like:

Excel Organization Chart 15

Double-click on any of these labels and set options as shown below:

Excel Organization Chart 16

This will give correct look to your labels. Only you will need to delete un necessary labels and leave only one label.

Excel Organization Chart 17

Repeat this for all remaining positions and your excel organization chart will be finished.

Organisation Chart

You can also download finished chart from the link below:

Excel Organization Chart

You may also like...

1 Response

  1. Hocine Satour says:

    very nice work!
    thank you for sharing it.

Leave a Reply

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