Are you fed up with searching through endless spreadsheets and disorganized task lists to manage your projects?
Look no further than customized and interactive Gantt charts built with Google Sheets. With just a few easy steps, you can simplify project management and boost team efficiency.
In this article, we’ll guide you through creating personalized Gantt charts that allow for tracking progress, setting deadlines, and staying on top of the project timeline.
Don’t let disorganized data hold you back – let’s get started on creating a Gantt chart that works for YOU!
What is a Gantt chart?
If you’re new to project management or have never used a Gantt chart before, let’s start from the beginning.
Best Charts, Graphs, and Diagram Tools
- 10 Best Online Chart Maker of 2023
- 10 Best Microsoft Visio Alternatives 2023
- 10 Best Org Chart Maker of 2023
- 10 Best UML Diagram Tools 2023
- 10 Best Entity Relationship Diagram (ERD) Tools 2023
A Gantt chart is a visual representation of a project’s timeline that helps manage tasks and monitor progress. Each bar represents a task, with its length indicating its duration; these bars are organized chronologically along a horizontal axis.
A Gantt chart is an invaluable tool, allowing you to quickly identify dependencies between tasks and anticipate overlaps or delays. You can use it for setting realistic deadlines, assigning tasks to team members, and keeping everyone on the same page.
- You may like: Best Gantt Chart makers
Furthermore, with a Gantt chart, you’re able to quickly identify your project’s critical path – i.e., those tasks which must be completed in a specific order for it to succeed.
Steps to make Gantt chart in Google Sheets
Follow the below steps to quickly create a Gantt chart in Google Sheets.
Open the Google Sheets and input the project data into Sheet
To start, you need to create two tables in the sheet. The first table you create will work as a template for the calculations of the second table.
1. Create the following columns in the first table:
- Column A: Tasks
- Column B: Start Date
- Column C: Duration
Then, create the cells:
- Cell A2: Task No. 1
- Cell A3: Task No. 2
- Cell A4: Task No. 3
Add new rows for each project task you want to appear on the Gantt chart,
For example, if the project has seven tasks, the table should contain seven rows along with the header row.
2. Add start and end dates for each task.
3. Create the second table below the first one and label it as follows. Here, we’re starting the second table from row 9.
- Cell A9: Tasks
- Cell A10: Start on Day
- Cell A11: Duration
The tasks in the “Tasks” columns in the second table should be exactly the same as those in the first table. Just copy and paste this information to avoid any typing errors.
4. After that, we have to determine the starting day of each task to ensure that the bars on the chart show the correct start date and duration.
To find out the “Start on Day” value, we calculate the difference between the start date of the first task from the start date of each task.
To do this, enter the below formula in cell B10:
=int(B2)-int($B$2)
Press “Enter” on your keyboard, and the value will automatically update in the cell.
5. We need to do this for all the tasks in the column “Start on Day.” To apply the same formula in each cell, click cell B10 and click and drag the small blue dot in the bottom right corner of the cell till the last project task.
6. Now, we’ll calculate the “Duration”of each task. In cell C10, input the below formula:
Best Charts, Graphs, and Diagram Tools
- 10 Best Online Chart Maker of 2023
- 10 Best Microsoft Visio Alternatives 2023
- 10 Best Org Chart Maker of 2023
- 10 Best UML Diagram Tools 2023
- 10 Best Entity Relationship Diagram (ERD) Tools 2023
=(INT(C2)-INT($B$2))-(INT(B2)-INT($B$2))
Press the “Enter” key on your keyboard to get the value in the cell.
In your sheet, cell numbers might differ depending on the project data. The rest of the things will remain the same.
7. Click on cell C10 and click and drag the small blue dot appearing in the bottom-right corner of the cell till the last project task.
Create a stacked bar graph
- Select the second table.
- Click “Insert” on the menu, then click “Chart.”
A stacked bar chart will appear on the sheet.
Turning the stacked bar chart into a Gantt chart
- Click on the “Start on Day” bar in the chart. This will highlight all the “Start on Day” bars.
- Configuring the chart.
In the “Chart editor” panel on the right side, click on the “Customize” tab. Click “Series,” then select “Start on Day” from the drop down menu. Click the “fill opacity” button and select “0%.”.
The chart will now look something like this:
Customizing Gantt chart in Google Sheets
Customizing any element of the Gantt chart, from title to bar style, everything is easy. Follow the below instructions to create 3D bars, remove the legends, change the colors of fonts, and more.
Update the Gantt chart title
To change the title of the project, double-click on the title located at the top of the chart and enter a new title.
Customizing the Gantt chart area
Below steps will guide you to adjust the border color and make the bar 3 dimensional.
1. Navigate to the “Chart editor” menu on the right and click on the “Customize” tab.
2. To change the font of the text on the chart, click on the “Font” drop down menu and choose the font.
3. If you want a colored border for your chart, you can choose colors from the “Chart border color” menu.
4. Click the 3D checkbox to add the visual effect to the bars.
Removing the Legend from the Gantt chart
- Click on the chart and click “Customize” tab in the Chart editor panel.
- In the “Legend” drop down menu, click “Position” menu and select “None.”
How to handle Gantt chart with dependencies?
While working on complex projects, it’s possible to have multiple tasks running that are dependent on each other. In this case, it’s essential to ensure that a task is initiated only when its previous task is complete.
Below given is the step you can follow to make sure a dependent task does not start until the prior one is completed.
Setting up dependent tasks to occur after completion of previous tasks.
- Identify the dependent task and the task it depends on.
- In the first table, click on the cell of dependent task in “Start Date” column, and put this formula: =max(B2)+1
The value in this formula (“B2” in this case) is the task that needs to be completed prior to the dependent task.
How to export a Gantt chart in Google Sheets to Excel
Some of your team members may prefer to work in Microsoft Excel. Therefore, you can export the Gantt chart and all the relevant data into Excel from Google Sheets.
- Click on “File” and click on “Download.”
- In the “Download” drop down menu, click on Microsoft Excel (.xlsx).
This will automatically create an excel file.
Google Sheets Gantt chart FAQs
Do Google Sheets have an inbuilt Gantt chart template?
Unfortunately, Google Sheets doesn’t offer a built-in Gantt chart template. You’ll need to create it manually, and if you want to use it in future projects, save it as a template.
How to format my Gantt chart by week?
To format your Gantt chart by week, Click on the “Customize” tab and click on the “ Gridlines and ticks” menu. Enter “7” in the “Major step” field.
Wrapping up
Gantt charts are an integral tool for project management that can help you stay organized, meet deadlines, and ultimately meet your project objectives.
Creating customized and interactive Gantt charts using Google Sheets is a straightforward yet efficient way to streamline your project management and boost team efficiency.
With just a few simple steps given in this article, you can quickly create a personalized chart that tracks progress, sets milestones, and ensures timeline adherence.
It doesn’t matter if you’re an experienced or novice project manager; creating Gantt charts in Google Sheets simplifies tasks while increasing team productivity.