You Gantt do that

Q: Does Excel 2013 provide the option to produce Gantt charts?

A: No, Excel does not produce Gantt charts out of the box, but you can create one with a bit of Excel trickery, as illustrated in the following example.

1. List your tasks. Start by entering a list of tasks including start dates, duration of each task, and end dates, an example of which is pictured below. (I used formulas to calculate the end dates by adding the start date plus the duration days.)

2. Create stacked bar chart. Create an empty stacked bar chart by selecting any empty cell surrounded by other empty cells, then from the Insert tab, in the Charts group, select Stacked Bar, and then click and drag the desired size range on the worksheet (as shown below).

3. Launch the Select Data dialog box. Right-click the blank chart and choose Select Data (or Select Data Source) from the pop-up menu.

4. Reference the start dates. In the resulting Legend Entries box section, click the Add button. In the resulting Edit Series box, enter the phrase Starting Dates in the Series name box. Click the Select Range button (the small icon depicting a worksheet) located at the end of the Series values box, highlight the starting date range in the worksheet (cells B4 through B12 in this example), and then click OK.

5. Reference the duration days. Again, in the Select Data Source dialog box, in the Legend Entries box section, click the Add button. In the Edit Series box, enter the word Days in the Series name box. Click the Select Range button located at the end of the Series values box, highlight the Duration (Days) range in the worksheet (cells C4 through C12 in this example), and then click OK.

6. Reference the task descriptions. Again, in the Select Data Source dialog box, in the Horizontal Axis Labels section, click the Edit button. In the resulting Axis Labels dialog box, click the Select Range button located at the end of the Axis label range box, highlight the tasks on the worksheet (cells A4 through A12 in this example), and then click OK, OK. Your progress should appear as follows:

7. Hide the first set of stacked bars. In the stacked bar chart, click on any of the start date bars (shown in blue above) to select them all. Right-click on the selected bars to pop up the Format Data Series options panel. Click the Fill & Line icon (the pouring paint bucket icon) and select No fill and No line options.

8. Reverse the order of the labels. Right-click on the chart’s task labels, choose Format Axis, and in the Format Axis panel, in the Axis Options section, check the Categories in reverse order box (optional). This action will position the first task at the bottom of the chart, which is an older Gantt chart tradition.

9. Edit the date range displayed by the chart. Holding the Ctrl key, select cells B4 and D12 (the first Start Date and last End Date, respectively). Change the format of these two cells to the General format, and make a note of these two numbers (41886 and 41931 in this example, but not shown above). Right-click on the Horizontal Axis (the chart’s date range) to pop up the Format Axis panel. Click the Size & Properties icon, and then, in the Axis Options section, set the Minimum and Maximum bounds to 41886 and 41931 (the numbers determined in the previous step).

10. Format the date axis. Still in the Format Axis panel, under the Axis Options icon, in the Number section, change the Category dropdown to Date, and then change the Type to a short date option. (I selected 3/14 in this example.)  

11. Finishing touches. As finishing touches:

a. Add a title to your chart.

b. Right-click the duration bars (shown above in dark red), and set the Gap Width to 50% (to increase the width of the bars).

c. Click the Series Options icon, and in the 3-D Format section, click the top bevel dropdown button and then select the Circle bevel.

d. Click the Fill & Line icon, and in the Fill section, click the Solid fill option, and then select a blue color.

e. If you desire, you might also edit the chart’s Plot Area by inserting a picture of the construction project and then adjusting the picture’s transparency to 65% so the chart remains readable.

Presented below is the final Gantt chart report.

You can download this example Gantt chart file at carltoncollins.com/gantt.xlsx.

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2013, 2010, and 2007 versions, unless otherwise specified.

Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.