Friday, June 6, 2014

10 steps to building a project timeline in Excel

Susan Harkins shows you how to implement a project timeline in Excel by using a scatter chart. 

An informed team is a productive team. Everyone on your team should be familiar with a project's schedule -- when each task is scheduled to start and end. A project timeline, similar to the one shown in Figure A, shares the project's overall schedule and progress while communicating each task's relationship to the others. In this article, I'll show you an old but true method that uses a scatter chart to implement a project timeline in Excel.
Figure A

Figure A

This project timeline isn't as difficult to create as you might think.
Excel 2013's chart interface is significantly different from earlier versions, so this article includes instructions for finding options in 2013 and 2010. I'll also include Excel 2003 instructions for finding options via the older versions menu. The downloadable demo .xlsand .xlsx files contain the completed chart and its data.

1. You'll need data

Figure B shows our sample data; each record represents a task within a single project with multiple tasks. The first three columns are self-explanatory. The Current and Completed values represent the project's current status and the number of days completed, respectively. The days completed value is a representation of your original schedule. You might spend more or fewer days to actually complete the task. The Completed values are the result of a simple formula: =D5*C5
Figure B

Figure B

Break down the project into tasks and give each a start date, an ID, and estimate the number of days your team will spend on each task.
The Priority values denote each task's position in the chart and can represent each task's priority. What that means is that the higher the priority value, the higher that task appears on the chart (vertically speaking). You can also assume that the higher the task appears on the chart, the more important it is (in relation to the others). If your tasks don't have a priority, you'll still need these values to position the tasks on the chart. Entering a negative value forces the task to appear below the Y axis. Most tasks won't be so low in priority, but it is possible. Don't worry too much about getting the priority values just right -- you can adjust them later, once the chart elements are in place.

2. Insert a chart

We'll build a scatter chart by adding a series to a chart window, which is probably different from what you've done before. Without selecting any values, do the following:
  1. Click the Insert tab.
  2. In the Charts group, click Scatter. [2003: Click the Chart Wizard to start the process.]
  3. Choose Scatter With Only Markers from the gallery. [2003: Click Finish instead of Next.]
Excel will insert a blank chart into your sheet. For now, it might be easier to work with the chart in the data sheet. (You might need to resize and move the chart.)

3. Add a series

Right now the chart is empty, so let's add the first series, as follows:
  1. Right-click the chart and choose Select Data. [2003: Choose Source Data.]
  2. In the resulting dialog, click Add. [2003: Click the Series tab to find Add.]
  3. You can give the series a name, if you like. For now, leave the name control blank.
  4. Identify A5:A10 as the Series X Values range.
  5. Identify F5:F10 as the Series Y Values range, as shown in Figure C.
    Figure C
    Figure C
  6. Click OK twice. The resulting chart is shown in Figure D. (Only Excel 2013 supports the new option icons to the right. If you're using an earlier version, your chart will be similar but not exactly the same.)
    Figure D
    Figure D

4. Format the chart

The chart needs a bit of formatting to improve its visual impact. First, select and delete the Series 1 legend (if the chart displays one). Next, hide the Y (vertical) axis values as follows:
  • 2013: Select the chart, and then click the Chart Elements icon (the plus sign to the right of the chart). Click the Axes arrow and uncheck Primary Vertical, as shown in Figure E.
  • Other versions: Right-click the vertical axis area and choose Format Axis. In the resulting dialog, choose None from the Axis Labels dropdown.
Figure E

Figure E

Remove the vertical axis labels in Excel 2013.
Hide the gridlines as follows:
  • 2013: Click the Chart Elements icon and uncheck Gridlines.
  • 2010: Click the contextual Layout tab. Then, click the Gridlines drop-down (in the Axes group) and choose None for both options.
  • 2003: Right-click the plot area, choose Chart Options, click the Gridlines tab, and uncheck both options.
At this point, your chart should resemble the one shown in Figure F.
Figure F

Figure F

A little formatting makes a difference!

5. Add data labels

As is, you don't know what the markers represent, so let's display data labels to identify the markers as follows:
  • 2013: Click Chart Elements, check Data Labels, and choose Left.
  • 2010: Click the Data Labels drop-down and choose Left (in the Legends group on the Layout tab).
The labels will display the priority values. To display the task ID instead is a delicate process:
  1. Click a data label -- not the label's corresponding marker, but the actual label control. Doing so will select all of the data labels.
  2. Click a second time to select only the clicked label.
  3. Click the Formula bar and enter the cell reference (including sheet name) for the cell that contains that marker's task ID. Figure G shows the change for the first marker. Use the data as your guide -- this is one of the reasons why working with the chart in the data sheet is beneficial.
    Figure G
    Figure G
  4. Repeat this process until you've updated all of the data labels, as shown in Figure H.
    Figure H
    Figure H

6. Identify today

Seeing the current date in relation to the start dates is helpful, so let's add a series that highlights the current date. This step is probably the most complicated, so be careful. You'll need two new records: one will denote the top and one the bottom of a vertical bar that travels across the chart horizontally. You can add the records anywhere, but you might as well work within the existing structure, as shown in Figure I.
The start values are the current date, so enter a TODAY() function for both records. (The downloadable demo -- linked to above -- uses a specific date so that it remains effective, regardless of when you download it. When you apply this to your own work, enter TODAY() functions.) The priority values should be a bit higher and lower than your highest and lowest values, respectively.
Figure I

Figure I

Use the TODAY() function to pinpoint the current date on your chart.
Now you're ready to add the "today" series:
  1. Right-click the chart and choose Select Data.
  2. Click Add.
  3. The two TODAY() functions are the Series X Values range.
  4. The two priority values are the Series Y Values range, as shown in Figure J.
  5. Click OK twice.
Figure J

Figure J

Define the "today" bar series.

7. Format the today series

At this point, the new today series is two new markers in a different color. We'll turn those markers into an error bar instead. First, select the new today series by clicking either of the two new markers. Then, do the following:
  • 2013: Click Chart Elements (the plus sign to the right of the chart), check Error Bars, then click the option's arrow and choose More Options, which will display the Format Error Bars task pane.
  • 2010: Click the Error Bars drop-down in the Analysis group on the contextual Layout tab, and then choose More Error Bars Options.
  • 2003: Choose Selected Data Series from the Format menu and click the Y Error Bars tab.
If Excel displays options for the horizontal (X) error bar, you need to change that to the vertical (Y). Using the Chart Elements drop-down on the contextual Format tab, select Series 2 Y Error Bars, as shown in Figure K. Or, choose that option from the Error Bar Options drop-down in the task pane. If Excel's already using the vertical error bars, you can skip this step. You won't need to do this for 2003.
Figure K

Figure K

Make sure you're working with the Y Error Bars.
The following settings will add a thin bar between the two markers, as shown in Figure L:
  • Direction: Minus
  • End Style: No Cap
  • Percentage: 100%
Figure L

Figure L

The error bar format will display a line between the two markers.
The error bar is a line object and you can format it as such. With the series still selected, do the following:
  • 2013: Click Fill & Line in the task pane. Select Solid Line in the Line section, Red from the Color drop-down, and 3 from the Width drop-down. Figure M shows the resulting today series.
  • 2010: Close the open dialog, and with the new series still selected, click the contextual Format tab. In the Shape Styles group, click the Shape Outline drop-down. Choose the color red and 3 point weight (or whatever value returns the most appropriate line for your purposes).
  • 2003: Click the Patterns tab. Choose a solid line, red, and a wider weight, and then click OK.
Figure M

Figure M

The today series highlights the current date.
You can watch the series move by entering literal dates as start dates (A2:A3), but be sure to replace them with the TODAY() functions when you're done.

8. Stretch it out

Right now, the series 1 data markers denote the start date, but they don't denote how long you anticipate the task to take. We can change that by also formatting the first series as error bars as follows:
  1. Select the first series (the blue markers) by clicking any of them.
  2. 2013: Click Chart Elements, check Error Bars, click the arrow, and choose More Options. 2010: In the contextual Layout tab, click the Error Bars drop-down (in the Analysis group) and choose More Error Bar Options. 2003: Choose Selected Data Series from the Format menu and click the X Error Bars tab.
  3. In 2013 and 2010, click the Chart Elements drop-down on the Format tab and choose Series 1 X Error Bars. In 2010, move the dialog if necessary; you'll need to select the duration values.
  4. Change the Direction setting to Plus.
  5. Change the End Style setting to No Cap.
  6. Click Custom in the Error Amount section and then click Specify Value.
  7. This next step is why you moved the dialog in 2010. In the resulting Custom Error Bars dialog, specify the Duration values as the Positive Error Value. In Excel 2010, close the dialog box.
  8. Format the error bars
The error bars would be more effective if they were a bit wider, as shown in Figure N. To format them, select them and then use the Chart Elements drop-down (on the Format or Contextual tab) to make sure you're working with Series 1 X Error Bars.
  • In Excel 2013, click Fill & Line in the task pane and choose a Solid Line, a color, and change the Weight to 3 or 4.
  • In Excel 2010, click the contextual Format tab and use the Shape Outline options.
  • In Excel 2003, click the Patterns tab, select the line settings, and click OK.
Figure N

Figure N

Format the error bars so they stand out.

10. Adjust

At this point, you have all the pieces in place and you can start adjusting. The most likely adjustments you'll make are to the priority values. Depending on the size of your chart, tasks on the same vertical plane might overlap, similar to the sample chart's task 2 and 5. Enlarging the chart might help, but it might not. Specifically, I changed the Priority values for tasks 5 and 6 to 7 and -15, respectively, to achieve the final results shown in Figure A.

More to consider

If you add a new record to the bottom of the data range, you'll have to update the chart by adding it to the series. If you convert the data range to a Table, the chart will update automatically. I've chosen not to do use a Table to maintain the chart's backward compatiability with earlier versions that don't support this option.


No comments:

Post a Comment