Draw a simple Gantt chart in Excel2010

The Gantt Chart (Gantt Chart) is also called a bar chart, which graphically represents the sequence and duration of the activity of a particular item. In Excel 2003, you can quickly draw a simple Gantt chart with a "suspended bar" in a custom chart type, while in Excel 2007/2010 there is no ready-made custom chart type available, but it is also easy to implement with stacked bar charts. Here's an example of Excel 2010 to illustrate the drawing steps, similar to the steps in Excel 2007, where the sample files can be downloaded at the end of the article.

Figure A is listed as some project code for a project, B to D listed as "Start Date", "Days" and "End Date", B11 a date within the project period in cell, this example is "2011-10-18", and you need to draw a simple Gantt chart with that date as the current progress. and use a bold vertical bar in the chart to indicate the date.

If the project is currently in progress, you can enter the current date in cell B11, such as the formula:

=today ()

This way, you can display the progress of the day by opening the workbook every day.

The specific steps are as follows:

1. Add secondary columns

Inserts two columns to the left of the original C column of the data range, representing the number of days that the items in the B11 cell have been completed and unfinished, with the date in the current progress. After inserting two empty columns, enter the formula in the C2 cell, as shown in the figure:

=if ($B $11-b2>e2,e2,min (E2,max ($B $11-b2,0))

Fills the formula down to the C9 cell, which gets the number of days that have been completed. Enter in D2:

=e2-c2

Fills down to the D9 cell, which gets the number of days to complete.

2. Insert Stacked bar Chart

Select a cell in the data range, and in the Ribbon, select Insert → bar → stacked bar chart. Delete the legend in the inserted chart, and then select Select Data on the chart tools-Design tab, pop-up the Select Data Source dialog box, select the unwanted days and end dates series, and click The Delete button deletes the two series and determines when the diagram looks like this:

3. Format related elements in a chart

Double-click the Start Date series in the chart, pop-up the Format Data Series dialog box, and, if you are Excel 2007, right-click the series and choose Set Series Formatting from the shortcut menu. You do not close the dialog box throughout the entire process of setting the related elements of the chart.

Select the Fill option in the dialog box and select No Fill.

Triple Recommendation: Click for free Download the latest version of WPS Office software "to see more computer tutorials

The start date series becomes transparent because the order of the items in the vertical axis is not the same as the custom in the data area, and can be changed by the following method. Select the vertical (category) axis, the dialog box title automatically changes to format axis, and in axis options, click Reverse category to move the horizontal axis above the chart to move it back below the chart to select maximum category in the cross-axis cross area.

Click the horizontal (value) axis in the chart again, in Axis options, set maximum and minimum values to fixed, and enter the start and end dates for the entire project, in this case the minimum value is set to date "2011-10-9" and the maximum value is set to "2011-11-10". For Excel 2007, enter the sequence number of these two dates, 40825 and 40857, respectively. There are many ways to get the number of date series, such as setting a cell containing a date to general to display the sequence number of that date in a cell.

Select the number option to set the date format to show only the number format for the month day.

Close the dialog box, and the diagram below.

4. Add a vertical bar to the chart to indicate the current date

The method is to add the current date series to the secondary axis, change the chart type to a line chart, and add a vertical line. The specific steps are as follows:

① Add the current date series: On the chart Tools-Design tab, click Select Data, eject the Select Data Source dialog box, and click the Add button. Set the series name to B11 cell in the Edit Data Series dialog box that pops up. Change "={1}" in "Series value" to "={0}", click OK, add a new series "2011-10-18" in the dialog box, and click OK again to close the dialog box.

② set the new series to the secondary axis: Select the chart Tools-Layout tab and select the newly added series "2011-10-18" in the Drop-down list in the current selection group.

Then click the Format Selection button below the Drop-down list to bring up the Set Series Format dialog box, and in series options, set the series to secondary axis and close the dialog box.

③ Change the chart type of the new series: Click the Chart Tools-Design tab, click the Change Chart Type button in the type group, and change the chart type to line chart in the dialog box that pops up.

④ Modify horizontal (category) axis labels: On the Data tab, click Select Data, pop the Select Data Source dialog box, select the series "2011-10-18", and click the Edit button under horizontal (category) axis labels on the right, and set the axis label area to B11 cell. Click OK to close the dialog box two times.

⑤ set Secondary horizontal axis formatting: Switch to the Layout tab again, and in the Axes group, click axis → minor horizontal axis → show left-to-right axis, where the secondary horizontal axis appears above the chart. Select the axis, click the Format Selection button, eject the Format Axis dialog box, set the axis minimum value to date "2011-10-9", and set the maximum value to "2011-11-10", the same as the primary horizontal axis.

The position axis is then set to on tick marks in the area at the bottom of the dialog box so that the tick position of the secondary axis is aligned with the primary horizontal axis after setting.

⑥ Add vertical lines: Once again, select the series "2011-10-18" in the Drop-down list in the current selection group, and then click polyline → vertical lines in the analysis group, which adds a vertical line with the same position as the current date (2011-10-18).

⑦ Add data Labels: then in the tags group, click data labels → other data label options, pop-up the Format Data Label dialog box, in the label include area to cancel the value, and check the series name, close the dialog box. Because the series name is the current date, the series name that is added to the chart is the current date.

5. Modify the chart format and beautify the chart:

Delete the minor horizontal axis, secondary vertical axis, and adjust the data labels to an appropriate location, such as the top of the vertical line, and modify the vertical line color and line style. Finally, according to the need to modify the series of fill color to beautify the chart.