Duplicates in the First Column in Organizational Chart

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:

Including Duplicates in First Column in Organizational Chart

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”.

Many-to-one in Org Chart

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 NumberNumber of Spaces to Add
1Nil
21
32
43

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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.