MeadInKent
| An introduction to organisation charts | Links | Books | Alphabetical Index |

Drawing Organisation Charts using an Excel VBA Macro

It can be a slow process to draw all of the boxes in a chart and fix their positions and contents. A better solution may be to use a macro to draw the chart based on a list of data.

The charts can be used for different purposes and therefore there is no fixed definition of what must appear in the columns.

  Extract from data table to be used in an organisation chart  
  • column 1 - a unique value such as a name, a staff number or a hierarchy element.
  • column 2 - a job title, a person's name or a hierarchy group name
  • column 3 - the level or row number in the chart
  • column 4 - the item on a higher level in the chart to which this item will be connected

Having created a list of details, a button can be clicked to create a nicely formatted chart. There are various options:


an organisation chart created in excel with a macro

Click to open examples: 1 ABP Transport 2 Hospital Structure

This program won't automatically fix everything for you. It is still important to sort the data into an order to prevent connector lines from crossing. The program will build a chart which has a maximum size of 8 rows by 8 columns. This is probably the maximum size which can be fitted on a standard sheet of paper.

excel 2010 Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: Organisation-Charts2.htm Page last updated: Feb2014 MeadInKent.co.uk 2014 CMIDX S7 P4B Y