Keeping your organization’s structure up-to-date is crucial, but manually updating charts can be a pain. In this guide, I’ll show you how to create a dynamic organizational chart in Excel that eliminates the manual work. By using the Visio Data Visualizer add-in with Excel, you can easily build a chart that updates automatically whenever your employee data changes. Let’s dive into it!
With a dynamic organizational chart in Excel, every time you add, remove, or change employee info, the chart stays synced. It’s simple, powerful, and much less time-consuming.
Here’s how you can get started with creating your own dynamic organizational chart.
Watch the full video tutorial below to follow along:
Step 1: Prepare Your Employee Data in Excel
Before diving into Excel’s functions, you need to structure your employee data. The organizational chart relies on this data, so make sure it’s properly formatted.
You’ll need to create columns in your spreadsheet for:
- Employee ID: A unique identifier for each employee.
- Name: The full name of the employee.
- Title: Their job position or role.
- Manager ID: This links your employees to their supervisors by referencing their manager’s Employee ID.
- Row type: This is optional, but it’s used to differentiate things like staff, contractors, etc.
For example, if Brian Hall is your IT Director, his Manager ID can be left blank since he doesn’t report to anyone in this department. His direct reports will have his Employee ID listed in their Manager ID cell.
Step 2: Install the Visio Data Visualizer Add-in
Next, we need to connect Visio with Excel to build an organizational chart dynamically.
- Open Excel and go to the Home tab.
- Find Add-ins, then click Get Add-ins.
- Search for Visio and add the Visio Data Visualizer add-in.
- Accept the licensing agreement.
Now, you’ll see the Visio add-in in your Excel window—ready to help you build your org chart.
Note: You don’t need a Visio license to use the Data Visualizer in Excel. However, you must have a Microsoft 365 license. Without Microsoft 365, this feature won’t work.
RELATED TOPIC: How to Create a Dynamic Annual Calendar in Excel
Step 3: Set Up the Organizational Chart Template
After getting the Visio add-in, Excel will ask you to choose a template. Let’s pick one for the organization chart:
- Click on Organization Charts in the Visio add-in.
- Select any layout. I usually go with the Hybrid layout.
- Excel will display a template with some placeholder data.
The template data lets you know how to structure your spreadsheet so that Visio can read it correctly. Since you’ve already prepared your data in the original spreadsheet with Employee ID, Title, Manager ID, and Row Type columns, you’re good to go.
Step 4: Import Your Employee Data
Here’s how you replace Visio’s sample data with your real employee data:
- Copy the relevant data sections from your original sheet, ranging from Employee ID to Row Type.
- Paste that data into the generated Visio template.
As soon as you paste it, the table will now link to the dynamic chart. You’re ready to refresh it anytime with new data.
Step 5: Refresh Your Organizational Chart
Once your employee info is linked to the chart, any updates will automatically reflect.
For instance, let’s say James Smith changes from a Business Analyst to a Software Developer. All you have to do is go to your original table and:
- Change his Title to “Software Developer.”
- Update his Manager ID if he reports to someone else now.
When you go back to the organizational chart, just hit Refresh, and the changes get updated in real-time.
Similarly, if you add a new hire or remove someone who left the company, the process is simple. Add or delete them in your Excel sheet and hit Refresh once again.
RELATED TOPIC: How to Use the Quick Analysis Tool in Excel
Handling Duplicate Employee IDs
Excel also helps you spot errors, like duplicate Employee IDs.
If you enter the same Employee ID twice, Excel will flag the issue by highlighting the duplicate row in pink and displaying a warning through the Data Checker feature. This warning gives you the chance to fix the issue before it creates inaccuracies in your chart.
You can either:
- Edit the duplicate Employee ID, or
- Remove the duplicate row entirely, if it was added by mistake.
Once corrected, the error notification will disappear automatically.
How to Re-Access the Visio Add-in
If you need to get back to the Visio add-in later, follow these steps:
- Go to the Developer tab in Excel.
- Click on Add-ins, and you’ll see Visio listed there.
If the Developer tab isn’t visible, you can make it appear by:
- Going to File > Options.
- Click Customize Ribbon.
- Check the Developer tab under Main Tabs, then click OK.
With all of this in place, you can manage your dynamic organizational chart in Excel quickly and efficiently, keeping everything organized and up-to-date without the hassle of manual changes.
RELATED TOPIC: How to Concatenate or Combine Data in Excel
Conclusion
Creating a dynamic organizational chart in Excel using the Visio Data Visualizer add-in is a total game-changer for HR professionals, team leaders, and department managers. You’ll save time, prevent errors, and keep everything in sync effortlessly.
Once set up, it’s all about tweaking job titles or adding new hires—all while knowing your chart stays updated automatically with just a refresh. No more scrambling to keep org charts accurate!
If you found this guide useful, give it a thumbs up, share it with your team, and don’t forget to check out more Excel tutorials. Also, take a look at my website for written instructions and more helpful tools.
Thanks for reading, and happy charting!
Get Started with Microsoft 365
Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.
This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.
