Excel Dynamic Organizational Chart with Visio Add-In
Companies of virtually any size benefit from Organizational Charts; the ability to see how each employee relates hierarchically to the other employees.
There are many programs that specialize in creating dynamic, colorful, and informative org charts… but many organizations don’t need such tools for something that plays such a minor role in operations. Many users will settle on Excel (or other Office products, like PowerPoint) to create their org charts.
Excel features like SmartArt and the shapes library offer simple, built-in ways of creating org charts that are in most user’s minds ‘good enough’.
The problem with these Excel tools is that they are not dynamic. Employees get hired (or they wouldn’t be employees) and employees leave the company. In smaller organizations, this may happen infrequently. In larger organizations, this may occur daily.
Making changes to the org chart to keep the information current may end up becoming a full-time activity.
Here at XelPlus, we’re all about providing solutions to everyday problems. Let’s see how we can use Excel to create a dynamic organizational chart without SmartArt or shapes.
Gathering the Employee Information
It may go without saying, but the first thing we need to put together is a list of our employee data.
Our table has the following information:
- Department – we will focus only on the Finance department.
- Employee ID – this is the ID number assigned to the employee.
- Employee – this is the name of the employee.
- Title – this is the employee’s title.
- Manager ID – this is the Employee ID number of the employee’s supervisor. If the field is blank, as in “Crystal Doyle”, then this person is the ‘big boss’.
- Type – this is the role the employee holds at the company. This field will be very important for our finished output to function properly.
We need this list to operate in such a way where newly added employees will be automatically added to the chart. We also want to automate chart updates when employees’ titles and/or types change.
NOTE: This will not be a 100% automated process, but rather 99%. There is 1 small thing we will be responsible for, that is refreshing the data.
Adding a Small, Free Missing Piece to Excel
For this feature to function properly, we need to download and install a free Microsoft add-in called the Microsoft Visio Data Visualizer.
This add-in allows us to use more sophisticated Visio diagrams inside of Excel. One of the great things about this add-in is that you are not required to own a Visio subscription in order to use the add-in. All you need is a subscription for Excel in Office 365.
To acquire this add-in, in Excel select Insert (tab) -> Add-ins (group) -> Get Add-ins.
In the Office Add-ins dialog box, search for “visio”. This will return a link to the Microsoft Visio Data Visualizer. Click the green ADD button to the right of the add-in to install this into Excel.
NOTE: This add-in is currently in preview (February 2020), so we can expect it will receive improvements pending its official release.
Once you have installed the free Visio Add-in, you are presented with the following dialog box.
This add-in gives us the ability to create the following types of charts:
- Basic flowcharts
- Cross-functional flowcharts
- Organizational charts
When you attempt to use one of the chart types, you may be presented with a message informing you that you need to login to your Office 365 account prior to use.
Creating Our Organizational Chart
In the Data Visualizer add-in, begin by selecting the Organizational Chart category. We will use the chart type labeled HYBRID for our example.
Clicking the HYBRID button will deliver to us a template table along with a visualization of the temporary data.
This is not our data, but it gives us a basis by which we can supply our own information along with additional content if desired.
Understanding the Relationship between the Data and the Chart
Let’s examine the data as it relates to the chart.
Our first entry in the table is for “Bianca Toscano” who holds the title of “Director”. Bianca has no Manager ID since she is the leader of the entire organization. She is also classified with a Role Type of “Executive”.
Bianca is visualized in the chart as follows.
Bianca has two direct-reports, Paul and Aasa. The individuals have Bianca’s Employee ID listed as their Manager IDs.
We also see that each employee label has a specific color associated with their Role Type.
- Executive – dark blue
- Manager – green
- Staff – light blue
- Consultant – orange
- Assistant – yellow
If one of our employees were to switch role types, like “Aet Kangro” were to switch from “Staff” to “Consultant”, we could press the Refresh button to receive an updated color in our org chart.
Using Our Own Data
The most important step when using your own data is that you maintain the same structure as the add-in provided.
We can copy our staff information and paste it into the provided table.
After we paste the data into the temporary table, we click the Refresh button on the org chart and are presented with the following.
Because our table is an official Excel Data Table, when we add new records, the table will automatically expand to include the new records. We only need to refresh the org chart when our data table content changes.
Adding New Records to the Table
It’s not really a challenge to add new records to the existing table. But what if we attempt to add a new record where the Employee ID for the new employee matches an existing employee? How would the Data Visualizer handle the potential supervisor/subordinate conflict when we refresh the org chart?
A conflict like this will trigger the Data Visualizer – Data Checker feature.
The Data Checker informs us that rows 9 and 16 are using the same Employee ID numbers.
If we correct our error and refresh the org chart, we see the following addition to the org chart visualization.
Changing the Org Chart Zoom Factor
You can zoom in and out of the org chart using the zoom control located in the lower-right corner of the Data Visualizer dialog box.
SIDE NOTE: Personally, I prefer using the CTRL-“spin the wheel on the mouse” trick. That technique works in most Windows applications.
If you get too zoomed in or out and want to return to the “perfect zoom” where all the data is visible with no wasted screen space, click the Fit Diagram button to the right of the zoom controls.
Printing the Visio Data Visualization
To print the visualization, select the ellipse button (to the right of the Refresh button) and select Open in Web.
This will open the visualization in your default Web browser.
By selecting the ellipse (upper-right corner), you can choose to print the chart to a traditional printer or to a PDF file. (WHAT! No XPS format support?!?!? Oh, I crack myself up sometimes.)
Feel free to Download the Workbook HERE.
Excel NEW Dynamic Arrays Course