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:TimelineObviously 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.dates
  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.labels
  4. Then in the final column put numbers corresponding to the height you wish the label to appear.height

Step 2: Create a basic bar chart

  1. On the ribbon, click on the insert tab > Column > 2D Column > Clustered Column.2D 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.series 1
  4. Add another series, and choose the zero column as the series values.Series 2
  5. Click on the Edit button to adjust the Horizontal (Category) Axis labels.  Select Column D.axis labels 1
  6. Press the OK button.  The chart should appear as below.chart 1

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.current selection
  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.chart 2
  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.chart 3

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.line color
  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.marker
    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.chart 4
    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.series 2 horizontal axis

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.error bars
  3. Click on the error bars that appear and press Ctrl+1.error bars 2
  4. In the vertical error bars tab of the menu box that appears, choose Minus, No cap, and change the percentage value to 100%.  format error bars
  5. Adjust the Line Color and Line style as you wish.  I’ve added a thin blue gradient dotted line.chart 5

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.chart 6
  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.chart 7

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.current selection
  3. Then click on Data Labels > More Data Label Options.data labels
  4. In the menu box that appears, choose Category name and Below then press the Close button.format data labels
  5. Repeat steps 1 to 4 for Series 1, and choose Category Name and Outside End.
  6. The chart should appear as below:chart 8

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.Timeline

Alesandra Blakeston

About these ads

24 responses to “Create a milestone timeline using Excel

  1. Great thanks for the lesson, I have a number of projects on the go that will benefit from this type of time line

  2. Pingback: Excel milestone timeline – version 2!!! | User Friendly·

  3. Exactly what I was looking for!
    Thank you very much.
    By the way, I like your blog and I will keep an eye for new posts while I read some of the old ones.

  4. Thank you so much! Q: Is it possible to add days to the months and years? I understand that an extra column with 28/29/30/31days would be very long. Is there a way to add just the labeled days and show it in the proper place on the timeline?

  5. Pingback: Creating a score meter in Excel | User Friendly·

  6. Pingback: Create a milestone timeline using Excel | User ...·

  7. Just great, Alesandra, thanks!
    I tried a little to have more milestones at the same date with different heights of the milestone labels. I did not succeed. Can you give me a hint?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s