How to Make Organizational Chart Template in Google Sheets

How to make Organizational Chart Template in Google Sheets 15

In this article we will learn about how to make Organizational Chart Template in Google Sheets. A free downloadable Organizational Chart Template is also available at the end of the article.

What is Organizational Chart Template in Google Sheets?

Google Sheets provide many automated tools for daily spreadsheet problems. We can use those automated tools for the solution to our problems. One of such tools is Organization Charts tool provided for automatically making Organizational Charts in Google Sheets. It saves up a lot precious time of user that would otherwise be spent on finding the symbols and adding them up 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!

See also  How to Find & Highlight Duplicates in Google Sheets (5 Methods)

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:

How to make Organizational Chart Template in Google Sheets 1

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:

How to make Organizational Chart Template in Google Sheets 2

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.

See also  How to Use Conditional Formatting in Google Sheets [Complete Guide]

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:

How to make Organizational Chart Template in Google Sheets 3

As the data is in proper format, we can now make organization chart using this data table as discussed below.

See also  How to Use HLOOKUP Function in Google Sheets (User Guide)

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:

How to make Organizational Chart Template in Google Sheets 4

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:

How to make Organizational Chart Template in Google Sheets 5

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:

How to make Organizational Chart Template in Google Sheets 6

Method 2: Select Chart from Insert Menu.

Insert -> Chart

Select chart from insert menu as:

How to make Organizational Chart Template in Google Sheets 7

Either way, when you click chart, you can see the Chart Panel as well as Chart Editor popped up as:

How to make Organizational Chart Template in Google Sheets 8

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

How to make Organizational Chart Template in Google Sheets 9

Now, you can see the organization chart in Chart Panel as:

How to make Organizational Chart Template in Google Sheets 10

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.

See also  How to Add a Trendline in Google Sheets [Complete Guide]

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:

How to make Organizational Chart Template in Google Sheets 11

Now you can see the organization chart automatically updates as:

How to make Organizational Chart Template in Google Sheets 12

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.

See also  How to Make a Beautiful Chart in Google Sheets (User Guide)

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:

How to make Organizational Chart Template in Google Sheets 13

We can do the same for any employee. Let us check Stacy’s experience as:

How to make Organizational Chart Template in Google Sheets 14

But if we look at the top-order of hierarchy that is Alice, we will not get that note as:

How to make Organizational Chart Template in Google Sheets 15

Now, we notice that Alice was not included in First Column (Employee Column) and hence there is no note stored against it.

How to make Organizational Chart Template in Google Sheets 16

Read further to learn how to add a note to the top of hierarchy.

See also  How to use IFS Function in Google Sheets (With Examples)

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:

How to make Organizational Chart Template in Google Sheets 17

(Step 2) Right click over it. You will see a pop-up menu. Select “Insert 1 row above” as:

How to make Organizational Chart Template in Google Sheets 18

(Step 3) You will see a new row on first place as:

How to make Organizational Chart Template in Google Sheets 19

(Step 4) Correct the serial number as:

How to make Organizational Chart Template in Google Sheets 20

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:

 How to make Organizational Chart Template in Google Sheets 21

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:

How to make Organizational Chart Template in Google Sheets 22

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:

How to make Organizational Chart Template in Google Sheets 23

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:

How to make Organizational Chart Template in Google Sheets 24

Alice’s experience shows up as 25 years; no wonder, she is the top employee!

See also  How to Automatically Sort Dataset in Google Sheets

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:

How to make Organizational Chart Template in Google Sheets 25

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:

How to make Organizational Chart Template in Google Sheets 26

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.

See also  How to Show Formulas in Google Sheets [Complete Guide]

Download/Copy Organizational Chart Template in Google Sheets

Notes

  • 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.

See also  How to Underline in Google Sheets (3 Methods)

FAQ

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.

See also  Date Formatting in Google Sheets [Complete Guide]

Conclusion

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!

Content Protection by DMCA.com

Widad Khan

Hi, I am Widad Khan. I was born and raised in Pakistan. I have always loved the field of Computer Sciences and since I was a kid, I would say I wanna grow up to be a scientist, now this term is mostly related to "Computer Scientist" and not scientist alone. I am pursuing my Bachelor Degree in Computer Sciences from GCU, Lahore. I have always loved keeping records of different types of data. Earlier, I would use MS Excel for that. But with the advancements in Google Sheets, my work has become much easier. And also it's very easy to share and coordinate data with fellows and coordinates.

OfficeDemy.com
Logo
Enable registration in settings - general