How-tos

How to Create a Gantt Chart in Google Sheets


Sheets Header

A Gantt chart is a commonly used type of bar chart that illustrates the breakdown of a project’s schedule into tasks or events displayed against time. Google Sheets has a handy feature to help you create a Gantt chart for your project.

Fire up Google Sheets and open a new spreadsheet.

First, create a small table and insert a few headings into the cells to get started. You’ll need one for tasks, start date, and end date. Fill in each cell with the project’s details. It should look like this:

Create a table with three headings: Tasks, Start Date, and End Date. Fill it out with your project's data.

Next, make a similar table off to the side or underneath the previous one that will serve as a way to calculate the graphs in each portion of the Gantt chart. The table will have three headings to generate the Gantt chart: tasks, start day, and duration (in days) of the task. It should look like this:

Make another table underneath the previous one with these three headings: Tasks, Start on Day, and Duration.

After you get the headings in place, you need to calculate the start day and duration. The “Tasks” heading will be the same as above. You can simply copy the cells underneath, directly reference them, or re-write them if you want.

To calculate “Start on Day,” you need to find the difference between each task’s start date and the start date of the first task. To do this, you first convert each date into an integer and then subtract it from the first task’s start date: ( <TaskStart> - <FirstTaskStart> ). It will look like this:

=INT(B4)-INT($B$4)

Type in the following formula and press Enter.

In the formula, the <FirstTaskStart> is always going to be an absolute value. Google Sheets uses the dollar sign ($) character to “lock” a row or column—or, in our case, both—when referencing a value.

So, when we copy the same formula for subsequent cells—which we do in the next step—using the dollar sign like this makes sure it always references that value in B4, which is the start of the first task.

After you press the “Enter” key, click on the cell again and then double-click the little blue square.

Click on the cell you previously entered the formula into, and then double-click the little blue square to apply the same formula to the subsequent cells in the column.

Like magic, Sheets will use the same formula—but making sure to reference the correct cell above—for the cells directly underneath, completing the sequence.

Voila! The formula calculates the rest of the cells in that column.

Now, to calculate the duration, you need to determine how long each task is going to take. This calculation is a little bit more tricky and finds the difference between a few more variables. The formula will resemble the format (<CurrentTaskEndDate>-<FirstTaskStartDate>)-(<CurrentTaskStartDate>-<FirstTaskStartDate>) and will look like this:

=(INT(C4)-INT($B$4))-(INT(B4)-INT($B$4))

To calculate the number of days that each task is projected to take, type in the following formula and press the Enter key.

As before, you must convert each date format into an integer as you reference it in the formula. Also, variables that will stay the same through all of the cells are referenced using the dollar sign characters.

After you press the “Enter” key, click on the cell again and then double-click the little blue square.

Just like before, click on the previous cell, and then double-click the little blue square to apply the formula to the remaining cells in the column.

Just like that, Sheets fills in the remaining cells for you.

Just like magic, the cells are filled with the duration of days each task will take to complete.

Highlight the entirety of the table.

Highlight the entire second table.

Next, click Insert > Chart.

Click Insert > Chart.

From the Chart Editor pane on the right of the window, click the drop-down box under “Chart Type,” scroll down, and click on “Stacked Bar Chart.”

From the Chart editor pane, click the dropdown box for chart type, scroll down to the heading Bar, and then choose "Stacked Bar Graph."

Finally, click on any of the light red bars, click on the color selector, and then choose “None” from the top of the color selector.

Click on any of the light red sections of the graph, click on the color picker from the chart editor pane, and then choose "None" from color selector.

After, head on over to the “Customize” tab in the Chart Editor pane, click on “Chart & Axis Titles,” and give your chart a name.

Next, give your Gantt chart a title by clicking on the Customise tab, Chart & axis titles, and then type it into the "Title text" box provided.

There you go. With that, you’ve created a fully-functional Gannt chart that updates in real time.

And there you have it. A beautifully made Gantt chart.





READ SOURCE

This website uses cookies. By continuing to use this site, you accept our use of cookies.