Article ID: 926369
Article Last Modified on 10/15/2007
APPLIES TO
- Microsoft Office Excel 2007
For a Microsoft Office Excel 2003 version of this article, see 213915.
INTRODUCTION
You can use a Gantt chart in Microsoft Office Excel 2007 to show the progress of a project or to show the relationship among the tasks in a project in relation to time. The unit of time that is most frequently used is days. However, you can also use hours as the unit of time to show the status of the task. To do this in Excel 2007, alter the scale of the value (y) axis.
This article contains an example that uses the hour as the unit of time to show the status of a task.
MORE INFORMATION
The scale in a ordinary Gantt chart is based on the serial number of the date. The scale that is used for the value (y) axis is shown in the following table.
| Axis Setting | Value or definition |
| Minimum | Serial number of the earliest date or of a date that is earlier than the earliest date |
| Maximum | Serial number of the latest date or of a date that is later than the latest date |
| Major Unit | 31 (one month) |
| Minor Unit | 1 (one day) |
| "Category(X) Axis Crosses at" | Same as the Minimum setting |
Time is entered as a fraction or as a decimal representation of part of a single day. The scale that is used for a Gantt chart that uses hours is shown in the following table.
| Axis Setting | Value or definition |
| Minimum | The decimal number that represents the earliest hour that is charted |
| Maximum | 1 (one day) |
| Major Unit | 0.0417 (the decimal equivalent of 1 hour) |
| Minor Unit | 0.000694 (the decimal equivalent of 1 minute) or 0.01 (if minutes are not a factor) |
| "Category (X) Axis Crosses at" | Same as the Minimum setting |
To alter the scale, follow these steps:
- In Excel, create a new worksheet, and then enter the following information in the worksheet.
A1: Depts B1: Start C1: End D1: Duration1 E1: Duration2 A2: NC B2: 8:30 AM C2: 5:00 PM D2: 8.5 E2: A3: TX B3: 8:30 AM C3: 5:30 PM D3: 9 E3: A4: AZ B4: 3:00 PM C4: 12:00 PM D4: 9 E4: A5: WA B5: 8:30 AM C5: 6:30 PM D5: 10 E5: This information shows the times at which four sites across the country start a work day in relation to the other sites.
Note In this example, all the times are Eastern time.
In cell E2, type the following formula:
=D2*0.0417
This formula converts the duration to a decimal fraction of a single day. This duration is expressed in hours.
Note The value 0.0417 is the decimal equivalent of one hour.- Select cell E2.
- Grab the fill handle, and then fill the formula down to cell E5.
- On the Insert menu, click Bar in the Charts area, and then click the 2D Bar chart (this is the leftmost chart).
- Right-click the chart, and then click Edit Data Source.
- In the Edit Data Source dialog box, click Add under Legend Entries (Series).
- In the Edit Series dialog box, click Series Name, and then select cell B1 on the worksheet.
- Click Series Values, delete any values in the box, select cells B2 to B5, and then click OK.
- In the Edit Data Source dialog box, click Add under Legend Entries (Series).
- In the Edit Series dialog box, click Series Name, and then select cell E1 on the worksheet.
- Click Series Values, delete any values in the box, select cells E2 to E5, and then click OK.
- In the Edit Data Source dialog box, click Edit under the Horizontal (Category) Axis Labels, and then select cells A2 to A5 on the worksheet.
- Click OK to close the Axis Labels dialog box, and then click OK to close the Edit Data Source dialog box.
- Right-click the chart, and then click Change Chart Type.
- In the Change Chart Type dialog box, click Bar in the left pane, click Stacked Bar, and then click OK.
Note Stacked Bar is the second bar chart from the left. - On the chart, right-click Horizontal (Value) Axis, and then click Format Axis.
- In the Format Axis dialog box, click Axis Options in the left pane, click Fixed under Axis Options in the right pane, and then set the following values.
Axis Options Value Minimum 0.33333 Maximum 1.2 Major 0.0834 Minor 0.01 - Under Vertical Axis Crosses, click Axis Value, type 0.33333, and then click Close.
- On the Ribbon, click the Layout tab, expand Chart Area, click Series Start, and then click Format Selection.
- In the Format Data Series dialog box, click Fill in the left pane, and then click No fill in the right pane.
- In the Format Data Series dialog box, click Border Color in the left pane, click to select No line in the right pane, and then click Close.
The chart displays the duration of each shift as a floating bar. The left edge of each bar is the starting time, and the right edge is the ending time. The relative positions of the bars show the relationships among the shifts.
Additional query words: XL2007 Excel2007
Keywords: kbhowto kbinfo kbexpertisebeginner KB926369
