Generate and update Org Charts automatically in Visio

I recently answered a question from someone who was disappointed to learn that the Org Chart Wizard in Visio didn't automatically respond to structural changes in the organizational data. 

It's true that the wizard will automatically build an org chart from data in Excel -- but that's where it stops. If you add people, remove people, or move people within the organization, all you can do with the wizard is start over and generate a new org chart.

That's all changed with the Visio Data Visualizer that is included in both Visio Plan 2 and in Excel.

Even better news for some: you don't even need a Visio license to use this feature! Excel on the desktop or Excel in a web browser will do it all.

Generating a Visio org chart from data in Excel (Visio not required!)

1. In desktop Excel: search for "organization chart" and select the "Organization Chart from Data" template 

In Excel Online: select the Add-ins dropdown on the Home tab to find "Microsoft Visio Data Visualizer"; if it's not there, use the More Add-ins button to search for it. In either case, add it to Excel.

2. Select an org chart layout:

3. Excel will create an in-app Visio diagram that looks something like this: 

NOTE: At this point, you can replace the fake data in the table by pasting yours there, even if your data contains additional columns. Just ensure that your equivalent of the Employee ID, Name, Title, and Manager ID columns align with those in the Excel-supplied table.

4. If you change the data in Excel (I added a new Consultant position), when you click Refresh in the Visio pane, the diagram is updated:

That’s all there is to it! You can continue to work in Excel whether or not you have a Visio license.

Creating an external Visio file

The Visio diagram created by Data Visualizer lives inside the Excel file. The person who asked the question that led to me writing this article actually wanted to use the resulting Visio diagram in the Visio Visual in a Power BI report, which requires an external Visio diagram. 

Although the Visio Visual is a subject for another article, I'll include the steps here for creating an external Visio diagram because you might have other reasons for wanting the file to reside outside Excel.

5. In the diagram pane in Excel, click the three dots and select Open in web

6. After the external Visio diagram is created, you'll see a dialog box like this 

Be sure to note the name of the diagram (Diagram5.vsdx in this case) and where it was stored in case you want to open is later or give it a more meaningful name.

7. Select Open file and the diagram will open in Visio for the web in your browser: 

At this point you've got an ordinary Visio diagram stored in OneDrive so it's the URL of this diagram that you can paste into the Visio Visual in Power BI.

8. Important note: The external Visio file you've created is still linked to the Excel data. If you change the data (refer to Step 4 above) and subsequently reopen the diagram in Visio or the web browser, you'll see that it's been updated there also: