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.

Adding Extra Rows

Adding extra rows at the bottom of the table may be necessary from time to time and the process is straightforward. Here are the steps to take:

1. Change the number at the bottom to add the desired number of extra rows, then click the Add button.

   

2. A warning message may appear. You can tick this box and click ok to dismiss this for 5 minutes, while you make thee changes.

   

3. Click on the last row that has formatting applied to select the entire row. In my example, it is row 61, but you're may be different.

   

4. Scroll all the way to the right to see the end of the row. You will see a blue dot, hover your mouse over this until you see a cross-hair, then click and drag this down to the bottom of the sheet. This will copy formulas and formatting down to the newly added rows.

   

5. Just repeat this process to add more rows in the future, but bear in mind you may need to adjust the filter to include new rows. Read on to find out how to adjust the table filters.

Adjusting the Table Filters

When a filter is applied, this is a static adjustment that will not dynamically update based on newly added rows of data. Therefore, if your filter does not extend down to encompass all of your data, it's easy to adjust, just follow these steps.

1. You can see in the image below, the filter on this table only goes down to row 61. This is defined with the green line. Any data below row 61 will not be included in the filtered data.

   

2. Select row 5 from A5:J5 and click the filter button in the top ribbon to switch the filter off.

   

3. Once this is turned off, you can click this filter button again to switch it back on. It should now include any new rows you've recently added.

4. Just repeat this process if your data ever extends below the green line for the selected filter range.

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!