- The simplest way to Make an Organizational Chart Template in Google Sheets: Format Data > Select Data > Create Chart from > “Insert” > “Chart” > Chart Type > Correct Spelling > View Notes > Adding Top Hierarchy Note.
In this article, we will learn about how to make an Organizational Chart Template in Google Sheets. A free downloadable Organizational Chart Template is also available at the end of the article.
Table of Contents
What is an Organizational Chart Template in Google Sheets?
Google Sheets provides many automated tools for daily spreadsheet problems. We can use those automated tools for the solution to our problems. One such tool is the Organization Charts tool provided for automatically making Organizational Charts in Google Sheets. It saves up a lot of precious time for users that would otherwise be spent on finding the symbols and adding them together for the creation of organizational charts.
Organizational Charts provide a view on relationship or reporting hierarchy of an organization, structure or group in the form of symbols and pictures. We can get a good idea of responsibility shifts and reporting mechanism by looking at the organizational charts. If the same information is displayed in textual form, it can get confusing to figure out the actual hierarchy and take up extra time analyzing it. We will discuss the meaning of Organizational Chart “Template” ahead.
In this article, we will discuss:
- How to format the data for making Organizational Chart?
- How to make Organizational Chart in Google Sheets?
- How to correct the Glitch due to Spellings in Organizational Charts in Google Sheets?
- How to view the note (or third column) on any employee of Organization Chart in Google Sheets?
- How to add note to the top of hierarchy in Organization Chart in Google Sheets?
- How to make Organizational Chart “Template” in Google Sheets?
Let’s get started!
Use Cases of Organizational Chart Template in Google Sheets
Google Sheets provide us with automated tools for making charts and graphs. Rather than wasting our time on creating representation of data and information, we can spend more time on analyzing it and doing something more useful as Google Sheets will make those charts and graphs for us automatically with a matter of clicks.
Let us say we have Organizational Hierarchy of an organization in terms of its employees. This organizational hierarchy completely describes the structure of organization and also informs who works under whom. In case of handling responsibility for a certain event, we may need to see the hierarchy structure to decide if the event management should be assigned to certain employee considering its supervisor’s capability.
Organizational hierarchy in the form of table and text requires quite a long time to analyze. Consider trying to find the responsibility shift weight of Jimmy in the sheet below:
What you actually have to do; is to find the supervisor of Jimmy, and then supervisor of his supervisor and in turn his supervisor until we reach the highest order of hierarchy. Let us try doing by analyzing from the table above. What you do is actually as tricky as:
Rather than going through the information details one by one to find hierarchy of structure, we can easily make Organizational Chart with a few clicks in Google Sheets and analyze that chart to find out responsibility shift at a glance.
In the same way, Organizational Chart Templates help us to create a sample building block for organizational charts to be made later. It is explained with details ahead.
Let us demonstrate the method to make Organizational Chart in Google Sheets as below.
How to Make Organizational Chart Template in Google Sheets
It is very easy to make any chart in Google Sheets. We just need a few clicks and a knowledge of where to use the clicks. We can create an organizational chart in Google Sheets with minimum effort using the method described below. But first of all, let us understand what we need to make an organizational chart.
How to format the data for making Organizational Chart
In terms of information, two attributes of information is necessary to have; employee and its supervisor (or manager). Basically, we need the data in the form of a table. Columns should be as:
- First column contains the title (or name) of employees or their job titles.
- Second column contains the name (or title) of supervisor or manager of that employee.
- Third column (Optional) contains some notes that are displayed when you move cursor over the employees’ names or titles. For example, if first column contains job titles, note may contain employee’s name; if first column represents names of employee, it may contain the job title or experience of the employee, etc.
- Each row represents information of each employee on the organization chart.
So, what we need in order to make organization chart is; the data formatted in the proper format according to above given details.
Let us just take the above mentioned example; Organizational Hierarchy structure where we are trying to find the responsibility weight shift on Jimmy. Let us see the proper format for making organization chart with respect to the example as:
As the data is in proper format, we can now make organization chart using this data table as discussed below.
How to make Organizational Chart in Google Sheets
When making organization charts in Google Sheets, always remember that data must be in the above mentioned format so that Google Sheets can detect the data types and make organization graph automatically. It does not matter if the employee details are given in order from high to low in rows but columns should be as mentioned above. If data is already placed in the above mentioned form of table, then it is only a few further steps away from a nice and cool organization chart. All you need to do is to follow the steps given below:
Step 1: Open the Google Sheet which contains the data table for making Organization Chart.
Here, let us use the above shown data for the demonstration of organization table as:
You can see that we have three columns; Employee name, Supervisor name and lastly the experience of each employee. We have 10 rows here, each representing one employee. As already discussed, the third column is optional as it will only show up when cursor is placed over employee name.
Step 2: Select the data table for organization chart.
Remember that we only need to select the data table and not its labels or titles.
Here, we are going to select the three columns which play role in making of our organization chart as:
We can see the selected cells with blue outline as above.
Step 3: Create Chart using one of the methods below:
Method 1: Click Chart icon displayed on the Google Sheets as:
Method 2: Select Chart from Insert Menu.
Insert -> Chart
Select chart from insert menu as:
Either way, when you click chart, you can see the Chart Panel as well as Chart Editor popped up as:
You can see that by default the Chart Type is set to Column Chart and so Google Sheets is unable to create a column chart for 3 columns, hence it shows “No data” for Chart Panel. Let us make it an Organization Chart so that it will show up.
Step 4: Change the Chart type to Organization Chart.
What we need to do now, is to make the chart from default Chart Type to Organization Chart so that our data can take form of Organization Chart.
Steps to change the type of chart is as:
Chart Editor -> Setup -> Chart Type -> Organization Chart
Now, you can see the organization chart in Chart Panel as:
We can see the Organization Charts in chart panel. Although we meant the same employee for “Cristopher” and “Christopher”, it is treated as a different employee.
Also, read the article further for knowing how to read note on the employees given in the third column.
How to correct the Glitch due to Spellings in Organizational Charts in Google Sheets
Notice carefully that although we meant the same employee when we wrote “Cristopher” instead of “Christopher” in column of employee supervisor but since the spellings were different, Google Sheets tend to take it as another employee and hence two organization charts are displayed in above chart panel. We can correct it by correcting the spelling of “Cristopher” to “Cristopher”.
All you need to do is to correct the spellings from the Supervisor column as:
Now you can see the organization chart automatically updates as:
There is no need to update the chart or make it again. Any set of changes made on the data table are automatically updated on the chart.
How to view the note (or third column) on any employee of Organization Chart in Google Sheets
We know that we made the Organization Chart with 3 columns; Employee name, Supervisor name and Note. Although we can see the Employee name and Supervisor name on the Chart; but Note is not displayed yet on the chart.
Follow these steps to view the note:
Step 1: Select the Organization Chart.
Click anywhere on the Chart to select it. Chart will appear with a blue outline when selected.
Step 2: Point the cursor over to the employee name to see the note.
In order to view the note on any employee of the Google Sheet, all we need to do is to bring our cursor on the entity (or node; namely employee name here). Hence we will be able to read the note as:
We can do the same for any employee. Let us check Stacy’s experience as:
But if we look at the top-order of hierarchy that is Alice, we will not get that note as:
Now, we notice that Alice was not included in First Column (Employee Column) and hence there is no note stored against it.
Read further to learn how to add a note to the top of hierarchy.
How to add note to the top of hierarchy in Organization Chart in Google Sheets
If we want to add a note to the top of hierarchy employee who works above all and under none, having no supervisor; we can follow the procedure below:
Step 1: Add a new row to the data table for top employee.
We can just add this row below (last of) data table or add a new row at start of data table.
Let us add a new row in the start of data table as:
(Step 1) Go to first employee. Select first employee cell as:
(Step 2) Right click over it. You will see a pop-up menu. Select “Insert 1 row above” as:
(Step 3) You will see a new row on first place as:
(Step 4) Correct the serial number as:
Step 2: Fill the new row for top employee “Alice”.
(Column 1): Employee name is “Alice”.
(Column 2): There is no supervisor for “Alice” as she is already on the top of hierarchy, so leave the column 2 empty.
(Column 3): Write the note for Alice for Alice as her number of years of experience.
This new row is filled up as:
Step 3: Add that new row to the organization chart.
When you add a new row to the top or bottom of data table for chart, Chart does not know if that row should be included to it. So, you need to add it to the range of chart by using Chart Editor as:
You can see the data range for chart is set as B4:D13 while it needed to be B3:D13 in order to include the new row, so we just change the data range to B3:D13 as:
Step 4: View the note on “Alice”.
We can now view the note on Alice by selecting the chart and pointing the cursor on Alice as:
Alice’s experience shows up as 25 years; no wonder, she is the top employee!
Finalizing the Organizational Chart Template in Google Sheets
In order to understand how to make an Organizational Chart Template, we should first understand the purpose of it. In this article, we learnt how to make an organization chart and why we make it. But what do we mean by “template” in this case. Let us learn more about it.
What is Organizational Chart Template?
Template refers to a “Sample” which can be used as a building block for making something. In this case, Organizational Chart Template refers to a Sample Organization Chart which may not have all the information about hierarchy in place but will be used later as a building block for a proper organization chart.
Suppose an example of a school, we know about basic hierarchy as:
- Principal who acts top employee.
- Head of Department’s (HoD’s) work directly under Principal.
- Teachers work under HoD’s.
- Students come under the supervision of teachers.
Although we may not know the names of number of departments, and so their HoD’s names, but we can create an Organizational Chart “Template” as below.
How to create an Organizational Chart Template
Let us follow the same example of school here.
Step 1: Firstly, create data table for the chart.
Here, the chart can be cooked up as:
Step 2: Make Organizational Chart using the same method as above.
Let us see a summary for steps for making an organization chart as:
Insert -> Chart -> Chart Editor -> Set up -> Chart Type -> Organization Chart
Following the same procedure, we can see organizational chart template as:
Although this chart does not represent any actual data rather a sample for what the data would look like when entered; this is what template means.
Download/Copy Organizational Chart Template in Google Sheets
- Any mistake in spelling of supervisor will lead the Google Sheet to assume that this supervisor is a new entity and hence it will be treated as a different entity. Example of this is shown in the method of “How to correct the Glitch due to Spellings in Organizational Charts in Google Sheets?” where “Cristopher” is written instead of “Christopher” which ends up making a separate organization chart for supervising “Cristopher”.
- Whenever any change in the data table is made, chart is automatically updated.
- Chart must be selected first in order to view the note on any employee. If it is not selected then the note would not show up.
- There is no need to include the name of top of hierarchy employee on first column of data table as it has no supervisor, but if you want to have a note for that employee then it should be included. Also, including it is a good practice to follow.
Can I make organizational chart without the proper format of data table as required for organizational charts in Google Sheets?
Yes, you can create organizational chart without putting data into proper format. But you will have to do that manually. You cannot use the automated feature of organization chart provided by Google Sheets without formatting the data into required form.
Can I delete a certain employee from Organization Chart in Google Sheets?
Yes, you can delete a certain employee from organization chart by deleting its row from data table. Alternatively, you can just empty the employee’s row in data table and use it for another employee when he/she joins in.
Can I add a new employee to the Organization Chart in Google Sheets?
Yes, you can add a new row for new employee in data table either by inserting it at top, bottom or between the data table rows. If you are adding it to the top or bottom make sure to change your Organization Chart data range accordingly as shown above.
How to open Chart Editor in Google Sheets?
Chart Editor automatically pops up when you create a new chart. You can also open it by double clicking the chart or choose Chart Editor from triple dot icon on top right corner of the chart.
Can I move the chart in Google Sheets?
Yes, you can move the chart anywhere in the sheet by using simple drag and drop operation.
Can I save the organizational chart in Google Sheets?
Yes, you can save the chart you just created by clicking the triple dot icon on top right corner of chart select “download” and further save it in your PC for offline use.
Can I change employees after making an organization chart in Google Sheets?
Yes, you just need to make changes to the data table. Google Sheets will automatically make changes to the organization chart. Make sure to set the data range correctly in case you add a new employee to the data table.
Google Sheets is an excellent online spreadsheet software which allows us many services and automated features for the ease of calculation and management of information. Organizational Chart is one of the tools provided by Google Sheets. You can use Organizational Chart tool for provided by Google Sheets to automatically make Organizational Charts with only a matter of clicks.
Any queries about the given topic and methods are most welcome to be entertained. Feel free to share your thoughts in the comment section below.
Thanks for Reading!