How to Create an Org Chart in Google Sheets

Creating an organizational chart doesn't have to involve complex design software or expensive subscriptions. You can build a clean, editable, and shareable org chart right inside a tool you already use every day: Google Sheets. This guide will walk you through exactly how to set up your data and generate a dynamic org chart automatically.

First, Why Use Google Sheets for an Org Chart?

While dedicated apps like Lucidchart or Visio offer more bells and whistles, Google Sheets has some powerful advantages, especially for small to medium-sized teams.

• It's Free and Accessible: If you have a Google account, you have Google Sheets. There's no new software to learn or pay for.
• It's Collaborative: Just like any other Google Sheet, you can easily share your org chart, allow comments, and let multiple people edit it in real-time.
• It's Data-Driven: Unlike manually drawing boxes and lines, an org chart in Google Sheets is generated directly from your data. When someone's role or manager changes, you just update a single cell, and the chart rebuilds itself instantly.
• It’s Centralized: You can keep your employee roster and visual org chart in the same workbook, making it a single source of truth for your team structure.

Of course, it's not perfect. The customization options are limited, and for massive organizations with hundreds of employees, the chart can become crowded and difficult to read. But for most teams, it’s a surprisingly effective solution.

Step 1: Structure Your Employee Data

The magic behind the Google Sheets org chart feature is all in the data setup. You need to create a simple table that tells Google who reports to whom. Get this right, and the rest is a breeze.

You need at least two columns, but a third one is highly recommended for adding detail.

Required Columns:

1. Employee/Entity Column: The first column should contain the name or unique ID of each person or position in your organization. Every entry in this column must be unique. This is what Google Sheets uses to identify each node of the chart.
2. Manager/Parent Column: The second column should list the name or ID of the direct manager for each person in the first column. This value must match an entry from the first column. The only exception is the person at the very top of the hierarchy (like the CEO), who should have this cell left blank.

Recommended Column:

1. Tooltip/Add-on Info Column: This optional third column lets you add extra text that appears when someone hovers over a person's name in the chart. This is perfect for adding job titles, departments, or other helpful context without cluttering the main visual.

Here’s an example of how your data should look for a small marketing agency:

You would set up a sheet like this:

Let's break down the key parts:

• Amelia Chen is at the top, so her 'Manager Name' cell is intentionally left empty. This tells Google Sheets she is the root of the chart.
• Brian Garcia reports to Amelia Chen. Notice that "Amelia Chen" appears in both the first and second columns.
• Leo, Mia, and Noah all report to Brian Garcia, creating three branches under him in the hierarchy.

The "Job Title" column will provide extra context on hover, but won't appear directly on the chart unless the node size allows for it. Feel free to add more columns for your own records (like start date or department), but make sure the first three columns are set up in this specific way for the chart builder to work.

Step 2: Generate the Organizational Chart

Once your data is correctly formatted, creating the chart takes less than a minute.

1. Select Your Data: Click and drag your mouse to highlight all the cells containing your data, including the headers.
2. Insert a Chart: Navigate to the top menu and click Insert > Chart. Google Sheets will probably try to guess what kind of chart you want and might insert something like a pie chart or bar graph first. Don't worry, we'll fix that in the next step.
3. Choose 'Organizational chart': The Chart editor sidebar should appear on the right side of your screen. Under the 'Setup' tab, click the dropdown menu under 'Chart type'. Scroll down to the 'Other' section at the bottom, and select Organizational chart.

And that's it! Google Sheets will read your data and instantly generate an org chart based on the reporting structure you defined.

Step 3: Customize Your Org Chart

You've created the chart, but you can still tweak its appearance to make it more readable and visually appealing. In the Chart editor, click over to the 'Customize' tab.

Node Size

Under the 'Org' section, you can change the size of the nodes (the boxes containing each person's name) to Small, Medium, or Large. If you used the optional third column for 'Tooltips', some of that text might become visible directly on the chart if you select Medium or Large. For most charts, 'Small' keeps things looking clean and relies on hovering to show extra details.

Node and Selection Color

You can also change the color of the nodes to match your company's branding or to color-code by department. The 'Node color' option changes the default box color, while the 'Selected node color' changes the highlight color that appears when you click on a specific box in the chart.

With just a few clicks, you have a functional, data-driven org chart that lives right alongside your employee data.

Tips and Tricks for a Better Org Chart

Want to take your org chart a step further? Here are a few tricks to handle more complex scenarios and get more from your chart.

Add More Info to Tooltips with a Formula

What if you want to show both the job title and the department when you hover over someone's name? You can easily do this by creating a new column with a formula that combines the text from multiple cells.

Imagine your data is set up with 'Job Title' in Column C and 'Department' in Column D. You could create a new 'Tooltip' column in Column E and use this formula:

=C2 & CHAR(10) & D2

Let's break that down:

• =C2 gets the job title.
• & is a concatenation symbol that joins text together.
• CHAR(10) is the formula for a line break. This is the key part that puts the department on a new line.
• & D2 gets the department.

Drag this formula down for every employee. Now, use this new combined column as your 'Tooltip/Add-on Info' column when creating the org chart. The hover text will be neatly organized on two lines.

Handling Large or Complex Organizations

If you're charting hundreds of people, the default org chart can become a tangled, horizontal mess. Here are a couple of approaches:

• Break it down: Instead of one giant chart, create separate charts for each major department. Just filter your data to show only the Marketing team, for example, and create an org chart from that subset of data. You can place each chart on its own tab within the sheet to keep things organized.
• Use a Vertical Layout: If you really need a top-down view and are comfortable with a more manual approach, you might have to skip the chart builder. You can use Insert > Shapes and Insert > Drawing to manually create boxes and lines. This gives you total control over the layout, but beware: it's not data-driven. Any structural changes will require you to manually move boxes and reconnect lines.

Sharing and Publishing Your Chart

Once you're happy with your org chart, you can treat it like any other dashboard component in Google Sheets.

• Click the three dots in the corner of the chart to copy it and paste it into a Google Doc or Google Slides presentation. It can even be linked so that it updates automatically when the data in the sheet changes.
• Use the blue 'Share' button in the top right of the Google Sheet to give colleagues view-only or edit access to the chart and its underlying data.

Final Thoughts

You now have everything needed to create a clear and effective organizational chart using just a Google Sheet. By structuring your employee data with a simple parent-child relationship, you can use the built-in chart tool to instantly visualize your company’s hierarchy and easily keep it updated as your team evolves.

While an org chart is great for understanding team structure, getting a clear picture of business performance requires connecting data from all your different tools. To simplify this, we built Graphed to be your AI data analyst. Instead of grappling with spreadsheets or learning complex BI software, you can connect your advertising platforms, CRM, and analytics tools, and just ask questions in plain English to build real-time marketing and sales dashboards. It’s the easiest way to see what's actually working without spending hours building manual reports.