"Automated" Org Charts - With Visio and Excel

Ever had this situation? You have a list of people and you need to turn it into an Org Chart. Think BIM execution plan. There is a quick way to do this with a little help from Visio.

STEP#1

Open up Visio and choose for an Organization Chart.

STEP#2

In the wizard you will have 2 options use the list you had or create a new one. I would go for the first option. This way you control the data in Excel and it is good to have a list that you have prepared. 

NOTE: If you reopen you can also access this wizard through the import button under Org Chart in the Ribbon.

STEP#3

I will assume you have chosen the first option which will bring you to the next window. In this window you can select the source type. Choose for Excel.

Note that you can also pull directly from Exchange or ODBC. Neither of which I have tried.

STEP#4

Once you have selected the source type you can point to your data by using “Browse…”, Select your file and click “Next”.

STEP#5

Access will analyze your file and want you to provide some more information about how your data is structured. An important piece of this wizard is built around the “Reports to”. This is the field that determines the place in the org chart.

NOTE 1 a person can only report to one manager. I appreciate that some organizations have a matrix like structure and that reporting lines are vague. However I would encourage you to think of a project a standalone organization.

NOTE 2 a person rolls up only under his manager all the way up. This means that for me I will report to my manager but not his manager. This keeps the data clean.

STEP#6

Now that we informed on the link between the people it is time to tell Access a bit more about the information we want to display.

This section has two places to edit.

STEP#7

Now you can chose to not use pictures and just jump to step#10 directly or you can choose to add some pictures.

STEP#8

I like to add visual information so I will add the pictures. Do that by creating a folder one level below where you have stored your Visio (and Excel) file. In theory *.PNG should wold but I have only made it work with *.JPG

Note for this tutorial I ran a search on Google for logo's and used these to test the principle. As I do not own the copyright to these logo's I will not show them here.

STEP#9

Inform Visio of the name that you used for the image files. Consider this before you start setting up your data. There will always be with the first name, some will even have the same first and last name. So if you want to use images of people make sure you are using something more unique, e.g. an employee ID#.

When creating BEP's I like to have a clear overview of who is from which company, so I use company logo's. This chart will not be used by the team working on the project. They can an will use the communications table so many BEP's start with. This information is more for the people around the project.

NOTE that a denominator like Organization is smaller than a individual "name".

STEP#10

The most important part is telling Visio how to export the information. If you do nothing access will start to aggregate and output sheets based on:

-        Reports to

-        Organization

and you could easily end up with more than 10 sheets.

To counter this, use the option to tell Visio that you want to be in charge of which data gets used and how it should be presented.

STEP#11

If the this page holds any all the pages that are there and start setting up new ones.

NOTE that if you change your data source to another source because you need or have another Org Chart you want to create. 

STEP#

Click add to start the wizard. Select

-        The top level “Name at top of page”

-        The depth of the chart “Number of additional levels”

-        A name for you page “Page Name”

STEP#

Click “OK” when done.

Click “Finish” in the overview of sheets to generate your sheets.

You will see Access read and generate sheets for you. Be sure to check if the images you have defined come in. If not try to find out what is wrong by using the dedicated buttons in the ribbon.

STEP#

If you made sure your images work it is time to focus on the formatting of the chart. If you brought in large quantities of data you will see that the sheet gets clogged up. I would recommend tweaking your layout until it looks nice.

Generally speaking don’t go down more than 2 levels in horizontal mode.

I hope this helps you create your org charts in an easy way. Happy Charting