How to plot a Gantt Chart using Google Sheet

Steps to plot a Gantt Chart using Google Sheet

Credits: https://www.youtube.com/watch?v=un8j6QqpYa0

This YouTube video is for Microsoft Excel, but I felt if you use Microsoft Office you will need an ofc365 subscription for full feature usage. The Google Sheet alternatively brings in most of the features of office 365 at no cost though but you can collaborate with others.

1. Populate raw data

To allocate work dates for effort:

=if(networkdays(E9,E9+C9)=C9,E9+C9,E9+(C9-networkdays(E9,E9+C9)+C9))

To allocate nonwork dates for effort:

=if(weekday(E9+(round(C9/2)*7))=0,E9+(round(C9/2)*7),E9+1+(round(C9/2)*7)-weekday(E9+(round(C9/2)*7)))

2. Populate the timeline info

3. Create named range for display_week

4. Relating the timelines for better tracking

=($D$2+(Display_Week-1)*7)

5. Basic formatting

a. Display week border

b. Frame borders for date field

c. Frame shades for date field

c. Format date fields

b. Align milestones

6. Custom formatting 1 (gannt view)

=AND(G$5>=$E7,G$5<=$F7)

7. Custom formatting 2 (progress bar)

8. Custom formatting 3 (gannt chart update)

=1*AND(G$5>=$E7,G$5<=$E7+($D7*($F7-$E7+1))-1)

9. reorder conditional formatting 3 and 1

10. Printing

YouTube vide of the above content: