This post discusses an issue that you may encounter when creating an organizational chart in Google Sheets. If one employee reports to two (or more) managers or works in two (or more) departments, you may end up with duplicate entries in the first column of the table in the organizational chart in Google Sheets.
In other words, if you want to create a chart where:
- Two or more managers have the same employee reporting to them.
- One employee has two or more managers/bosses.
You will encounter a duplicate name or ID issue, also known as a many-to-one relationship issue.
What happens in this scenario?
You may have duplicate entries in the first column of the table of the organizational chart. There is no other way to format the source data for the chart.
The problem is that the Organizational Chart in Google Sheets will not pick up the second occurrence of the name or ID, and as a result, it will be missing from the chart.
Example:
As you can see, the employee “Olive” is working under the managers “Burt” and “Mary.” However, in the chart, the name “Olive” only appears once because Google Sheets does not allow duplicates in the first column of the table in the Organizational Chart.
Here is a solution for two employees reporting to one boss in the org chart in Google Sheets:
The Solution to Two Employees Reporting to One Boss in Org Chart
We can usually draw an organizational chart in two ways in spreadsheet solutions: using the drawing tool or the built-in chart.
In Excel, I used the text box and line drawing tools to draw site organizational charts. It was easy to drag and drop objects to create organizational charts.
In my personal opinion, the drawing tool in Google Sheets is not as user-friendly as the drawing tool in Excel.
So, what is the solution to two or more employees reporting to one boss or manager in an organizational chart in Google Sheets?
We can think about a workaround involving the built-in chart. Here it is.
A Workaround for Including Duplicates in the First Column of an Organizational Chart
For the sample data, please refer to the cell range A2:B11 in the above example.
Arrange the data in two columns (columns A and B) as per the example above. The first column is for employees (member IDs), and the second column is for managers (parent IDs).
In column A, double-click the cell that contains the second occurrence of the employee name.
Press the space bar once and hit the enter key to insert a single space at the end of the name.
As per my sample data, you may double-click cell A11, press the space bar once, and hit the enter key. Instantly, the chart will show the name “Olive” under the manager “Mary”.
This way, you can assign two parent IDs (“Burt” and “Mary”) to one member ID (“Olive”).
The Logic Behind Adding a Space Character to the Name
When you add a space, the name becomes unique. Google Sheets treats it as a different name. Therefore, there will be no duplicate issues in the first column of the table.
How can I add more than two managers or bosses for an employee in the org chart in Google Sheets?
To make all the occurrences unique, please add spaces as follows.
Occurrence Number | Number of Spaces to Add |
1 | Nil |
2 | 1 |
3 | 2 |
4 | 3 |
The main drawback of the above workaround is that the label on the node may not be aligned with the center. However, you will not see any noticeable difference in the alignment if the number of spaces added is one or two.
Additional Tips
How many columns can I have in an organization chart in Google Sheets?
As far as I know, an organizational chart in Google Sheets supports up to three columns. The third column is optional.
The first column is for entering the names of all employees (member IDs). It must not contain duplicates. If duplicates are present in the first column of the table, then follow the workaround I mentioned above.
The second column is for entering the name of each employee’s manager, parent, or hierarchical relationship.
The third column is for showing tooltips when you hover your mouse pointer over the nodes. You can usually use it to show additional notes, such as the employee’s designation or position in the organization.
I have already detailed how to add tooltips to an organizational chart in Google Sheets here: How to Add Tooltips to Org Chart in Google Sheets.
Thanks for the stay. Enjoy!