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
UPDATE
The Visio Data Visualizer is now built directly into Excel. As of a recent Office update (September 2020), what was once an add-in is now a native feature found on the Insert ribbon in Excel.
If you are running an older build number of Excel, you can still manually add the Visio Data Visualizer per the below instructions.
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
Login Requirement
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.
Changes in Existing Employee Information
If an employee were to change departments and/or roles in the organization, thus receiving a new manager, we only need to update the employee’s Manager ID and refresh the org chart to see the newly updated 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.
Creating a Data Visualizer Object the “Next Day”
If you want to create a new visualization using the Data Visualizer, select Insert (tab) -> Add-ins (group) -> My Add-ins – Microsoft Visio Data Visualizer.
From here we can create a Basic Flowchart…
… or a Cross-Functional Flowchart.
The Cross-Functional Flowchart
The Cross-Functional Flowchart is special in that it arranges shapes into “swimlanes”.
This chart type has some additional table fields, such as Shape Type that supports 15 different shapes, as well as “Yes/No” connectors.
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.)
A Note About Cross-Functional Charts
If you wish to customize a cross-functional chart beyond simple data population, you are required to possess a valid Visio license.
Download Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
