Archive for category Excel

Display Hierarchical Data With Visio and Excel


This is a post on how to display any hierarchal data in Visio. It is one of those post which shows the benefit of using the Microsoft Office suite, when you know how to make them “dance to your tune”.

This shows how to “bend” the “Organization Chart Wizard” (the US spelling on the screen) to display an arbitrary set of hierarchical data. In this case I have been using it go generate a presentation of a “Taxonomy of Software”, or a “Software Portfolio Taxonomy” (if you prefer – I’m not exactly what title to give it yet). I’ll blog about the taxonomy, and some of the design decisions, I’ve built in a subsequent blog post.

The Data

The data which is “presented” by Visio, is a Excel Table in an Excel Work Book. The columns I am using are shown beside. This is the top of theExcel_Visio_data taxonomy, “Software Portfolio” is the one top element, which has a couple of children shown. You can probably go to any depth (number of levels down from the top), or width (I’ve only tried one top element, but I’ve only tried one top thus far), I’ve not found where the limits are in this approach.

One Tip For Creating the Data

The hierarchical relation “hangs together” on the basis of the strings in the “Parent” and “Name” columns. To make sure that these are going to link together correctly, use Excel’s copy and paste functions to duplicate the strings between the columns. Being a lazy typist, I always do this.

You do need to have you wits about you when you are setting up the data. Visio is doing all the hard work drawing the hierarchical tree, any mistakes (things not linking up the way you wanted) and you just go back fix the data, and run the wizard again.

The Method

I’ll show the individual steps here, so that you should be able to follow.

Step 1 – Select the Organization Chart WizardVisio_OrgChartWizard

This is under the Business Templates in the “Create New” part of Visio. This is the starting point for the process.

Step 2 – Create Organization Chart FromVisio_Wizard1

Here select the “Information that’s already stored in a file or database”.

Tip: Make sure you have saved and closed the Excel workbook which has the data you about to use. If you don’t you’ll get an error from one of the following wizard screens.

Step 3 – Select the Data Source Visio_Wizard2

This is where we start down the data from Excel path.

I have not tried any other data source, but I’m certain that those should also work.

Step 4 – Select the Excel Workbook file Visio_Wizard3

Just select the Excel Workbook which you have set up with the data to be presented in the hierarchical structure.

Step 5 – Selecting The Name and “Next Up” Fields“Visio_Wizard4

This is the point where using an Excel Table in the Workbook brings a benefit. The system knows how to get the names form the columns of the table.

Step 6 – Selecting The Field which is Displayed Visio_Wizard5

This is where we are setting the field which is “put in the boxes” which Visio is going to draw is defined.

Step 7 – Selecting Shape Data FieldsVisio_Wizard6

If you other data columns you would like to have attached into each of the object created by this process, this where you specify them.

I’ve not used this option, but it should work. If you are using people, then phone numbers and locations could be extra data which you would attach.

Step 8 – The Pagination of the OutputVisio_Wizard7

I have found that what I’ve wanted is all of the hierarchy on the one Visio page. I do some shuffling around to get it to “fit”, but that’s simple stuff to do.

Step 9 – The EndVisio_Wizard9

This is the end wizard screens. Visio will next “whir it’s cogs”, and present you with a hierarchal display of the data you have fed into it.

Step 10 – The ResultsVisio_OrgChartSnap

The opposite is a screen grab from Visio. All of the text and lines are generated by the wizard. A bit of quick shuffling and I get it all onto one page.

Applying a Theme and Effects takes the simple monochrome present into something which the “boss” or “client” will like.


I hope this saves you some time when next confronted with lots of data which you need to build a presentation from.

This approach saves a lot of work in Visio, and the sort of work which I dislike doing.  I really hate drawing boxes, adding text to them and drawing lines between them, especially when there is a smarter way to achieve the result.

, , , , , ,


%d bloggers like this: