Gantt Excel is all about speed. A million calculations need to take place in literally less than a second to create the beautiful timeline that you see.
If the calculations in your gantt worksheet has slowed down here are a few things to watch out for.
- Do you have other excel worksheets with formulas open?
This can slow down the Gantt template. Please close all other excel files especially if you have a lot of formulas in them. - Do you have a lot of memory intensive programs open on your computer?
You may have to close them as Microsoft Excel may not enough memory for it to work correctly - Do you have a single parent task with hundreds of child tasks under it?
We do not recommend this structure as any change within the child tasks will trigger date, cost and timeline calculations for all child tasks, sub parent tasks and parent tasks. - Do you have a lot of task dependencies in your program? Are all tasks dependent on each other?
Well in this case any change within the dependent tasks will trigger date, cost and timeline calculations for all tasks, sub parent tasks and parent tasks. This does take time (should take less than 2 seconds for 200-300 tasks)
As an example, here is a small sample of calculations that happen when you add a task.
- Check if the worksheet is a gantt worksheet
- Unprotect required parts of the worksheet
- Add today's date for Estimated start date
- Add default duration as 1 day
- Scan all holidays and add them to memory to calculate end date
- Scan workdays in a week and add them to memory to calculate end date
- Calculate estimated end date based on start date, duration, holidays and workdays
- Scan resources so that they can be selected
- Trigger Resource cost calculation based on duration
- Set and save baseline dates and calculate based on items 2-6
- Set and save actual dates and calculate based on items 2-6
- Check date format in settings and format all date fields
- Save estimated, baseline and actual costs
- Check currency in settings and format all cost fields
- Scan all tasks that can be set as predecessor tasks and save them to the tasks dependencies dropdown
- Save all predecessor tasks that user has selected with type of task dependency and lag
- Recalculate estimated start and end date based on task dependency information
- Save estimated, baseline and actual color data into the task row
- Check if Percentage calculations are set to Automatic in settings
- If yes then calculate task percent value based on start date, end date and current date
- If not save manually entered value
- Save notes to task row
- Check the current view (daily, weekly etc...)
- Calculate Timeline start date based on earliest task start date
- Calculate and set timeline columns based on number of time columns to be drawn set in settings.
- Check if task estimated and end dates are available in the timeline
- Calculate estimated timeline bar start position based on start date in the timeline
- Calculate estimated timeline bar end position based on end date in the timeline
- Draw estimated timeline bar based on color set by user
- Check if baseline bar needs to be drawn
- If yes then
- Calculate baseline timeline bar start position based on start date in the timeline
Calculate baseline timeline bar end position based on end date in the timeline
Draw baseline timeline bar based on color set by user
Check if percent complete bar needs to be drawn
If yes then
Calculate start point based on start date of task
Calculate end point based on start date, end date and task percent complete value
Draw percent complete bar based on color set by user
Check if actual bar needs to be drawn
If yes then
Calculate actual timeline bar start position based on start date in the timeline
Calculate actual timeline bar end position based on end date in the timeline
Draw actual timeline bar based on color set by user
Check if overdue bar needs to be drawn
If yes then
Calculate if task is overdue based on start date, end date, duration, task percentage complete and today's date
Calculate and draw overdue bar based on color set by user
Calculate today's date and find today column in timeline
Draw Today column bars based on color set by user
Draw borders for newly added task based on color set by user
Move other tasks if task was added in between tasks
Add line 'Type here to add new task' at the bottom of all tasks
If task is a child task then recalculate all dates (baseline, estimated and actual), all costs (baseline, estimated and actual), task percent for
All sub parent tasks
All parent tasks
Check if all dates, costs and task percent is valid for all child tasks
Redraw all timeline bars for all parent tasks
Redraw all timeline bars for all sub-parent tasks
If task is a dependent task then recalculate all dates (baseline, estimated and actual), all costs (baseline, estimated and actual), task percent for
All sub parent tasks
All parent tasks
Check if all dates, costs and task percent is valid for all predecessor and successor tasks
Redraw all timeline bars for all parent tasks
Redraw all timeline bars for all sub-parent tasks
Calculate start and end points for all dependency task arrows
Draw dependency arrows based on color set by user
Again this is just a tiny subset of the things that happen when a single task is added. We often stop all work and sometimes marvel at how extremely powerful excel is.
It is truly an amazing piece of software and definitely the best amazing spreadsheet program ever made.
So if your Gantt chart has slowed down and if you need us to take a look at it please raise a ticket and send us your file. We'll be happy to check it for you.