How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...


Duplicates in First Column of the Table in Organizational Chart in Google Sheets

Posted by   on

This post is about an issue that you may face when creating an organizational chart in Google Sheets. It’s like this.

If one employee works under two (or more) managers or one employee works in two (or more) departments, you may end up with duplicates in the first column of the table in the organization chart in Google Sheets.

In other words, you want to plot a chart when –

  • Two or more hire ranks have the same lower rank member.

  • Two or more parent IDs for one member ID.

– you will face the duplicate name/ID, or we can say many-to-one relationship issues.

What happens in such a scenario?

You may have duplicates 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 issue is, the Organizational chart in Google Sheets won’t pick the second occurrence, and as a result, the same will be missing on the chart.


Including Duplicates in First Column in Organizational Chart

As you can see, the employee “Olive” is working under the managers “Burt” and “Mary”.

But in the chart, the name “Olive” only appears once because we can’t have duplicates in the first column of the table in the Organizational Chart.

How to Include Duplicates in the First Column of the Table in Organizational 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 the line drawing tools to draw site org charts. It was easy to drag and drop the objects to create org charts.

In my personal opinion, the drawing tool in Google Sheets is not user-friendly compared to the drawing tool available in Excel.

Then what is the solution?

We must think about a workaround involving the built-in chart, and here it is.

Here are the steps to include duplicates in the first column of the table in an Organizational Chart in Google Sheets.

For the sample data, please refer to the cell range A2:B11 in the above example.

  1. Arrange the data in two columns (columns A and B) as per the example above. The first one is for employees (member IDs), and the second one is for managers (parent IDs).

  2. In column A, double-click the cell that contains the second occurrence of the employee name.

  3. Tap the space bar once and hit the enter key to insert a white space at the end of the name.

As per my sample data, you may double click cell A11, tap the space bar, 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 include two-parent IDs (“Burt” and “Mary”) for one member ID (“Olive”).

What is the Logic of Adding Space Character to the Name?

When you add a space, the name became unique. Sheets treats it as a different name. So there won’t be any duplicate issue in the first column of the table.

What about more than two occurrences of the name “Olive”?

To make all the occurrences unique, please add spaces as follows.

Occurrence NumberNumber of Spaces to Add

The main drawback of the above workaround is the label on the node may not be aligned to the center.

But, you won’t 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, it supports three columns, and the third column is optional.

In the first column, we can enter the names of all employees (member IDs). It must not contain duplicates. If duplicates are there in the first column of the table, then follow my above workaround.

The second column is for entering the name of each employee’s manager, parent, or hierarchical relationship.

The third column is for showing tooltips while hovering your mouse pointer over the nodes. You can usually use it for showing additional notes such as designation/position in the organization.

The same I have already detailed here – How to Add Tooltips to Org Chart in Google Sheets.

That’s all about how to allow duplicates in the first column of the source data in Organizational Chart in Google Sheets.

Thanks for the stay. Enjoy!

No comments:
Write Comments

Hello Friends, welcome to we Hope You'll like it - COntact US