Gantt Chart from scratch
Gantt charts are world-renowned and offer immense capabilities as well as instant insight into work progress, which is why so many people want to unlock their full potential. If you are wondering how to create your own Gantt template, we invite you to read this guide. Building a custom solution is worthwhile because it gives you the ability to fully tailor it to your team's unique needs - something that ready-made tools like Asana or ClickUp often fail to provide or require high subscription fees.
Is it difficult to create such a chart?
That depends on your skills and what you want to include in the sheet. Below we have tried to show how to most easily create your own sheet step by step, using basic spreadsheet logic. If you know the basics of formulas in Google Sheets, you certainly will not have major trouble with this.
However, if you do not want to build everything from scratch, you can always use a ready-made, fully functional template available in our store.
What is a Gant Chart?
Before we move on to building our own chart, it is worth recalling what a Gantt chart actually is.
In the simplest terms, it is a visual project timeline. Instead of merely displaying a classic task list, it presents tasks along a calendar in the form of horizontal bars: the length of each bar shows how long a given task takes, and its position indicates the exact moment of its start and completion.
Why are templates so popular?
These charts owe their immense popularity primarily to the fact that they perfectly combine the visual part with the content of the tasks themselves. Since many people have trouble realistically estimating the time needed to achieve goals or with general project management, a Gantt chart significantly streamlines the entire process. One glance at the chart is enough to immediately know what exactly we are doing and when.
The availability of these charts in the form of Google Sheets has further contributed to improving work, as they allow multiple people to view the same project simultaneously.
Create a simple Gantt chart
Now that we know what a Gantt chart is and what it is used for, it is time to move on to building it. Below we present a step-by-step guide showing how to easily build your own chart in Google Sheets.
Step 1. Define what you need the chart for
Before you start filling in cells in the sheet, it is worth considering what exactly your Gantt chart is supposed to be used for. Thinking about this at the beginning will save you unnecessary formatting and a lot of time in the future. Consider whether you are planning projects typically related to work (e.g. marketing campaigns, implementations, customer service) or perhaps you are looking for a tool for more personal purposes (e.g. home renovation, study planning, or event organization).
Example for business projects: If you are creating a sheet for professional purposes, your columns should include, among others: task name, external and internal description of a given department, person responsible, start date, and end date, as well as status (e.g. to do, in progress, done), which will significantly facilitate later data filtering.
Prepare the workspace
Open a new Google Sheet or Excel workbook and name the first tab according to your needs, for example "Project" or "Gantt Chart" - this is where all data will be entered.
At the very top of the sheet (rows 1-4) create a summary box to serve as a control panel. Place the following information in it:
- Project name and project manager
- Project start date (this will act as the "anchor date" for formulas creating the timeline)
- Expected end date
- Today's date (use the formula =TODAY() so that the chart automatically indicates the current point in time)
The above information does not have to be located in the specific cells given in this guide. However, later in the text, we will refer precisely to these places in the sheet. If you decide to place them elsewhere, remember to adjust the cell addresses accordingly in the formulas introduced later.
Step 2. Create a dynamic calendar
We are now moving on to creating the visual part of the "timeline" - we will build a calendar on which tasks will automatically be highlighted.
- Define column widths: Select the columns intended for the calendar (e.g. from column P to V) and adjust their width to a uniform size, e.g. 30 pixels. This creates a compact, square layout that allows you to view several weeks at a glance.
- Select these columns and drag them to the right until you reach 365 or 366 columns, which will ensure that your Gantt chart remains dynamic for a full 12-month cycle.
- Days row (P7): In the first cell of the calendar, use a formula indicating the project start date. In the cell right next to it, type =P7+1 and drag it to the right, creating a continuous timeline. Format these cells to display only day numbers.
- Weekdays names: In the row below, you can use the formula =TEXT(P7, "ddd") to automatically display abbreviated day names (e.g. Mon, Tue, Wed) based on the date from the row above, or simply refer to the date (=P7) and change the cell formatting to the day name itself.
- Months name row: Above the days, group them into blocks of 7 cells. In the first cell (P6), type =P7 to indicate the start of the week, and format it to display the month name. In the next cell of the block (W6), type =P6+7 and drag this formula to the end of the timeline.
Tip: Keep column widths small - the goal is to get a readable dashboard view, not a standard spreadsheet. If at least 3 weeks of the project do not fit on one screen, it means you are looking at a regular list, not a timeline chart.
Step 3. Create a database
Before we move on to configuring the calendar itself, we need to create a database for our tasks. To this end, in row 8 (in the range from B8 to N8) we define columns that will allow us to precisely manage each element of the project:
- Column B (Status): contains an interactive checkbox for marking completed tasks.
- Column C (Label): field intended for labels with colors.
- Column D (Task): name of the specific task.
- Columns E-G (Description): task description.
- Column H (Project lead): person responsible for the project, selected from a previously prepared drop-down list.
- Column I (Priority): task priority level, also defined using a drop-down list.
- Column J (Start date): start date, entered via quick calendar picker.
- Column K (End date): end date, entered in the same way.
- Column L (Days): total number of days (difference between end date and start date).
- Column M (Working days): number of working days - for the first row you can use the formula: =IF(J9="","",NETWORKDAYS(J9,K9)).
- Column N (Progress): work progress entered in percentages.
Step 4. Color coding using conditional formatting
It is time to bring the chart to life and add colors. We will use a custom formula for this, which will check each field in our calendar and answer a simple question: "Does this date fall between the start and end date of this task?".
Define the range
Select the entire "empty" calendar grid (e.g. from cell P9 all the way to NK500, or as deep as your project list reaches). It is crucial that this area covers every possible task row and every day of the year.
Open formatting rules
In Google Sheets: go to Format > Conditional formatting.
In Excel: go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format.
Apply the custom formula
In the field intended for the custom formula, enter the following entry:
=AND(P$7>=$J9, P$7<=$K9,$C9="C1")
How this formula works:
- P$7>=$J9: Checks if the date in the calendar (P7) falls on or after your task start date (J9).
- P$7<=$K9: Checks if the date in the calendar (P7) falls before or on your task end date (column K).
- $C9="C1": This is your color filter. It tells the sheet to change color to a specified one (e.g. blue) only when the task in the category column is marked as "C1". (C1 does not refer here to the cell, but to the color name).
Note: The formula refers to specific rows and columns visible in the mentioned example. If your sheet looks slightly different, make sure if any of the references need adjustment.
Copy this formula for as many colors as you need, e.g. $C9="C2" etc.
Remember: if you change the color in the configuration tab, make sure to update the conditional formatting in the chart tab so that the colors display correctly.
Common mistakes when creating a Gantt chart
Building your own Gantt chart is a great way to have full control over a project, but it requires knowledge of tools like Excel or Google Sheets and spending time configuring formulas and conditional formatting. If you do not want to waste time and prefer to move straight to action, a great solution is to use ready-made, professional templates.
Regardless of whether you choose to create your own sheet or a ready-made solution, it is worth remembering the most common pitfalls:
- Data overload: Adding too many columns and overly complicating the configuration.
- Outdated data: A Gantt chart is only as good as its last update.
- Unrealistic schedules and omitting overlapping tasks.
Sources
Investopedia - Gantt Chart Definition
Microsoft Support - Conditional formatting formulas
