In this tutorial, you will learn how to make a gantt chart in Google Sheets.
Gantt Charts are a popular tool to visualize project schedules in many industries.Google Sheets doesn’t have a built-in Gantt chart feature, but it’s possible to set up a Gantt Chart in Google Sheets relatively quickly. Just follow the steps below
The first step to creating a Gantt chart is to set up the data columns to input information about each task
Add the following headers to the first 4 columns: Task, Start Date, Duration, End Date
The first 3 columns will be input fields, while the End Date will calculate based on the Start Date and Duration. Enter the following formula in the first cell of the End Date column and drag down: “=if(ISDATE(B2), B2+C2-1, “-“)”. This formula checks if the start date in column B is a date and, if it is, calculates the end date by adding the duration in C2 and subtracting one. Drag this formula down as far as needed
Add some data to columns A through C to help in the following steps
Once you’ve set up the columns to record task data, it’s time to generate the bar chart portion of the Gantt Chart. We’ll first add date headers across the top, then conditional formatting to generate the bars
Type the project start date in the header column immediately following the End Date header, using a compact date format such as “Oct 4”
In the next column, type the following formula to generate the following date: “=E1+1”
Drag the formula cross the header row to populate as many dates as you need
Select the date columns and right click, then choose Resize Columns. Enter 25 in the popup that appears and click Ok
Open the Format menu, hover over Text Rotation, then Angle and select 75 to rotate the text so it fits in narrow columns
To add the Gantt Chart conditional formatting, select the date columns and open the Format menu, then choose Conditional Formatting
In the Conditional Format Rules sidebar, under Format Cells If, select Custom Formula Is
In the Value or Formula field, type: “=AND(ISDATE($B1), E$1>=$B1, E$1<=$D1)”. This formula evaluates each cell in the selected range by checking if 3 conditions are correct::
- Is the value in column B for this row a date?
- Is the header date for this column greater than or equal to the start date in column B?
- Is the header date for this column less than or equal to the end date in column D?
If all 3 conditions are met, the cell will be highlighted
Click Done to save the rule. Your Gantt chart is now ready to use. Update the start dates and duration and the colored bars will reflect the changes
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to make a gantt chart in Google Sheets. Want more? Check out all the Google Sheets Tutorials.