Ahh, the Gantt Chart. Is there anything so satisfying? If you’ve never encountered one, a Gantt Chart is a horizontal bar chart that visually represents a project plan over time. It’s super handy, but when we try to insert one into our workbook using the Insert tab, the Gantt Chart is nowhere to be found!
To work around this, all we’ll need to do is set up our own Gantt Chart by listing our project’s tasks and their respective start and end dates across the first three columns of our sheet, and by listing the dates we want to include in our chart overall across the top row of the table.
Once this foundation is set up, we can create the Gantt Chart using conditional formatting to highlight each cell that falls within the project start and end dates. To do this, select all of the cells in that area of the chart, open the conditional formatting dropdown on the Home tab, select new role, choose “use a formula to determine which cells to format” and enter =AND(D$2>=$B3,D$2<=$C3) to determine if the date lies within the range of the project dates.
With this magic formula, the only thing left we have to do is choose the fill color for our highlighted cells by selecting the format button and selecting our desired color. Once we’ve got our shade, we hit OK, and we have a beautiful Gantt Chart! The best part? This chart is dynamic, so if we update any of our start or end dates, our chart will automatically adjust.
