Learning how to make an organization chart in Google Sheets is useful for showing the hierarchy of employees in a company, relationships between groups of people, or a family tree.
Table of Contents
In this article, we’ll learn how to easily make an organization chart (org chart) by making use of Google Sheets’ built-in chart maker. First, we will determine how to properly format our data to ensure that the chart made will display the correct relationship. Lastly, we shall learn two ways to combine names to titles in an org chart, either as tooltips or as part of the data set.
Consider this example.
You were chosen as the representative to present your company in a career series seminar, with the goal of gaining applicants for the internship program the company offers. To get to know the structure of the company better, you were instructed to include an organization chart in your presentation. The list of employees and their corresponding positions are as follows:
All the VPs are designated under the CEO, while the Accounts Manager and Finance Executive report to the VP for Finance, Web Developer and Admin report to the VP for Operations, and Digital and Brand Marketing report to the VP for Marketing. Thinking of manually creating and editing multiple lines and text boxes already give you a headache.
Here’s some good news! Did you know that Google Sheets have a built-in chart maker that includes organization charts? No need for any complicated tools for making this diagram. You can easily make an org chart with a few clicks, as long as your data is formatted properly.
Let me show you how to prepare your data to ensure that the org chart you create will show up properly!
Preparing the Data for an Organization Chart
By default, charts made in Google Sheets use data that are grouped by columns. Of course, this also applies to the organization chart. For this chart type, the types of data it needs are called ID and Parent. ID pertains to the list of all entities in the org chart, while Parent refers to the entity directly above the hierarchical relationship. Therefore, you only need to format your data into two columns. An optional third column may be referenced (called ToolTip) and will be discussed further in a later segment.
For the example earlier, the table of data involving only the job titles will be:
If the names are preferred to be used, then the table should be:
How to Make an Organization Chart in Google Sheets
Now that we have properly prepared our data, let’s finally learn how to create an organization chart!
- To insert a chart, first, specify the data on which our chart will be based on. Select the range A1:B11. Go to the upper menu and select Insert > Chart.
- From the Chart editor toolbar at the right, change the chart type to organisational chart.
- Check the ‘Use row 1 as headers‘ checkbox. You should end up with the following diagram:
- For an organizational chart containing the names of the employees, simply repeat the process, this time selecting cells D1:E11. Doing so will lead to you the following diagram:
Although there is not much that you can customize with the chart created, you can change the size (small, medium, and large) and the color of the nodes. Just go to the Customise tab of the Chart editor toolbar.
Simple right? We were able to create two organization charts in a matter of a few minutes, how easy! Just make sure you format the table properly and Google Sheets will do the rest!
You think to yourself, but what if you want to include both the name and position in the organization chart? This way, the audience won’t have to look at two separate charts. There are actually two ways for you to do this in Google Sheets.
Let’s start with the first one!
How to Add Names to Organization Charts as ToolTips
You might have noticed in the Chart Editor toolbar that another set of data may be obtained by the organization chart. This field is called the ToolTip. This section allows you to view another set of text pertaining to the ID parameter specified. Once you hover your mouse over one of the nodes in the chart, a text box will appear showing the text in the ToolTip column.
For our example, adding this data can allow you to display the job titles on the org chart first and hover around the nodes to show the names or vice versa. Let’s see how to do it in the example. This part of the tutorial will focus first on making a new organization chart. Adding ToolTips to existing org charts shall be discussed afterward.
- To insert a chart, first, specify the data on which our chart will be based on. For this example, three columns are required to obtain data. A column for the ‘designation’, another for the ‘reports to’, and lastly for the ‘name’. Select the range A1:C11. Go to the upper menu and select Insert > Chart.
- From the Chart editor toolbar at the right, change the chart type to organisational chart. Check the ‘Use row 1 as headers‘ checkbox. You should end up with the following diagram:
- To verify if a ToolTip has been added, select the chart and hover your mouse over one of the nodes. A text box should pop up for each node, revealing the names of the employees.
Notice how the first column is automatically selected under ID, the second column as Parent, and the third column as ToolTip. Google Sheets considers data placed in the same row as pertaining to a single data point. Therefore, you only need to specify the corresponding ToolTip at the same row as ID.
If you wish to include ToolTips in an existing chart, first select the chart itself. Proceed to the Setup tab of the Chart editor toolbar, click on the Add ToolTip button, and then click on the select data range button (). A window will pop up. Select the column containing the data you wish to put as a ToolTip and select OK.
Note that this feature will not be exported once you save the chart as an image or PDF file. If you wish for both to be presented in the organization chart, keep reading below!
How to Add Names to Organization Charts Using CONCATENATE Function
To include both the name and position in the organization chart, both need to be included in the data set. This means that both details must be in the same cell already. One can simply type out these two together, but it will result in an organization chart that has nodes that are too long. To better maximize the space of a single node, these details should be placed in a separate line, like the ones illustrated below:
When you try to manually type these values and press Enter in Google Sheets, however, instead of creating another line in the same cell, it moves to the cell below. How was I able to do it? Simple! Just press Alt+Enter to insert another line in the same cell. However, manually re-typing these values for each employee is time-consuming. Let me teach you a faster way to combine these texts using the CONCATENATE function.
The CONCATENATE function appends strings to one another. In other words, they combine two or more text strings together into a single cell. We shall also make use of the CHAR function to insert a new line in the formula. For more details about these functions, proceed to the google docs editors help sections for each function: CONCATENATE and CHAR.
- To make sure each value is combined properly, ensure that each pair is in the same position relative to the other. Since we already have two versions of the required table format for the job titles and names, simply place them side by side. Then, simply click on any cell to make it the active cell. For this guide, I will be selecting E2, where I want to show my result.
- Next, simply type the equal sign ‘=’ to begin the function, followed by the name of the function, which is our ‘concatenate’ (or ’CONCATENATE’, whichever works). Select the first text string, cell A2, followed by a comma. Type the expression ‘CHAR(10)’ and then another comma. This expression inserts a line after the text in the first string. Lastly, select the second string, cell C2.
- Press Enter to finish the function. Notice how the cell’s size is adjusted to accommodate the new line you have inserted. Copy this formula for the rest of the items in the table. You should end up with the following table:
- To insert the chart, simply follow the steps presented in the ‘How to Make an Organization Chart‘ section of this article. You should end up with the following diagram:
We’re done! It’s easy to create organization charts, right? This tutorial not only presented the steps to create one using Google Sheets’ built-in chart maker but also demonstrated two ways to add names to the chart. If you want to practice some more, make a copy of our spreadsheet and give it a try:
Or browse our other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.
