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).
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.
When finished with data arrangement, select all data and insert a clustered column chart. Your initial chart will look like the one below:
Delete chart legend (the part below the chart which reads Series1, Series2, …) and click Switch Row/Column button from chart tools, design tab.
Your chart should look like the picture below at this step:
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:
Now your chart is supposed to start looking like the picture of the finished chart.
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.
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).
Here is our excel organization chart, colored based on the colors on the data table.
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.
Double-click on this this label and change options as shown below:
Your label should fit right into the column now:
Do this for all columns of the same level. When finished, your excel organization chart should look like this:
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:
Double-click on any of these labels and set options as shown below:
This will give correct look to your labels. Only you will need to delete un necessary labels and leave only one label.
Repeat this for all remaining positions and your excel organization chart will be finished.
You can also download finished chart from the link below:
very nice work!
thank you for sharing it.