Dynamic Gantt Chart Template Setup Instructions

Setup Instructions

Thanks for downloading this Automatic Gantt Chart Template Spreadsheet for Days and Weeks. Follow the steps below to get everything set up.

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.

   1. If you wish, you can rename these labels to categories or project name - more on this later. 

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

   1. This column can be converted to a dropdown if required - more on this later.

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

4. Add a person to whom the task relates (Assignee). 

   1. You can type their name, use the Smart People Chips in Google Sheets, or this column can be converted to a dropdown if required - more on this later.

5. Set a Start Date for the task

   1. Double-click to show the date picker

6. Set an End Date for the task

   1. Double-click to show the date picker

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

Viewing Options

You can choose to view the Gantt Chart by Days or Weeks by selecting the option from the dropdown menu in cell I2. Depending on your preference, the main Gantt Chart will adjust to display your tasks broken out by days or weeks

When ‘Days’ is selected, you’ll see the date, weekday, month and year. You can also choose to show the days of the week as Monday to Friday or Monday to Sunday. 

When ‘Weeks’ is chosen, you’ll see the date for the start of the week along with the week number, month and year. 

The Gantt Chart will automatically adjust, so this is a great tool for visualising your tasks over time. 

Days, Progress & Progress %

The Days column (Column H) will automatically calculate (This counts working days if Monday to Friday is selected or All days if Monday to Sunday is selected). The mini progress bar will also update as the task progresses through time (Column I and J) for a given task. This helps to visualise your progress across all tasks. This is based on the time between the start and end date. 

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. 

As you mark tasks as complete with the checkbox in column A, the Week number, Month, Year, day of the week and dates along the top of the Gantt Chart will automatically update to hide past dates when tasks are completed and show new dates at the end of the chart. 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.

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 these colours to a name relevant to you, such as departments, categories or projects, you can do this here, but the sheet is only set up to accept ten labels. These ten cells (B3:B12) are associated with the conditional formatting of the main Gantt Chart and mini progress chart in column I on the main Gantt Chart sheet. If you wish to add more than ten, then the formulas and conditional formatting will need to be adjusted to reflect that; therefore, I do not recommend adding more than ten, unless you know how to make these adjustments yourself. 

If you wish to change the colour of the mini progress bar chart and the main Gantt Chart, you can adjust the Hex value in column C of the settings sheet for the mini progress bar chart and the conditional formatting colours for the main Gantt Chart. 

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. 

Converting a column into dropdown options

If you wish to convert the entries in column C or E into dropdown menus just select the data from cell C6:C100 or E6:E100 and right-click to bring up the contextual menu. Next choose Drop-down; this will bring up a sidebar showing all your options, just click ‘Done’ to save. If you want to add more later, you can always come back to edit this and add another item to the list. 

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. 

Adding extra days

I find it's never really beneficial to see too far into the future, as you can only really affect days or weeks, so visualising six months ahead seems plenty, in my experience; however, this can be adjusted if necessary; read on if this is something you would like to do. 

It should be noted that the benefit of this sheet is that as you complete tasks, you can hide them using filters, as shown in this video. Hiding completed tasks from the table automatically adjusts the dates along the top, which would hide older dates and show newer dates at the end; consequently, the Gantt Chart constantly adjusts to the work being completed. This means you should never need to add more columns at the end.

If you would like to add more columns to the end of the sheet to visualise a longer period of time, you can follow these steps below:

• Select the last 7 columns, then right-click to bring up the contextual menu and choose Insert 7 columns to the right.

• You'll see this message appear. You can tick that and click OK to stop the warnings from showing while you make these changes.

• Click on cell GC2 and drag down to GI100 to select everything. Then click on the blue dot and drag this to the right to copy another week over.

You will need to repeat this process until you have all the weeks you need. The Conditional formatting should carry across to these new columns if you follow this process. Once you have added a few weeks, you can select three or four weeks in one go and repeat the process but with a larger range of cells.

Conclusion

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. 

If you like this template, please consider leaving me a review, it really helps. 

Thanks!