Manage Consulting Projects with an Excel Timeline
Excel timeline charts are a great tool in any consultant’s toolkit. You can insert Excel timelines in your Excel workbooks where you manage projects, monitor your Statement of Work, show resources and expenses, schedule marketing events, etc. They make communication and detection of conflicts much easier.
In the following article you’ll learn how to make a static timeline. In the next article you’ll learn how to create a dynamic timeline that you can scroll through a long database of milestones or events. This is a tool worth using!
Click here to read the article on the dynamic scrolling Excel timeline <link>
Although creating the first static timeline has a lot of steps you can use it as a template to create new timelines.
While there are numerous methods of creating timelines in Excel most have shortcomings. Either they are difficult to create or they are static or changes require time consuming manual updates. This method gets around those problems.
Begin by entering the data as shown. These steps will be easier for you to follow if you enter data in the same cells as shown.
The columns in the worksheet are,
The date of each event. A custom “mmm d” format was used here.
This will become the horizontal axis for date labels.
These are the events or milestones corresponding to the dates in column B.
These are the vertical positions each Event label will appear at in the Excel chart.
Create a Two-Column Bar Chart
Create this two-column bar chart by manually selecting the data series in the Select Data Source dialog box.
- Click a blank cell that isn’t touching the data set.
- On the Insert tab, in the Charts group, click Column and select 2D Column Clustered Column. A blank chart will appear. Keep it selected.
- On the Chart Tools Design tab, in the Data group, click Select Data to display the Select Data Source dialog box. In this dialog you manually specify the location of labels and data for charts. There are no selections in it because you had a blank cell selected.
- Add the data series for the first columns by clicking the Add button to add the first data series. In the Edit Series dialog that appears, select the Label Height data, $E$5:$E$16, as the Series Value. Click Ok. The first set of columns will appear.
- Add the data series for the second columns by clicking the Add button to add the second data series. In the Edit Series dialog box select the Baseline data, $C$5:$C$16, as the Series Value. Click Ok. You won’t see these columns because they have zero height.
- Add the Events as the horizontal axis series. This creates the Event labels on the horizontal axis. In the Select Data Source dialog box on the left side is the Horizontal (Category) Axis Labels. Click Edit and select the Events range, $D$5:$D$16. Click Ok.
You should now see one set of columns and the event labels. Series 2 columns have zero height.
- The Select Data Source now shows the Event labels as the Horizontal Axis Labels as shown below. Click Ok.
Change to a Dual-Axis Chart with Series 2 as a Line Chart
- Select the entire chart by clicking on its edge.
- Select the Chart Tools Format tab. Select Series 2 from the Current Selection drop-down list at the far left on the Chart Tools Layout ribbon.
- Format Series 2 by clicking the Format Selection button at the far left of the ribbon or press Ctrl+1 to open the Format Data Series dialog box. Select the Secondary Axis option and click Ok. This will make Series 2 data use a secondary vertical axis on the right side of the chart.
- Change the data in the Baseline column from 0s to 1s so you can see the Series 2 columns. The Series 2 columns may display on top Series 1 columns.
- Right-click on the Series 2 columns and select Change Series Chart Type. Select Line with Markers and click Ok. You chart should now look like this.
- Change the 1s in Baseline back to 0s and the line will drop down. It will later become the horizontal axis for your timeline.
Format the Series 2 Line
The Series 2 line, shown in red here, will become the horizontal axis for your timeline. You can format the line and its markers as simply or as fancy as you want.
- Save your worksheet so if you make a serious mistake formatting Series 2 that can’t be undone you will be able to return to the saved worksheet.
- Format the Series 2 line the way you want the horizontal line to appear in your timeline. Select Series 2 by selecting Series 2 from the Current Selection at the left end of the ribbon and then pressing Ctrl+1 or clicking the Chart Tools Format tab and then clicking the Format Selection button at the left end of the ribbon.
Here are a few ideas for formatting the horizontal timeline axis,
- Format it as an unadorned straight line without markers for simplicity.
- Format it as a wide line to create a base foundation.
- Format the data markers as circles or triangles to make the date more obvious.
- Paste a drawn object onto the data markers to give it a special “ruler” effect.
Add the Dates to the Timeline
- Select the Series 2 data by clicking on it or by selecting the Chart Tools Format tab and selecting Series 2 from the Current Selection drop-down list.
- In the Chart Design Tab, in the Data group, click Select Data to display the Select Data Source dialog box.
- Select Series 2 and on the Horizontal Axis Labels side of the dialog box, click the Edit button to display the Axis Labels dialog box.
- Enter the date range, $B$5:$B$16, and click Ok. The Select Data Source dialog box will now show dates as the horizontal axis as shown in this figure. Click Ok.
- Although you are close to being complete your chart still does not show dates. It should look like this,
Add Vertical Lines (Error Bars) to Connect Labels to the Timeline
Use Excel’s error bars to draw a connecting line between the event labels and the horizontal timeline.
- Select Series 1 (the blue columns).
- In the Chart Tools Layout tab, in the Analysis group, click Error Bars and select Error Bars with Percentage.
- Click again on the Error Bars button and select More Error Bar Options to display the Format Error Bars dialog box.
- In the Vertical Error Bars tab, select Minus Direction (from the top of the column to the horizontal axis) and enter 100 as the Percentage. Click Ok.
- With Series 1 selected, press Ctrl+1 to display the Format Data Series dialog box. Set the Fill to No Fill and set the Border Color to No Line.
You should now have a chart showing vertical lines without columns.
Format the Chart
With a little cleanup the Excel timeline chart is almost done.
- Save the timeline worksheet just in case. (Yes, I’m a little paranoid about the work you’ve put into this.)
- Click the Legend and press Delete.
- Click the horizontal grid lines and press Delete.
- Adjust the scale for both left and right axis to maximum 20 and minimum -20 by right clicking on the numbers on the side of the axis, choosing Format Axis and entering Maximum 20 and Minimum -20 in the Format Axis dialog box. Click Close. Do both axes the same.
- Select each vertical axis line and press Delete.
Display the Event and Date Labels
- Select Series 2 by selecting the Chart Tools Format tab, then selecting Series 2 from the Current Selection drop-down at the left side of the ribbon.
- To display Dates on the horizontal axis, select Data Labels and More Data Labels Options to display the Format Data Label dialog box. Select Category Name. Deselect Value. Set Label Position to Below. Click Close.
- Select Series 1 by selecting the Chart Tools Format tab, then selecting Series 1 from the Current Selection drop-down at the left side of the ribbon.
- To show Event labels, select Data Labels and More Data Labels Options to display the Format Data Label dialog box. Select Category Name. Deselect Value. Set Label Position as Outside End. (This option is for labels on the error bars.) Click Close.
- You will see the event labels along the horizontal axis and floating at the ends of the error bars. Click the horizontal axis to select all the labels on the axis and press Delete to remove them.
At this point the Excel timeline chart is finished and should look like the following figure, but some formatting would help make it more readable.
Now that you have the chart complete, save it again and format it as fancy as you want. You can make timelines quite delicious, but I’d recommend saving to a new file name occasionally in case you want to backup farther than Undo reaches.
Three things you can do to make the event labels more readable are,
- Select all the event labels and format the label background as white. This masks lines or text underneath so the label is readable.
- Change the Label Height in column E to give greater separation between labels.
- Make the chart as large as possible.
You can reuse this Excel timeline chart as a template by changing the data or by adding or deleting cells in the range B5:E16. Just be sure you insert or delete through the middle of the range and the chart will adjust to the change.