Gantt Chart with Excel in 5 minutes Part 1/2

Create a Gantt chart (3 phase, 2 level WBS) from a blank sheet by filling cells with color. This is not a graph version.
後半で音ズレ(音先行)御免。
AND条件式の説明で"何々または"と舌がすべた。正しくは"なお且つ"です。

[used Excel functions]
Grouping; Data/Group and Outline/Group
Date calculation; =workday(Start date, days)
AND condition; =AND(date is larger or equal to Startdate, date is smaller or equal to Enddate)
Conditional Format with formula.

[PROCESS]
Step 1, Create Header. 0:10
Task Id, Task, Duration, Start Date, End Date.

Step 2, Create Calendar. 0:36
Change date format to display day of the week and change alignment to vertical.

Step 3, Input Tasks. 1:05
Main task flow, Preparation, Execution then Review
Create Work Breakdown Structure (WBS) with Task 1, Task 2 and Task 3.
Increase indent of these sub tasks
Set sub tasks as group.
Input duration of tasks

Step 4, create formula to calculate start and end date. 2:25
Use WORKDAY function, to use this function you need Analysis ToolPak add-in,
Copy the formula down.
Correct start dates of task 1 of second phase and third phase.
Fill start and end date of summary tasks.
Start date of the summary task is same to the start date of sub-task 1.
End date of the summary task is same to the end date of sub-task 3.
Change appearance of summary tasks.

Step 5, create Logical Test formula using AND function. 3:57
Select first calendar date and first task cell (left top cell)
Logical test to check the calendar date is within start and end date of the task.
Set up absolute cell address of the formula so that the formula applied to the rest of cells.
Copy (Ctrl + C) the formula

Step 6, Set up Conditional Format. 5:08
1st, select the calendar cells. Cover entire calendar section (except 1st row of date), and make sure that the pointed cell is most left top cell where "AND" formula is created.
Open Conditional Format window.
Past (Ctrl + V) the formula created in step 5.
Apply the format to cells.

☆ This Excel worksheet is available for download at Google Docs. エクセルファイルのダウンロード
https://docs.google.com/leaf?id=0BzkoU613v8EkMTg5ZDE3OTctZDY3OS00YTIwLTk0MzIt...

In next video Part 2/2, I will explain followings
https://www.youtube.com/watch?v=D-LFxuNVGOg
1. skip Sat and Sun filling with color
2. change color for summary tasks and low level tasks
3. add task completion rate.

☆ other Tips for Project Management.
https://sites.google.com/site/mkdatamining/pc-etcetera

Category:

Howto & Style

Tags:

• Gantt Chart
• Excel
• HowTo
• ガントチャート
• WBS
• Work Breakdown Structure
• management
• conditional formatting
• workday