How to create an auto-updating Excel Gantt Chart using formulas (Single Day Columns)
- Jan 8
- 3 min read
Updated: Jan 15
In this article I'm going to show you how to create a simple Gantt Chart in Microsoft Excel using excel formulas that will update when you change the start and finish date. In this example I'm using single days for each column but in other articles linked below I will show you how to use formulas for week-based columns.
Where will I use this?
At this point the usual question is “why don't I just use Microsoft project?” or “Why do I need to use formulas I can just colour in the Excel cells myself?”
The answer is, you can absolutely do both but after working in project management for 30 years there have been hundreds of times I needed a really simple Excel schedule that would auto-update in a planning workshop or I had some date based data from a external system that I wanted to display in a simple Gantt chart to make it easier for my customer to consume. The formulas can look complicated but once you understand how, you'll be at apply them easily for this and many other purposes.
Creating the Gantt Layout
In the linked video I will walk through the creation of this worksheet which will you find in the example spreadsheet also available for download.
The most important thing with date calculations is to ensure that Excel recognises the date fields as dates and not text. You can confirm this by changing the date format to a long date format (e.g. Monday, 6th of January 2025) and confirm it is correct before changing it back to your desired date format. I prefer to use the medium date format (e.g. 6-Jan-25) so there is no confusion between the US date format of Month, Day, and then Year (e.g. 01-06-25 or, “mm-dd-yy”) as opposed to the date format used in most other countries of Day, Month, and then Year (e.g. 06-01-25 or, “dd-mm-yy”).
Creating the Gantt Bars
Creating the Gantt Chart Bars that displays the task timing on the axis between the start and finish date and the calendar date is a two-step process.
Firstly, will use an Excel formula to display an “X” character in the cells where the formula determines a True result.
Secondly, we will use conditional formatting across the Gantt chart area to change the “X” characters into a blue format fill with the same colour used for the font, so it displays as a solid blue cell.
The axis cell formula is constructed as follows.
IF the [Column Date] (displayed in row 4) is equal to, or greater than, the [Start Date] AND is greater than, or equal to, the [Finish Date] then insert the “X” value in that cell. IF this formula is found not to be true then a blank value (“”) is inserted in that cell.
For cell G5 in the sheet displayed below for formula is [ =IF(AND(G$4>=$E5,G$4<=$F5),"X","") ]
Once you copy that formula across the rest of the Gantt Chart area and then apply the conditional format as per the screenshot below you should have a simple Gantt Chart that will auto-update when the start and finish dates are changed.
I hope this helps, feel free to ask questions in the comments and I will do my best to answer them as soon as possible.
Download the example Excel File here