This post is going to show you step-by-step how to create an organizational chart in Google Sheets.
Every organization has an internal structure. This structure defines everyone’s role, relationship, and responsibility between individuals within the organization.
An organizational chart is the graphical representation of the internal structure that exists within an organization.
There are many types of organizational structures, but the hierarchical organizational structure is the most common.
The hierarchical structure is an organizational model that places high-ranking positions/individuals at the top and lower-ranking positions/individuals at the bottom. It uses a top to bottom approach with the highest-ranking individual, usually the CEO, placed at the top.
The clarity of responsibility that comes with having an organizational chart makes it essential for the various interdependent departments of an organization to function without friction.
In this post, you will see a step-by-step guide to creating an organizational chart in Google Sheets.
Download your copy of the example workbook from the above link to follow along.
Data Structure Setup
Getting the source data structure correct is the first and perhaps most important step when you’re creating an organizational chart in Google Sheets.
When setting up your data, you must make sure it has at least two columns that contain the hiarchy. The reason for this will become apparent later.
Just keep in mind that you should have no more than two or three columns.
Each column should have the following information.
- Employee name.
- Job title or position.
- Whom they report
You can keep this simple by naming the columns as appears in the image above. Instead of using employee positions, you can opt to use the names of employees in the Reports to column.
How to Insert an Organizational Chart
When you have your data properly set up, the next step is to insert a chart.
Go to the Insert menu and clicking on Chart.
Ensure that the range you have selected is the range you want to use for the chart before clicking on Charts. This will help you to avoid any technical issues that might come up while creating the chart.
The Chart editor window will open to the right of your spreadsheet.
Follow these steps to create the organizational chart.
- Select Organizational chart from the Chart type option. You will find it at the bottom of the list.
- You can select or edit the Data range. In this case the data source is in A1:C11.
- Check the Use row 1 as headers box found below the Tooltip selection. This will allow you to select the following items based on the column name.
- Select Title for the ID field.
- Select Reports to for the Parent field.
- Select Employee for the Tooltip field.
You should have a chart on your current sheet that looks exactly like this. You can expand it with the square boxes on the edges.
Organizational charts are great because it’s immediately obvious who reports to who.
Customize Your Organizational Chart
Now that you have created an organizational chart, you can change how it looks to fit your requirements.
In the Chart Editor, click on the Customize tab.
You can change the node Size in the chart, the Node color, and the Selected node color.
The Selected node color will reflect when you click on a particular node in the chart.
Move the Organizational Chart to Its Own Sheet
If you don’t want the chart to appear on the same sheet where the data is, you can also change that.
Click on the three vertical dots at the top right-hand corner of the chart. From the options, select Move to own sheet.
The chart will instantly be exported to a new sheet.
When you place the mouse on top of each node, the Employee name attached to the position will display. That is because you have placed the Employee column in the Tooltip field when creating the chart.
Publish an Organizational Chart
Now that you have created your organizational chart, you can share it with external sources.
To share it, you can choose to download it in a suitable format and send it to others.
You can also share the chart by publishing it on a website by using the Publish chart command.
When publishing the chart, you have two options, Link or Embed.
When you click on Publish, the Link option will create a direct link to the chart that you can share with others.
Publishing with the Embed option will create an HTML code that you can paste onto the website where you want to share the chart.
In fact, here’s an example of the organizational chart embedded in this post! Notice you can interact with this embedded chart by selecting any node or hovering over a node to view the tooltip.
Additional controls in the Published content and settings section provide more options for you to modify the content you’re publishing.
You can choose to Stop publishing the chart or restrict those who can see or use the published content with the Restrict access to the following option.
You can also make the chart update to reflect changes with the Automatically republish when changes are made option.
The awesome thing about creating an organization chart in Google Sheets is that you don’t have to recreate it when something in the organization changes.
The chart will automatically update to reflect any changes in the source data.
You don’t have to look too far when you’re thinking of creating a pictorial representation of your organization’s structure, thanks to the power of Google Sheets.
Do you think you’ll find this organizational chart type helpful? Let me know in the comments below!