Duplicates in Organizational Chart (Dual Reporting) in Google Sheets

Duplicates in an organizational chart refer to dual reporting, and Google Sheets’ org chart doesn’t support this without a workaround approach.

What is Dual Reporting?

Dual reporting occurs when a subordinate works under two superiors. For example, a software engineer might report to both a project manager (for project-specific tasks and deadlines) and a functional manager (for day-to-day job responsibilities).

In the case of dual reporting, Google Sheets won’t recognize the second occurrence of the subordinate when plotting the organizational chart.

Here’s an example of duplicates in an organizational chart in Google Sheets.

Example

In the following example, employee names are listed in column A, and column B holds the names of the superiors to whom the employee reports.

EmployeeManager
AliceCarter
BurtCarter
MaryCarter
ChrisAlice
SusieAlice
OliveBurt
RayMary
TonyMary
TroyBurt
OliveMary

This two-column structure is the most common data format for creating an organizational chart in Google Sheets. Of course, the first column can contain employee positions, and the second column can contain superior positions instead of names.

Dual reporting occurs when multiple occurrences (duplicates) of values (names/positions) appear in the first column.

In our example, the employee “Olive” reports to both “Burt” and “Mary.” As a result, Olive’s name appears twice in column A. However, the chart only assigns Olive under Burt since it’s the first occurrence in the table.

Dual reporting in organizational chart and missing node in Google Sheets

Including Duplicates in the First Column in the Organizational Chart

First, create the chart as usual:

  1. Select the range A2:B11.
  2. Click Insert > Chart.

In the chart editor panel, select “Organizational Chart” under Chart type. Now, let’s address the duplicates in the first column of the table used to create the organizational chart.

Addressing Duplicates (Dual Reporting) in the Organizational Chart

To resolve this, double-click the cell in column A that contains the second occurrence of the employee name. Move the cursor after the last character in the name and press the spacebar once.

For example, in my sample data, you can double-click cell A11, press the spacebar once, and hit Enter. Instantly, the chart will display the name “Olive” under the manager “Mary.”

Handling duplicates in the table for organizational chart in Google Sheets

This method helps handle duplicates in the organizational chart in Google Sheets.

Logic

When you add a space, the name becomes distinct. Google Sheets treats it as a different name, thereby eliminating duplicate issues in the first column of the table.

What if a Subordinate Reports to More Than Two Managers?

To handle this, you should make all occurrences distinct by adding spaces at the end of the names in the first column of the table.

Occurrence NumberNumber of Spaces to Add
1None
21
32
43

This method addresses duplicates in organizational charts.

The main drawback of this workaround is that the label on the node may not be centered. However, you won’t notice any significant difference in alignment if one or two spaces are added.

Resources

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.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

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.