How to Create an Organisation Chart in Google Sheets

An organization chart (often referred to as a “site org chart” in EPC contracts) visually represents the structure of a company or group, showing roles, relationships, and hierarchies. With minimal effort, you can create an organisation chart in Google Sheets. This tutorial will guide you through formatting your data correctly to generate the chart seamlessly.

What Is a Site Organisation Chart?

A site organisational chart is essentially a regular organisation chart but tailored for construction or project sites. The chart structure reflects the site’s hierarchy, such as a project manager at the top, followed by managers, site in-charges, safety officers, planning engineers, and so on. The difference lies in the designations of the employees, not in the process of creating the chart.

Having worked with EPC companies, I have experience preparing these charts. In the past, I used Excel’s drawing tools to create them manually. While effective, they weren’t flexible. Any changes, like adding or removing an employee, required rearranging all the text boxes manually.

In contrast, Google Sheets allows you to create and update site organisation charts effortlessly without relying on drawing tools.

The Four Steps to Create an Organisation Chart in Google Sheets

Here’s how you can create an organisation chart in just a few steps:

  1. Enter Employee Data
    • In Column A, list the names of employees along with their designations in brackets.
    • In Column B, enter the names of their immediate supervisors (reporting officers) with designations in brackets.
  2. Select Your Data
    • Highlight the data you’ve entered in columns A and B.
  3. Insert the Chart
    • Go to the menu Insert > Chart.
  4. Choose Organisational Chart
    • In the Chart editor, select Organisational Chart.

Data Formatting Example

Properly formatted data is key to creating an effective organisation chart. Here’s an example:

Formatted data for an organisation chart in Google Sheets.

In this example:

  • Alfredo, the project manager, doesn’t report to anyone, so Column B for Alfredo is left blank.
  • Maria reports to Alfredo, so Alfredo’s name appears in Column B for Maria.
  • Similarly, other employees like David, Sam, and Anne report to Maria.

Plotting the Chart

  1. Once your data is formatted (e.g., in A1:B14), select it.
  2. Navigate to Insert > Chart.
  3. In the Chart editor, select Organisational Chart as the chart type.
  4. Your chart will now display the hierarchy based on your data.
Example of a site organisation chart with roles and hierarchy

Organisation Chart Customization Options

Unlike other chart types, organisation charts offer limited customization options. However, you can make a few adjustments using the Customize tab in the Chart editor:

  1. Node Size
    • Change the size of the rectangular nodes to Small, Medium, or Large using the Chart editor.
  2. Node Colors
    • Customize the background color of the nodes or the color of the active/selected node.

Tips and Best Practices

  • Always include designations in brackets for clarity.
  • Ensure the hierarchy is accurate by correctly assigning reporting officers in Column B.
  • Update the chart easily by modifying your data—Google Sheets automatically adjusts the chart.
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.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

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.