Create a milestone timeline using Excel

Excel charts are very flexible.  By combining different chart types and multiple axes, you can create a lot of different custom graphs, for example: waterfall charts, Pareto charts, stacked charts, thermometers, gauges, dot tables, bullet charts, box plots etc.  In fact you can even create timelines:Obviously Excel is not the best software to create a timeline, you can do this directly in PowerPoint or using Gantt chart software for example (I really recommend this add in for PowerPoint Office Timeline).  However the techniques involved in creating a timeline in Excel are really quite interesting to know.  In fact we are going to combine Column and Line Charts, add Error bars, use custom markers, add data labels and use multiple horizontal and vertical axes.

Step 1: Set up your data table:

Feel free to download the sample excel sheet here.

1. In your first column type in the months that you will need.  In my example, I have added Jan – Dec and then Jan to Jul.  Then add a second column to show the year.
2. Fill the next column with zeros (This will form the actual bottom line of our graph / timeline).
3. In the next column add in the labels you need for your timeline according to the dates needed.
4. Then in the final column put numbers corresponding to the height you wish the label to appear.

Step 2: Create a basic bar chart

1. On the ribbon, click on the insert tab > Column > 2D Column > Clustered Column.
2. Right click on the chart area that appears and click on Select Data.
3. Click on the Add button and choose the height column (column E) as the series values.
4. Add another series, and choose the zero column as the series values.
5. Click on the Edit button to adjust the Horizontal (Category) Axis labels.  Select Column D.
6. Press the OK button.  The chart should appear as below.

Step 3: Change the second series to a line chart

1. With the chart selected, on the ribbon, click on the Chart Tools > Format tab.
2. In the Current Selection area, choose Series 2 from the drop down list.
3. Press Ctrl+1 to open the formatting menu box.
4. On the series options tab, click on “Plot Series on Secondary Axis” and press the Close button.
5. A secondary vertical axis will appear.
6. At the moment, you will not be able to see this series plotted on the chart, so in cell C1, type in a value of 1 to make the series visible.
7. Then right click on the new series and choose Change series chart type.
8. Choose a Line with markers chart type and press the OK button.
9. Change the 1 value in cell C1 back to zero.

Step 3: Format series 2

1. Click on series 2 and press Ctrl+1.
2. Click on the Line Color tab.
3. Change the line colour to a Gradient Line and adjust the colours.
4. Click on the Line Style tab and increase the line width to 12pt.
5. To create the custom markers:
   1. Click on the Insert tab of the ribbon > Shapes > Rectangle.
   2. Draw a small rectangle on the worksheet.  Adjust the fill to match the line colour.
   3. Click on the rectangle shape and press Ctrl+C.
   4. Click on series 2 on the graph and press Ctrl+V.
   5. The markers should update with the custom symbol.
   6. Right click on series 2 once more, and choose select data.
   7. Click on Series 2 and then click on the Edit button to adjust the Horizontal (Category) Axis labels.  Select Columns A & B then press the OK button.

Step 4:  Add error bars to series 1

1. Click on series 1.
2. Then click on Chart tools on the ribbon > Layout > Error bars > Error bars with percentage.
3. Click on the error bars that appear and press Ctrl+1.
4. In the vertical error bars tab of the menu box that appears, choose Minus, No cap, and change the percentage value to 100%.  
   
5. Adjust the Line Color and Line style as you wish.  I’ve added a thin blue gradient dotted line.

Step 5: Format series 1 and remove axes

1. Left click on Series 1 once more and press Ctrl+1.
2. Change the fill colour of the bars to none and the border colour to none as well and press the Close button.
3. Delete the legend.
4. Double click on the primary vertical axis and adjust the range from -0.5 to 4.5.
5. Double click on the secondary vertical axis and also adjust the range from -0.5 to 4.5.
6. Then delete the primary and secondary vertical axes.
7. Delete the horizontal axis.
8. Delete the Vertical axis major gridlines.

Step 6: Add the data labels

1. With the chart selected, on the ribbon, click on the Chart Tools > Format tab.
2. In the Current Selection area, choose Series 2 from the drop down list.
3. Then click on Data Labels > More Data Label Options.
4. In the menu box that appears, choose Category name and Below then press the Close button.
5. Repeat steps 1 to 4 for Series 1, and choose Category Name and Outside End.
6. The chart should appear as below:

Step 7: Final formatting

1. If any of the data labels are not perfectly positioned, you can double click on them and then move them into the position you would prefer.  For example in the chart above the label “Final prototype trial” crosses over one of the lines and needs to be moved to the left.
2. If you wish to make any of the labels stand out for example Jan 13 and Jan 14, double click on them and adjust the formatting.
3. You can add shadow etc. as you wish.

And that is it!  Enjoy!  Once again, if you wish you can download the sample excel sheet here.

Alesandra Blakeston

Advertisements