How to make dynamic Gantt chart using Conditional Formatting in excel

To build a dynamic Gantt chart, you can use Conditional Formatting

1- First create a simple excel File with some activities and dates that you want to make gantt chart to

2 - Highlight the area you want to make gantt chart in

3 - From conditional formatting , choose new rule

4 - from new rule , choose Use a formula to determine which cells to format

now we need to enter a formula that checks to see if the day in row 1 ( Calendar row ) above the selected cells falls within the ranges of days in columns c & d (start & Finish ).

5-we will use the following formula

=And(E$1>=$C2;E$1<=$D2)

The formula is based on the AND function, with two conditions. The first conditions checks if the calendar header (row 1) column date is greater than or equal to the start date:

E$1>=$C2

( $ before row number to fix calendar row , $ Before C to fix start date column )

The second condition checks that the column date is less than or equal to the Finish date:

E$1<=$D2

( $ before row number to fix calendar row , $ Before D to fix Finish date column )

6 - Choose format , From fill choose the color you want for the Gantt chart

7 - click ok , now you have a dynamic bar chart