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

48 thoughts on “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. 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.

  3. 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?

      1. This looks amazing. I’m also curious how I can tweak this so I can display days

  4. 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?

  5. This is amazing and incredibly helpful! I’m trying to do this on a Mac, though, and running into problems at step 2. Any advice?

  6. Thank you so much for your fast replies! It is open in Mac 2011. The problem is that the options listed in step 2 aren’t things available to me, such as right clicking, and editing the series.

    1. It’s hard to advise you without having your version in front of me, but in my version you can also select the data for example by clicking on the design tab in chart tools and then clicking on the select data button. The right click menu in Windows is there as a shortcut only. Does that help?

  7. Alessandra, I have a new challenge… I was trying to replay the same way but to have the timeline vertically insted of horizontally, is that possible?, I have done as you explain (Changing the axes) but it seems that a Line Chart cannot be in reference to a Y axis but always to an X (mathematics and functin concepts I guess…)

  8. Thanks for the excellent post, Alesandra. I created a chart with my own data, but I am having issues since the graph does not update automatically when I change the values in column D. Do you know how I can go around this without doing the whole process over again?


    1. Perhaps I should elaborate: the “event” labels (those coming from column D) do update when I change a value for an already existing “event”. However, if I want to add a wholly new “event” (say, something that happened in Apr 2013 in your example), it won’t show in the graph.


      1. Nevermind! I hadn’t realised that I also need to include a value in column E. Duh! Thank you anyway =)

  9. This is really impressive, thank you. I am wondering if it is difficult to have the values on the x-axis be irregularly spaced. In my case, I”m trying to show when events occurred. The dates are, for example, 6/22, 6/29, July 8, Aug 6, Sep 26. I want the labels for each event like you have, but also for people to notice that there is more data from June than from August. Is this possible by making some small adjustments, or is it trickier than that? Thanks again for this, it is great.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s