Gantt Chart Template Setup Instructions
Setup Instructions
Despite this being an Automatic Daily Gantt Chart, this template is easy to use; to add tasks, all you need to do is follow the steps below.
Add Tasks
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.
Enter a Project or Department name in column C to associate related tasks with the same colour, or use this for your Task Title.
Enter a Task Description or Task Title, depending on your preference.
Add a person to whom the task relates (Assignee). You can type their name or use the Smart People Chips in Google Sheets.
Set a Start Date for the task
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.
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 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 last one that matters if you want to change the colour.
=AND(L$4>=$F5,L$4<=$G5,$B5="Blue")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 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 three 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.
2. You'll see this message appear. You can tick that and click OK to stop the warnings from showing while you make these changes.
3. Click on cell CR2 and drag down to CX60 to select everything. Then click on the blue dot and drag this to the right to copy another week over.
4. Click on the cell CK5 to CQ5 to highlight some blank cells
5. Click on the Paint Format tool to copy the format of those highlighted cells
6. Click and drag to cells that do not have this border formatting to copy that format to them.
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:
Change the number at the bottom to add the desired number of extra rows, then click the Add button.
A warning message may appear. You can tick this box and click ok to dismiss this for 5 minutes, while you make thee changes.
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.
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.
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.
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.
Select row 4 from A4:J4 and click the filter button in the top ribbon to switch the filter off.
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.
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.