XLGantt is a program designed to enable project scheduling in Microsoft Excel. It is composed of the following features. You don’t need to install this program because it is a Excel macro file. You just download this file, save it to a folder, and open it in Excel.
1. Download the latest version
Download the program file from the link below!
After downloading, unzip and use the “XLGantt_v4.0.0_20200801_Release_ENG.zip” file.
Note: It is recommended that you do not download a xlgantt file on sites other than xlworks homepage because the file may be changed or may contain virus.
2. Security Settings
After downloading the XLGantt file, when the “PROTECTED VIEW” appears as below, click the “Enable Editing” button.
If you see the “SECURITY WARNING” related to using macros, click the “Enable Content” button.
3. Enter tasks
You can modify the task entered as an example, or delete all the example tasks and retype them.
- The Orange columns in the title (total work, planned work, etc.) are automatically calculated.
- The blue column (task, start date, finish date, %Work Complete, etc.) must be entered manually by the user.
For start and finish dates, just enter the dates of the sub tasks and press “Update Schedule” on the ribbon to enter the date of the summary task automatically.
If you enter only the dates of the subtasks and click “Update Schedule” on the ribbon, the dates of the summary tasks are entered automatically.
1) Enter date
You can enter the start and finish dates for a task, or you can choose from a calendar.
Right-click on the cell where you want to enter the date and pick a date.
Click “Pick a Date” and the calendar will appear. Double click the date you want to enter and the date is entered in the cell.
2) Indent or outdent Task
To indent or outdent a task, select the task (multiple tasks can be selected) and click “Indent Task”, “Outdent Task” on the ribbon.
Let’s indent “Design” and “Development” tasks as follows.
① Select tasks to indent
② Indent Task (Ribbon menu > XLGantt)
③ All tasks from row 9 to row 13 have been indented.
4. Update Schedule
Enter tasks and go to Ribbon > XLGantt tab, click the “Update Schedule”
As shown in the figure below, the workloads of the subtasks, the workloads of the summary tasks, and the dates of summary tasks are automatically calculated.
On the left side of the task, group rows are displayed, and the Gantt charts are automatically displayed on the right side.
Let’s look at the above features one by one.
1) Group rows
Go to the ribbon menu> XLGantt tab > Options > WBS tab.
If “Group rows(Max 8 Levels)” is checked, row groups are always displayed.
2) The date of the summary task is automatically calculated using the dates of the subtasks
• Start date of summary task – The earliest date among the start dates of the subtasks.
• Finish date of the summary task – the latest date among the finish dates of the subtasks .
In the example below, the start date and finish date of the “Design” task are automatically calculated using the start date of the “Design Database” task and the finish date of the “Design UI” task.
3) Total work – The workload of the summary task is automatically calculated using the workloads of the subtasks
• Workload of the summary task – The sum of the workloads of the subtasks.
In the example below, the workload of the “Design” task is automatically calculated as the sum of the subtasks (5 + 10 = 15 days).
– Design Database(5 days)
– Design UI (10 days)
4) Planned work(Planned workload)
The “planned work” displayed to the right of the “total work” is the workload to be done by the current day and is automatically calculated. The calculation method that adds up to the parent workload is the same as the calculation method of “total work”.
The total workload from 9/23/2019 to 9/27/2019 for the “Design Database” task above is 5 days, but total workload from 9/23/2019 to today(as of 9/25/2019) is 3 days.
* Workload calculation method
Go to the ribbon menu > XLGantt tab > Options > Calendar > “Days counted as Nonworking days”, select how the workload is calculated.
- Saturdays, Sundays and the dates entered in the Calendar sheet :
Saturdays and Sundays are automatically excluded. You do not need to enter them. Enter only the dates except Saturdays and Sundays.
- Only Saturdays and Sundays :
Only Saturdays and Sundays are counted as nonworking. No need to enter Saturdays and Sundays in the Calendar sheet.
- Only the dates entered in the Calendar sheet :
Only dates entered on the Calendar sheet are counted as nonworking days and excluded from the workload.
* Example of workload calculation
- “Design UI” task shown below has a duration of 10 days from 9/30/2019 to 10/11/2019..
- However, Saturday and Sunday are nonworking days and two days must be excluded. Thus, the workload is calculated for 10 days.
- When two people work on the above “Design UI” task (in this case, the weight should be entered as 2), the workload is calculated by multiplying the original workload by the weight. Since the workload is 10 days and 2 people work, the workload is calculated as 2 * 10 days = 20 days.
- The weight is the number of people who are put in full time for a task. For example, if you need 1 person to complete a task, enter 1 as weight, but you want 2 people, enter 2 as weight. The weight defaults to 1. If not entered, 1 is automatically entered.
If you want to enter a value other than 1, you can enter 0.5, 1.5, 2, etc. as numbers.
If you enter 0.5, it means that only 50% of the workload is spent per day. Therefore, if you enter 0.5 for the above “Design UI” task, the workload is calculated as 0.5 * 10 days = 5 days.
5)Total Duration, Planned Duration
The workload described above literally calculates the amount of work. If the weight is entered, the weight is multiplied, and the workload of the parent task is calculated by adding up the workload of the child task.
However, the total duration and the planned duration are not the amount of work but the duration. This is the value you need when you are wondering about the duration between the start date and the finish date rather than the amount of work.
- In the example below, the “Design UI” task has a weight of 2, so the total workload is 20 days, but the total duration is simply a duration, so we can see that it is calculated as 10 days.
- The planned duration is to be performed until today (as of 9/25/2019). Since the start date for the “Design UI” task is 9/30/2019, the task has not yet started. Therefore, the planned duration is zero days.
6) Planned %Work Complete, %Work Complete
Planned %Work Complete is the planned progress that should have performed by today.
It is the planned workload divided by the total workload (= planned workload / total workload).
- The Planned %Work Complete of the “Design database” task below is calculated as 60% (= 3 days / 5 days).
- %Work Complete can be entered directly, since the person doing the task can know the progress.
As explained above, let’s look again at the meaning of “Planned %Work Complete” and ” %Work Complete” for better understanding.
- Suppose you have a task that requires you to complete a total of 10 days starting on January 1 and completing January 10, as shown in the example below. If today is January 7, then 70% of the task must be completed by the plan. In other words, the completion rate to be done by today is called the “Planned %Work Complete”.
- However, if today is January 7, but only six days of task is completed, the”%Work Complete” is 60%. Therefore, this task is 10% delayed.
5. Schedule Update after modifying the task
Once you enter a task, it can be rescheduled or changed. If there is any change in the inputted contents, modify it directly. In project practice, performance is often updated on a daily or weekly basis.
1) Change project name, project start date, finish date:
Go to the ribbon menu> XLGantt tab > Options> General> “Project Information”, change the project name to display changed project name in the upper left of the Schedule sheet.
The Start-Finish date can be set in two ways.
- Set “the earlist start date and the latest finish date of the tasks” to the Project Start-Finish date.
- Enter Start-Finish date directly as desired date (uncheck above checkbox)
2) Enter performance (%Work Complete)
The Planned %Work Complete of the task is automatically calculated based on the current date, but since the %Work Complete is only known to the person who has performed the task, the %Work Complete of the task is checked and entered directly.
As shown in the red box image below, “Design Database” task 50%, “Confirm scope” task is entered in the form of 80%
As shown in the red box image below, you can enter 50% of the %Work Complete for the “Design Database” task.
When all the necessary information has been entered, press “Update Schedule” on the ribbon.
- The %Work Complete of subtasks (Design Database, Design UI) is added up to the upper level “Design” task, and the %Work Complete is automatically calculated to 10%.
- On the right, a performance line (blue line) is drawn on the Gantt chart according to the %Work Complete you entered.
[ XLGantt(Excel Gantt) How to ]
- XLGantt(Excel Gantt) How to #4 – Tracking the progress of the project
- XLGantt(Excel Gantt) How to #3 – Setting Gantt chart display styles
- XLGantt(Excel Gantt) How to #1 – Getting Started With XLGantt
- XLGantt(Excel Gantt) How to #2 – Input task, Update schedule