top of page

Weekly Gantt Chart Template Setup Instructions

Setup Instructions

Despite this being an Automatic Weekly Gantt Chart, this template is easy to use; to add tasks, all you need to do is follow the steps below.

Add Tasks

  1. Choose a colour from the Label column (B); this will set the colour of the mini progress bar found in column I and the primary Gantt Chart colour.

  2. Enter a Project or Department name in column C to associate related tasks with the same colour, or use this for your Task Title. 

  3. Enter a Task Description or Task Title, depending on your preference. 

  4. Add a person to whom the task relates (Assignee). You can type their name or use the Smart People Chips in Google Sheets. 

  5. Set a Start Date for the task

  6. Set an End Date for the task

Once this information has been added, you should see your chosen colour display in the main Gantt Chart for the set number of days. 

Days, Progress & Progress %

The Days column will calculate automatically (This counts working days only using the NETWORKDAYS function - Monday to Friday). The mini progress bar will also update as you set the Progress percentage (Column J) for a given task. This helps to visualise your progress across all tasks. 

The main Gantt Chart

The main Gantt Chart will automatically display the duration of tasks based on the start and end dates in your chosen colour by week number.

As you mark tasks as complete with the checkbox in column A, the Week number, Month, week beginning date and year along the top of the Gantt Chart will automatically update to hide past dates. You’ll never need to add new columns at the end, as the chart will constantly update as you progress through your tasks. You just need to ensure you check the tasks off in column A when they are completed. 

Completing Tasks

When you check tasks off, you can filter this table to hide those tasks by looking only for FALSE values (incomplete tasks). This will keep everything nice and tidy, but it’s optional; you can leave completed tasks displayed; they will just grey out when marked as completed. 

Filtering tasks in a Gantt Chart in Google Sheets

You will see this pop-up if you try to filter the table, but filtering this table won’t cause any harm, so you can click OK here. 

This warning has been added to prevent any accidental changes to particular cells and ranges, but filtering a table won't change any formulas or data; it simply hides or shows the data based on the chosen filter. 

Change colours

If you want to change the colour of the drop-down labels, mini progress bar chart and the main Gantt Chart, you’ll need to make a few changes to ensure everything works correctly. I would recommend leaving these colours to avoid errors, but I’ve detailed the steps below if this is something you wish to do. 

Mini Progress Bar Chart Colours

The mini progress bar chart uses named range references, which can be found in the settings tab (this is hidden by default to avoid any accidental changes) 

If you wish to change any of these colours, it’s best to leave column B alone in the Settings tab as these are associated with the conditional formatting of the main Gantt Chart; therefore, only change the Hex value in column C to something else. The formulas in column I of the Gantt Chart shouldn’t need to be adjusted if you do it this way. 

Drop-down Label Colours

Changing the drop-down labels requires editing each Background colour, as shown below. 

You can bring this edit window up as shown above by clicking on the edit pencil in any dropdown menu, as shown below. 

Gantt Chart Colours

The Gantt Chart colours use a custom formula that looks for the naming in column B of the Setting tab, so if you wish to change the colour of these, you will need to adjust column B of the Setting tab and the custom formula for each conditional formatting rule applied to the Gantt Chart area. 

Here is an example of the conditional formatting for the Blue colour. This formula looks for three conditions, but it’s the first one that matters if you want to change the colour.

=AND($B6="Blue", L$3 >= $F6, L$3 <= $G6)

You need to adjust this “Blue” to a name that matches the name found in column B of the settings tab for that given Hex value. 

Adjusting a custom formula in conditional formatting in Google Sheets

Adjusting the Date format

Most countries use the date format with Day / Month / Year (dd/mm/yyyy), such as 17/01/2024 for 17th January 2024, for example. This includes most European countries. The format - Month / Day / Year (m/d/yyyy) is unique to the United States and perhaps Canada. It’s possible to adjust the date format within this sheet by adjusting the locale settings. 

You can check the Spreadsheet settings for Locale by going to the File > Settings menu and choosing your Locale from the drop-down.

Any new dates that you add will follow the new locale settings. If you need to adjust existing dates within the sheet, you can set the date format by highlighting column F5:G60, then clicking the '123' menu and choosing the format Month/Day /Year or Day/Month/Year. 

Adjusting the date format in Google Sheets


That’s pretty much all you need to know. It’s a powerful and lightweight Gantt Chart to help you visualise projects over time for individuals and teams alike. 

There are one or two interesting formulas and functions within the sheet, so take some time to look at these, as this might inspire you with other sheets. 

Thank you again for your purchase and for supporting SpreadsheetWise. We really do appreciate your business. 

We would encourage you to check our full range of Google Sheets templates here, and be sure to check out our YouTube channel for tips and tricks in Google Sheets. 

bottom of page