Gantt Chart is an essential part of project scheduling. A basic free Gantt chart is always within your reach. I am talking about Google Sheets and its Sparkline function. You can create a free Gantt Chart using SPARKLINE in Google Sheets.

There is a clear benefit of creating a Gantt Chart in Google Sheets. First of all, it’s free. But most importantly you can share it with your team anywhere.

You can adopt three different methods to create a Gantt Chart in Google Sheets. What are they?

1. Gantt Chart using the Conditional Formatting.

2. Gantt Chart using the Bar Chart.

3. Finally, the Gantt Chart using the SPARKLINE function.

This tutorial is about the third one, i.e. how to create a Gantt Chart Using Sparkline in Google Sheets. I think it’s best among the three.

As a side note, if you are looking for a professional Gantt Chart, you should look for project management solutions like Wrike. For other Charts, please see my Chart section.

How to Create a Gantt Chart Using Sparkline in Google Sheets

I have two formulas for this. One to draw the Task Bar (horizontal line) and the second to get the week numbers along the Task Bar.

Also, I’ve used the row grouping feature to expand and collapse subtasks.

Steps to Create a Gantt Chart Using Sparkline in Google Sheets

In every project, there must be a project start date and end date. The tasks involved are distributed based on these two dates. There may be main tasks and subtasks.

Here is my chart: Gantt Chart Template

Here is the sample data:

Points to Note:

1. Cell B2 contains the project start date and Cell C2 contains the project end date. In Cell D2 you can see the total project duration that is 184 days. This is the basics of the Gantt Chart.

The tasks are distributed in rows 3 to 11 keeping this project duration period in mind. In D2 I’ve calculated the number of days using the DAYS date function as below.

=days(C2,B2)

But instead of the above formula, I’ve used the following Array Formula in D2. The reason it’s expanding and that can calculate the tasks and subtasks duration in each row. So use the below DAYS function in D2, not the above non-array one.

=ArrayFormula(if(len(B2:B),days(C2:C,B2:B),))

2. In the sample data, please take note of rows # 4 (Task 2) and 7 (Task 3) which have subtasks. The subtask’s start date and end date should fall within its main task’s start and end date.

Leaving the function DAYS aside, actually, we only want to use two formulas to create a Gantt Chart using Sparkline in Google Sheets.

Key Formulas in Google Sheets Sparkline Based Gantt Chart

Key Formula 1 in E2:

=ArrayFormula(TRANSPOSE(row(indirect("A1:A"&int(datedif(B2,C2,"D")/7)))))

This formula puts the number of weeks involved in the project in row E2: 2. No need to drag the formula right as it will expand itself.

Must Check: Google Sheets Function Guide

You should reduce the column width from column E onwards to fit the Taskbar in one screen.

Key Formula 2 in E3:

=sparkline({B3-$B$2,C3-B3},{"charttype","bar";"color1","white";"color2","blue";"max",$D$2})

Enter this formula in cell E3 and then drag to down up to cell E11. Then you should merge the columns in each row as below.

Our Gantt Chart using the SPARKLINE formula is almost ready. Now let’s move to the customization part.

Steps to Customize the Gantt Chart that Created Using Google Sheets Sparkline Function

I am opting for only two basic customizations here. What are they?

1. Changing the Taskbar color of the subtasks.

2. Subtasks grouping.

How to Change the Taskbar Color of the Subtasks?

As you can see the subtasks are in rows 5, 6, and 8 to 11.

You only need to change the Color2 option in the formulas in those rows. To get light blue I have used the hex code “#b1aef9”.

This is the modified formula in row # 5 (cell E5)

=sparkline({B5-$B$2,C5-B5},{"charttype","bar";"color1","white";"color2","#b1aef9";"max",$D$2})

Similarly, change the color code in the formula in other cells where there are subtasks. Just copy this formula and paste it in the corresponding cells would do the trick. That’s all that you want to do.

How to Group Subtasks in Gantt Chart in Google Sheets

Grouping is pretty easy.

Select row 5 and 6 first, right-click on the row number, and from the shortcut menu select “group rows 5-6”.

Similarly, select rows 8 to 11, right-click, and group the rows, and voila! You have created a smart-looking Gantt Chart in Google Sheets!

Actually, the above Gantt Chart shows only the scheduled tasks. If you want, you can easily include the progress of your tasks.

The above horizontal bars (Gantt Chart) are our schedule. Now record the progress of the tasks. So that we can compare the actual schedule vs actual completion in one Gantt Chart. See the tips below.

You May Also Like: Create a Gantt Chart Using Wrike Online Project Management Software

How to Show Progress of the Tasks in Gantt Chart in Google Sheets

The above Gantt Chart using Sparkline in Google Sheets shows the tasks (schedule) to complete only. But you can record your job/project completion in that chart as below.

In this, the series of blue and light blue bars/horizontal lines shows the planned task in a certain period and the red/light red bars show the amount of work done/completed in that period.

How can I Add Progress Bar As Above in the Gantt Chart?

It’s simple. Here are the steps.

Steps to Add Actual Job Completion Against Scheduled Completion in Gantt Chart

1. Insert a new column between the column A and C. It’s optional as it’s only to show the text “Sch/Actual”. Please refer to the above screenshot.

2. Just insert new rows between your tasks and subtasks and enter your actual work start and end dates.

The formulas are the same. Just copy/drag the formula in cell F2 (yes! not E2 as we have inserted a new column so the formula column is now F2) to down.

3. Change the color of the series “Actual” to Red in the formula. I have used Red for main tasks and hex code “#FC8E5E” for subtasks.

4. Change the date in cell C2 to the last completion date. It would be your actual last task completion date in cell D12. This would extend the week numbers in row # 2 automatically.

5. Merge the columns that contain the bars based on the new week numbers. That’s all.

Now see this screenshot where I’ve hidden the subtasks.

The blue lines/bars shows our schedule and the red lines/bars show our actual completion (how long we took to complete the tasks). From this, you can clearly visualize that the project didn’t complete within the scheduled period.

Follow the above steps to create a Gantt Chart using Sparkline in Google Sheets. Now for those who want to know the logic of the SPARKLINE formula that I’ve used.

How to Use SPARKLINE formula to Plot a Gantt Chart

I think I should explain it with an image.

We have two series (two bars) in this chart. They are the start date and the end date. See the formula on the screenshot.

In that, the “color1” should be set to “white”. So that the first bar won’t be visible. In this, cell C is the horizontal axis. As per the current data in A1 and B1 the default max value of the cell is set to 72(22+50=72).

That means the above formula is equal to;

=SPARKLINE(A1:B1,{"charttype","bar";"color1","red";"color2","blue";"max",72})

If you change this max value to 100 or some other value that is greater than 72, you can see additional space on the right side of the bar.

In our original formula, this max value is our total project duration. I mean the total number of days between the project start date and end date in cell D2 in the first example (only schedule) and in cell E2 in the second example (schedule and actual). Our sample project is 184 days long.

In my sample project (first example), I’ve used this formula in E2 (Task 1) then dragged it down.

=sparkline({B3-$B$2,C3-B3},{"charttype","bar";"color1","white";"color2","blue";"max",$D$2})

To make you understand this formula, see the portion of the data taken from my sample project below. Please pay special attention to understand this part.

Project Start Date: 15-May-18 (Cell B2)

Project Finish Date: 15-Nov-18 (Cell C2)

Total Project Duration: 184 days (Cell D2)

Task 1 Start Date: 15-May-18 (Cell B3)

Task 1 End Date: 14-Jun-18 (Cell C3)

{Task 1 Start Date-Project Start Date, Task 1 End Date-Task 1 Start Date}

This generic formula is equal to;

{0,30}

This 0 will be get increased in each row (E3, E4, etc.) depending on the task’s (sub-tasks) start date.

So the white bar gets increased in each line. Also, there will be variations in the second bar too that represents the task duration. That’s all.

Conclusion

Follow the above steps carefully to create a Gantt Chart Using Sparkline in Google Sheets. If you face any issues you can ask me in the comments. Further, I’ve already shared my sheet with you. You can refer to that to sort out your chart-related issues if any.