You’ve probably heard of a Gantt chart, an essential tool for any professional working on a project. Gantt charts help visualize the timeline and progress of a project, making them particularly useful when communicating with stakeholders.
There are many Gantt chart software available for project management, but these tools often create complex schedules that are too confusing for executive reports or client reviews.
Thankfully, Google Sheets are easily editable and accessible from the cloud, making them useful for collaborating on projects. While there is no native Google application solution for project management, Google Sheets is perfectly suitable for tracking project schedules. To present these schedules to clients or management, you’ll need to learn how to create a Gantt chart.
This article will show you how to manually create a simple Gantt chart in Google Sheets. If you want to create more professional visualizations for high-level presentations, we’ll also show you how to instantly transform your data into a beautiful Gantt chart using a free online tool called Office Timeline Online.
How to Manually Create a Gantt Chart in Google Sheets
1. Organize the schedule data into a table.
Start by creating a Google spreadsheet for your project schedule by building a table. It should include the description of your tasks or phases as well as the start and end dates for each of them.
2. Add a task duration table.
Add a second table below. This table will be used to calculate the intervals in days (duration) between the start date and the end date. These intervals will be used to construct your Gantt chart. The 3 columns of this table will reflect the 3 columns of your original table. Set it up like this:
- The first column will be the description of your tasks copied exactly from your original table. In our new table, we called them “Tasks.” To copy them from your original project plan using a formula, enter “=” in the first cell of your new table, then click on the cell with the title of your project description in the original table. You can repeat this step or simply drag the corner of that cell downwards, and your formula will be copied for each task description.
We called our second column “Start from day.” Essentially, this column determines the day of the project where each of your tasks will start. So, the first task or the earliest one will obviously start on day 0. To calculate this column, use the following Google Sheets formula as shown below: =int(E5)-int($E$5), and in the cell below it, =int(E6)-int($E$5). This formula translates to: Start date of this task – Start date of the first task (in our example, cell B5). Refer to other Google Sheets formulas to choose the one that best suits your project data.
We called our third column “Task Duration (days).” In this column, Google Sheets calculates how many days each of your project tasks will last. In our example, the first task, “Procurement,” will last for 77 days. Calculate this for each of your tasks using the following Google Sheets formula, as shown below: =(int(F5)-int($E$5))-(int(E5)-int($E$5)), and in the cell below it, =(int(F6)-int($E$5))-(int(E6)-int($E$5)). This formula translates to: (End date of this task – Start date of the first task) – (Start date of this task – Start date of the first task) = Task Duration.
3. Insert a stacked bar chart.
Select all the data in your new table by clicking on the corner. Go to the “Insert” tab in the Google Sheets ribbon and select “Chart” from the dropdown menu. Google Sheets will automatically insert a stacked bar chart, which is exactly what we need here.
4. Modify the appearance of the chart bars.
Double-click on one of the “Start from day” bars in your chart (the blue bars, in our case). This will open the “Series” section in the “Chart Editor” menu. Open the dropdown menu under “Fill Opacity” by clicking the arrow next to “100%” and select “0%”.
5. Customize your Gantt chart.
Once you have created your chart, you can customize it to your liking. For example, you can rename the chart, change or add horizontal and vertical axis titles, and make other customizations by right-clicking anywhere on the chart and accessing the chart editor. To change the color of the bars, double-click on one of the “Task Duration (days)” bars (the red bars, in our case). This will open the “Series” section in the “Chart Editor” menu. Open the dropdown menu under “Fill Color” by clicking the arrow next to “Automatic” and select the color you want to use.
And here’s what our Gantt chart looks like in Google Sheets after a few customizations (we removed the chart title, added a horizontal axis title, and changed the vertical axis title):
Now that you know how to create a Gantt chart in Google Sheets, you can easily track and present your project schedules in a visually appealing way. Don’t forget to use Office Timeline Online to create professional visuals for your high-level presentations.
To learn more about creating Gantt charts in Google Sheets and other exciting topics related to marketing and project management, visit Crawlan.com.
