How to Create a Gantt Chart in Google Sheets

Gantt Chart with Google Sheets icon
Lifewire

A popular tool for project management, Gantt charts provide a chronological, easy-to-read breakdown of completed, current and upcoming tasks as well as who they're assigned to along with start and end dates. This graphical representation of a schedule offers a high-level view of how much progress is being made and also highlights any potential dependencies.

Google Sheets provides the ability to create detailed Gantt charts right within your spreadsheet, even if you've had no past experience with their unique format. To get started, follow the instructions below.

01
of 03

Creating Your Project Schedule

Gantt project schedule in Google Sheets
Screenshot from Chrome OS

Before diving into Gantt chart creation, you first need to define your project tasks along with their corresponding dates in a simple table.

  1. Launch Google Sheets and open a new spreadsheet.
  2. Choose a suitable location near the top of your blank spreadsheet and type in the following heading names in the same row, each in their own column, as shown in the accompanying screenshot: Start Date, End Date, Task Name. To make things easier for yourself later in the tutorial you may want to utilize the same locations that we've used in our example (A1, B1, C1).
  3. Enter each of your project tasks along with their corresponding dates in the appropriate columns, using as many rows as necessary. They should be listed in order of occurrence (top to bottom = first to last) and the date format should be as follows: MM/DD/YYYY.
  4. Other formatting aspects of your table (borders, shading, alignment, font styling, etc.) are purely arbitrary in this case, as our main goal is to enter data that will be used by a Gantt chart later in the tutorial. It's completely up to you whether or not you'd like to make further modifications so that the table is more visually appealing. If you do, however, it is important that the data itself remain in the correct rows and columns.
02
of 03

Creating a Calculation Table

Create calculation table

Just inputting start and end dates isn't enough to render a Gantt chart, as its layout relies heavily on the actual amount of time that passes between those two important milestones. In order to handle this requirement you need to create another table that calculates this duration.

  1. Scroll down several rows from the initial table that we created above.
  2. Type in the following heading names in the same row, each in their own column, as shown in the accompanying screenshot: Task Name, Start Day, Total Duration.
  3. Copy the list of tasks from your first table into the Task Name column, ensuring that they are listed in the same order.
  4. Type the following formula into the Start Day column for your first task, replacing 'A' with the column letter which contains Start Date in your first table and '2' with the row number: =int(A2)-int($A$2). Hit the Enter or Return key when finished. The cell should now display the number zero.
  5. Select and copy the cell in which you just entered this formula, either using a keyboard shortcut or Edit -> Copy from the Google Sheets menu.
  6. Once the formula has been copied to the clipboard, select all of the remaining cells in the Start Day column and paste by using a keyboard shortcut or Edit -> Paste from the Google Sheets menu. If copied correctly, the Start Day value for each task should reflect the number of days from the beginning of the project that it is set to begin. You can validate that the Start Day formula in each row is correct by selecting its corresponding cell and ensuring that it is identical to the formula typed in step 4 with one notable exception, that the first value (int(xx)) matches the appropriate cell location in your first table.
  7. Next is the Total Duration column, which needs to be populated with another formula which is slightly more complicated than the previous one. Type the following into the Total Duration column for your first task, replacing cell location references with those corresponding to the first table in your actual spreadsheet (similar to what we did in step 4): =(int(B2)-int($A$2))-(int(A2)-int($A$2)). Hit the Enter or Return key when finished. If you have any issues determining the cell locations that correspond to your particular spreadsheet, the following formula key should help: (current task's end date - project start date) - (current task's start date - project start date).
  8. Select and copy the cell in which you just entered this formula, either using a keyboard shortcut or Edit -> Copy from the Google Sheets menu.
  9. Once the formula has been copied to the clipboard, select all of the remaining cells in the Total Duration column and paste by using a keyboard shortcut or Edit -> Paste from the Google Sheets menu. If copied correctly, the Total Duration value for each task should reflect the total number of days between its respective start and end dates.
03
of 03

Creating a Gantt Chart

Creating a gantt chart

Now that your tasks are in place, along with their corresponding dates and duration, it's time to create a Gantt chart.

  1. Select all cells within the calculation table, including the headers.
  2. Choose the Insert option in the Google Sheets menu, located towards the top of the screen directly under the worksheet title. When the drop-down menu appears, select Chart.
  3. A new chart will appear, titled Start Day and Total Duration. Select this chart and drag it so that its display is positioned below or side-by-side the tables that you created, as opposed to overlaying them.
  4. In addition to your new chart, the Chart Editor interface will also be visible on the right-hand side of your screen. Select Chart type, found towards the top of the DATA tab.
  5. Scroll down to the Bar section and choose the middle option, Stacked bar chart. You'll notice that the layout of your chart has changed.
  6. Select the CUSTOMIZE tab in the Chart Editor.
  7. Choose the Series section so that it collapses and displays available settings.
  8. In the Apply to drop-down, select Start Day.
  9. Click or tap the Color option and choose None.
  10. Your Gantt chart is now created, and you can view individual Start Day and Total Duration figures by hovering over their respective areas within the graph. You can also make any other modifications that you wish via the Chart Editor -- as well as through the tables that we created -- including dates, task names, title, color scheme and more. Right-clicking anywhere within the chart itself will also open the EDIT menu, which contains a number of customizable settings.