How to Make a Hierarchy Chart in Google Sheets
Trying to make sense of a company's structure or a project's hierarchy can feel complicated, but Google Sheets has a surprisingly useful tool for the job: the hierarchy chart. This built-in feature lets you turn a simple list of names and roles into a clean, easy-to-understand org chart. This tutorial will walk you through exactly how to set up your data and create a professional hierarchy chart in just a few minutes.
What Exactly is a Hierarchy Chart?
A hierarchy chart, most commonly known as an organizational chart (or org chart), is a diagram that visually represents the structure of an organization. It shows who reports to whom, outlining the formal relationships and chains of command between roles, departments, or even specific tasks within a project.
Think of it as a map of your team. At the top, you have the head of the organization, like a CEO or a Project Lead. Branching down from there, you see the managers or team leads who report to them, followed by the individuals who report to those managers. Each connection illustrates a direct reporting line, making it clear how information and responsibility flow through the organization.
But it’s not just for people. You can use hierarchy charts to map out anything with a clear parent-child structure, such as:
- A company’s management structure: The classic use case, showing the entire command chain from the C-suite down to entry-level positions.
- Project task breakdowns: Illustrating which sub-tasks fall under a larger project phase.
- A website’s site map: Visualizing how subpages branch off from main category pages.
- A family tree: Mapping out genealogical relationships across generations.
Free PDF Guide
AI for Data Analysis Crash Course
Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.
Getting Your Data Ready for Google Sheets
This is the most important step. If your data isn’t formatted correctly, Google Sheets won’t know how to build your chart. The entire process hinges on setting up a simple table with at least two, and optionally three, columns that define the relationships.
The Required Columns
Your sheet needs two essential columns to function:
- Column 1: The 'Child' or 'Node' ID. This column lists the unique identifier for every single person, role, or item in your chart. Think of these as the individual boxes in the chart. Your values in this column must be unique.
- Column 2: The 'Parent' ID. This column lists who each 'child' reports to. The name here must exactly match an entry from the 'Child' column. The very top-level node (like the CEO) should have this cell left blank, as they don't report to anyone.
The Optional Third Column (for Tooltips)
You can add a third column to include extra information that will appear as a tooltip when you hover your mouse over a specific node in the chart. This is incredibly helpful for adding context without cluttering the main visual.
- Column 3: Tooltip Text. You can use this for job titles, department names, email addresses, or any other short piece of text.
Example Data Structure
Here’s a practical example of how to structure your data for a simple company org chart. Notice that "Amelia Chen (CEO)" has a blank "Reports To" cell, signaling that she is at the top of the hierarchy. Also, every name in the "Reports To" column exactly matches a corresponding name in the "Name" column.
Here's how you'd set that up in a Google Sheet:
Pro-Tip: Keep naming conventions consistent! "Ben Carter" and "Ben C." will be treated as two different people by Google Sheets, which can break your chart.
Creating The Hierarchy Chart: A Step-by-Step Guide
Once your data is locked in and correctly formatted, creating the chart itself takes less than a minute. Let’s walk through the steps.
Step 1: Select Your Data
Click and drag your cursor to highlight all the data you want to include in the chart. Make sure to include the column headers, Google Sheets often uses them to automatically configure the chart.
Step 2: Insert the Chart
With your data selected, navigate to the main menu at the top of the screen and click Insert > Chart.
Google Sheets will immediately insert a chart onto your sheet, but it will probably guess the wrong type. Most of the time, it will default to something like a pie chart or a bar chart, which isn't what we want. Don't worry, this is normal.
Step 3: Choose the Hierarchy Chart Type
A Chart editor sidebar should appear on the right side of your screen. If it doesn't, just double-click on the chart you just created.
Under the Setup tab in the editor, look for the 'Chart type' dropdown menu. Click it, scroll all the way down to the "Other" section at the bottom, and select Hierarchy chart.
Instantly, your data will transform into an interactive org chart. Google Sheets is usually smart enough to figure out which column is the ID and which is the Parent, but if it gets it wrong, you can manually set them here. Ensure that ID corresponds to your employee/node column, Parent to your Reports To column, and Tooltip to your optional data column.
Customizing Your Chart for Clarity and Style
Your basic chart is ready, but a few quick customizations can make it much more professional and easier to read. For this, you’ll use the Customize tab in the Chart editor sidebar.
Changing Colors and Sizes
The Hierarchy section is where you can change the visual appeal of the nodes.
- Node color: You can set a default color for all nodes in your chart. More usefully, you can click on an individual node in the chart to select it, then use this color picker to give it a unique color. This is great for color-coding different departments (e.g., Marketing in blue, Sales in green).
- Size: By default, all nodes are the same size. If you added a fourth column to your data with numerical values (like budgets or team size), you could use that data to define the size of each node. For most org charts, it's best to leave this blank.
Free PDF Guide
AI for Data Analysis Crash Course
Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.
Adding Chart and Legend Titles
Under the Chart & axis titles section, you can add or edit the main title for your chart. This is important for context. Something like "Company Organizational Structure - Q3 2024" tells everyone exactly what they're looking at.
Troubleshooting Common Issues
Building hierarchy charts is usually smooth, but a few tiny data errors can cause problems. Here’s what to look for if your chart isn’t working as expected.
1. Error: "The visualization contains cyclic dependencies."
- What it means: You've created an impossible loop in your reporting structure. For example, Ben Carter reports to Amelia Chen, but you accidentally listed Amelia Chen reporting back to Ben Carter.
- How to fix it: Carefully review your "Reports To" column. Trace the lines of reporting for the nodes mentioned in the error until you find the person reporting to a subordinate. Remove the loop, and the chart will work again.
2. Problem: A person or node is missing from the chart.
- What it means: This is almost always caused by a typo or inconsistent naming. If Chloe Davis is listed as reporting to "Ben Carter," but the boss’s name in the first column is "Benjamin Carter," Google Sheets can't find a match and will leave Chloe as an orphaned node that doesn't appear.
- How to fix it: Go through your data with a fine-toothed comb. Make sure every name in the "Reports To" column is spelled exactly the same way it appears in the primary "Name" column.
3. Problem: The chart is too dense and hard to read.
- What it means: Your organization has grown too large to be cleanly displayed in a single, screen-sized chart.
- How to fix it: Instead of creating one massive chart, break it down into smaller, departmental charts. Create one high-level chart showing only the CEO and their direct reports (VPs). Then, create separate charts for each department on different tabs in your sheet, with the VP at the top of each. This makes the information much more digestible.
Final Thoughts
Once you get the hang of organizing your data into related columns, creating a hierarchy chart in Google Sheets becomes an easy and powerful way to visualize relationships. It transforms a boring list of names into an insightful map of your organization that everyone can understand at a glance.
While Google Sheets is fantastic for manual org charts, generating insights from live business data often requires connecting to the original source. For that, we built Graphed to automate the entire reporting process. You can connect sources like Google Analytics, Shopify, and Salesforce in seconds, then ask for a dashboard using plain English - like "create a sales performance dashboard by rep for this quarter" - and instantly get a live, interactive visualization without manually formatting any data.
Related Articles
How to Sell Mockups on Etsy: A Complete Guide for Digital Sellers
Learn how to sell mockups on Etsy — from creating your first product to pricing, listing optimization, and driving consistent sales.
The Bookmarks Market: Trends, Opportunities, and How to Win on Etsy
The bookmarks market is growing. Discover the trends, buyer personas, and strategies helping Etsy sellers win in this profitable niche.
How to Start a Bookmark Business on Etsy: A Step-by-Step Guide
Thinking of starting a bookmark business? Learn how to design, price, and sell bookmarks on Etsy for steady creative income.