Discover our new product to help you understand usage and control costs in Azure. Explore Overcast.

SHAREPOINT 10 MIN READ

How to create an organizational chart with SharePoint and Office 2013

Jasper Oosterveld
WRITTEN BY JASPER OOSTERVELD

Although SharePoint comes with the out-of-the-box organizational chart, there are scenarios where a customer would like to create their own chart based on custom data. One of our customers at Sparked created a list to store all of their worldwide SharePoint employees. This was a pretty straightforward contact list with a name, job title, region and manager as metadata. The challenge was creating an organizational chart based at this list without the help of a third party tool!  There are, of course, tools to facilitate this requirements. Here is one covered recently in a blog review worth checking out.  Unfortunately, we couldn’t acquire one so I had to look for a different solution.  After doing some research I stumbled upon a solution! A collaboration between SharePoint, Excel and Visio. Let’s take a closer look.

Jasper Oosterveld
This article is a guest post by Jasper Oosterveld. A member of the SharePoint community, co-author of two books and speaker for many SharePoint Conferences and SharePoint Saturdays. If you are looking for Jasper, you'll find him working for Sparked straight from the Netherlands!

Let’s start with SharePoint

We start with creating a custom list to store all employees. In our example we create a list with SharePoint employees. The following figure illustrates the example:

How to create an organizational chart with SharePoint and Office 2013

There is a neat little trick to automatically get the full name without manually entering it. Create a new calculated column with the following formula:

=[First name]&" "&[Last name]

Works like a charm!

Next step: Excel!

Our contact list is done; it contains all of the metadata and employees. The next step is exporting the list to Excel. Open the ribbon and click on List and select Export to Excel:

We have to clean up the spreadsheet a bit by manually removing the last two columns. The export only shows the data from the current view of the list. This offers you flexibility by creating other views with other metadata fields to create multiple Excel files.

Instead of exporting the list to Excel after every change, you can automatically update Excel. Save the file and close it. Enter a new item to the list:

How to create an organizational chart with SharePoint and Office 2013

Reopen the Excel file, open the ribbon and click on Data and Refresh All:

SharePoint Organization Chart

The new row with sales employee Gemma is available in Excel! How cool is this? In our example we only use single line of text columns. I can imagine you use a managed metadata column for job titles or another metadata fields. The export to Excel looks a bit different for managed metadata columns:

How to create an organizational chart with SharePoint and Office 2013

There is an easy solution to remove the IDs:

How to create an organizational chart with SharePoint and Office 2013

The only downside is you have to do this after every synchronization.

Build your organizational chart in Visio

Now it’s time to open Visio and create a new organization chart. The first pop-up appears:

How to create an organizational chart with SharePoint and Office 2013

Click on Next:

How to create an organizational chart with SharePoint and Office 2013

Click on Next:

SharePoint Organization Chart

Select the Excel file and preferred language and click on Next (You have to close the Excel file otherwise you cannot continue):

How to create an organizational chart with SharePoint and Office 2013

Select Full name, Manager and set the last field at <none>. Click on Next:

How to create an organizational chart with SharePoint and Office 2013

Select the fields you want to display in the chart, in our case only full name and job title. Click on Next:

How to create an organizational chart with SharePoint and Office 2013

Be sure to use the same fields you selected in the previous screen and click on Next:

How to create an organizational chart with SharePoint and Office 2013

In case you have pictures ready, you can select the folder and tell Visio which metadata field to connect it with. We are doing this afterwards, so click on Next

How to create an organizational chart with SharePoint and Office 2013

Our chart is relatively small so we can leave this with the default option. Otherwise, you can configure this yourself with the first option. Click on Finish:

How to create an organizational chart with SharePoint and Office 2013

Visio created an organization chart based at our Excel data that’s based at our SharePoint data! We can use the Org Chart tab in the ribbon to customize the look & feel:

How to create an organizational chart with SharePoint and Office 2013

Look at that! You have to admit, this is pretty awesome. We can also configure a connection between Visio and Excel to load new or modified data in Visio. Click in the ribbon on Data and Link Data to Shapes:

How to create an organizational chart with SharePoint and Office 2013

We pick the first option and click on Next:

How to create an organizational chart with SharePoint and Office 2013

Select the Excel workbook and click on Next:

How to create an organizational chart with SharePoint and Office 2013

Click on Next:

How to create an organizational chart with SharePoint and Office 2013

We only select the columns we use in our chart, click on Next:

How to create an organizational chart with SharePoint and Office 2013

We choose the second option and click on Finish. An additional screen is displayed with all of the Excel data.

How to create an organizational chart with SharePoint and Office 2013

To synchronize new or modified data, we click in the ribbon on Data and Refresh All. Before you get too excited, at least I really did, there is one major flaw I can’t seem to fix. You would expect to be able to drag the new entry to the diagram and it automatically gets the same look and feel as the current shapes. It doesn’t:

How to create an organizational chart with SharePoint and Office 2013

You have to manually adjust the shape. I searched everywhere on the Internet, but couldn’t find a solution. Although this may be dissapointing, there is a cool synchronization option between the current shapes and the SharePoint/Excel data. In case of a change within current employee information, for example the last name, you want Visio to automatically update the organizational chart. That’s possible! Click in the ribbon on Data and Automatically Link:

How to create an organizational chart with SharePoint and Office 2013

Click on Next:

How to create an organizational chart with SharePoint and Office 2013

We select the two fields we use and that’s full name and job title. Connect the data columns to the shape fields. Click on Next and Finish in the next window. All of the shapes get the callout with the data. We have to manually remove these by selecting all the shapes, right click, and click on Data and Remove Data Graphic. A change made in the SharePoint contact lists is synchronized with Excel and Visio synchronizes with Excel. Again, don’t get too excited because this isn’t perfect. A change in the hierarchy, for example a promotion or demotion, isn’t synchronized in the chart. You have to manually reload the chart and start over.

Final step: SharePoint

We want to share our beautiful organizational chart with the world, at least your internal SharePoint world, so it’s time to upload all data to SharePoint. Choose a document library for the Excel and Visio files. SharePoint comes with a web part to display Visio charts:

How to create an organizational chart with SharePoint and Office 2013

This completes the circle between SharePoint, Excel and Visio.

Conclusion

I realize this solution isn’t perfect because it can require manual adjustments after certain actions. Try to take a step back and see what you can do with all of these out-of-the-box features and it’s safe to say: That’s pretty awesome!

Hey, got another minute?

Learn more about external sharing and benefit from the full potential of your Office 365.

The Ultimate Office 365 migration checklist