Excel Gantt Chart with milestones and moving timeline, using standard or Dynamic Array formula
My longest video to date!
If you need to distribute an Excel-based Gantt chart within your project team, consider creating one more useful than some of the basic templates available. This video shows you how to include:
Two-colour task bars indicating the percentage complete/incomplete of each task
In-cell data bars for the percentage complete column, so you can visualise in one column the progress on individual tasks
Milestone markers
Highlighting for today's date, to see if tasks are behind or ahead of schedule
Highlighting for weekend dates
A moving timeline, to shift the date range shown in the grid as the project progresses
The template chart is first created with standard (traditional) per-cell formula. I then go through how the same result can be achieved by amending formula to work with Dynamic Arrays, reducing the model to only a few formula per sheet.
A link to the finished workbook is provided below if you need the template for an upcoming project, or just want to check out the formula and how the workbook was built.
00:00 - Intro
01:21 - Master Data (Lookup Tables)
02:06 - Chart Layout
05:12 - Data Validation
08:50 - Duration Formula
11:38 - Gantt Chart Formula
14:52 - Conditional Formatting
19:54 - Different Formula Methods For The Gantt Grid
20:30 - Dynamic Array Versions
22:24 - Getting NETWORKDAYS.INTL To Work With Dynamic Arrays
23:07 - Adding New Rows & Columns To The Chart
23:48 - Summary
NETWORKDAYS.INTL formula arguments
https://support.microsoft.com/en-gb/o...
Download the project plan created in this video
https://drive.google.com/drive/folder...
Microsoft's Create site has some Gantt chart templates you may want to check out for more ideas!
https://create.microsoft.com/en-us/se...
Intro music: "Pitbull" by Michael Ramir C.
https://mixkit.co/
#microsoft #ms365 #msoffice #office #excel #gantt #ganttchart #project #projectplan #dynamicarrays #dynamicarrayformula