A Dynamic Organizational Chart in Excel is a visual diagram that represents the hierarchy and reporting structure within an organization, where roles and relationships update automatically based on changes in data or user input. By using Excel features like SmartArt, linked data, or VBA, the org chart can dynamically reflect updates in positions, departments, or reporting lines without recreating the chart manually.
Dynamic Organizational Chart
An org chart becomes dynamic when its roles or hierarchy update automatically based on data or user input. Consider this college hierarchy:
Role | Reports To |
|---|---|
Principal | None |
Head of Dept (IT) | Principal |
Professor 1 (IT) | Head of Dept (IT) |
Professor 2 (IT) | Head of Dept (IT) |
Head of Dept (CS) | Principal |
Professor 1 (CS) | Head of Dept (CS) |
Professor 2 (CS) | Head of Dept (CS) |
We’ll create a SmartArt org chart and make it dynamic manually (via Text Pane) or with VBA (using InputBox). We might have the data in any of the formats shown in the image.
2. Creating a Dynamic Organizational Chart
Step 1: Click on the “Insert” tab on the ribbon. Then select “SmartArt” from the illustration group. A dialog box will appear on the screen. In your device, you might have the “SmartArt” option somewhere else in the insert tab.
Step 2: Now select “Hierarchy” from the vertical menu on the left side. Then choose “Organization Chart” and click on “OK”.
Now the chart will be displayed on your screen.
Step 3: Click on the “Design” tab on the ribbon. Then select “Text Pane” from the top-left corner of the ribbon.
And copy all the information about the organization and paste it anywhere in the text pane.
Step 4: To change the data and hierarchy of each role follow any of the methods given below:
Method 1:
After inserting data into the 'Text Pane' click on any role and put it under other higher roles. To place a role under another role to whom that role is reporting simply press 'Tab' to make it hierarchical.
And to show higher level roles just enter 'Backspace'. You'll understand this better after watching the video.
Method 2:
To show a role with higher priority just right-click on that role in the text pane and select "promote". The "promote" simply means that you're promoting that role to a higher level/position than its current level. And to show a role with lower priority right click on the role and then select "demote". The "demote" is used for demotion.
For Demoting:
After Demoting you can see the result.
For Promoting:
After Promoting you can see the result.
Step 5: You can also add new roles in the chart by adding that role name in the text pane. And then promote or demote it as per the priority. Here the role "Professor 3" has been added under "Head of Department (IT)". That means "Professor 3" is reporting to the "Head of Department (IT)".
Step 6: You can change the structure, color, font, and size of the chart too. When you click on the org chart Design and Format options will be available to you in the ribbon.
