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.
- 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.
- Fill the next column with zeros (This will form the actual bottom line of our graph / timeline).
- In the next column add in the labels you need for your timeline according to the dates needed.
- Then in the final column put numbers corresponding to the height you wish the label to appear.
Step 2: Create a basic bar chart
- On the ribbon, click on the insert tab > Column > 2D Column > Clustered Column.
- Right click on the chart area that appears and click on Select Data.
- Click on the Add button and choose the height column (column E) as the series values.
- Add another series, and choose the zero column as the series values.
- Click on the Edit button to adjust the Horizontal (Category) Axis labels. Select Column D.
- Press the OK button. The chart should appear as below.
Step 3: Change the second series to a line chart
- With the chart selected, on the ribbon, click on the Chart Tools > Format tab.
- In the Current Selection area, choose Series 2 from the drop down list.
- Press Ctrl+1 to open the formatting menu box.
- On the series options tab, click on “Plot Series on Secondary Axis” and press the Close button.
- A secondary vertical axis will appear.
- 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.
- Then right click on the new series and choose Change series chart type.
- Choose a Line with markers chart type and press the OK button.
- Change the 1 value in cell C1 back to zero.
Step 3: Format series 2
- Click on series 2 and press Ctrl+1.
- Click on the Line Color tab.
- Change the line colour to a Gradient Line and adjust the colours.
- Click on the Line Style tab and increase the line width to 12pt.
- To create the custom markers:
- Click on the Insert tab of the ribbon > Shapes > Rectangle.
- Draw a small rectangle on the worksheet. Adjust the fill to match the line colour.
- Click on the rectangle shape and press Ctrl+C.
- Click on series 2 on the graph and press Ctrl+V.
- The markers should update with the custom symbol.
- Right click on series 2 once more, and choose select data.
- 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
- Click on series 1.
- Then click on Chart tools on the ribbon > Layout > Error bars > Error bars with percentage.
- Click on the error bars that appear and press Ctrl+1.
- In the vertical error bars tab of the menu box that appears, choose Minus, No cap, and change the percentage value to 100%.
- 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
- Left click on Series 1 once more and press Ctrl+1.
- Change the fill colour of the bars to none and the border colour to none as well and press the Close button.
- Delete the legend.
- Double click on the primary vertical axis and adjust the range from -0.5 to 4.5.
- Double click on the secondary vertical axis and also adjust the range from -0.5 to 4.5.
- Then delete the primary and secondary vertical axes.
- Delete the horizontal axis.
- Delete the Vertical axis major gridlines.
Step 6: Add the data labels
- With the chart selected, on the ribbon, click on the Chart Tools > Format tab.
- In the Current Selection area, choose Series 2 from the drop down list.
- Then click on Data Labels > More Data Label Options.
- In the menu box that appears, choose Category name and Below then press the Close button.
- Repeat steps 1 to 4 for Series 1, and choose Category Name and Outside End.
- The chart should appear as below:
Step 7: Final formatting
- 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.
- 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.
- 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.
Great thanks for the lesson, I have a number of projects on the go that will benefit from this type of time line
Thanks Gary. Nice to hear from you! Hope you’re ok. Let me know if you need anything else…
Great work Alesandra, much appreciated!
Alesandra,
Your solutions are very creative and efficient, Thanks!
André Luiz Bernardes
bernardess@gmail.com
Santos – SAO PAULO – BRAZIL
http://inanyplace.blogspot.com/
Glad you found it useful. Let me know if I can help you in any other way!
Very interesting! I would certainly use some of the learnings. Thanks.
It should. Can you send me your amended data to alesandra@about.me and I’ll take a look.
thank you mam this helped alot for my FYP presentation
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.
Wow… that was awesome!!!! Thank you for sharing!
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?
Yeah it’s possible. Send me the file. Alesandra@about.me
This looks amazing. I’m also curious how I can tweak this so I can display days
Very smart! I would never have guessed that this is done in Excel
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?
You need to change the number to have the different heights
I was not clear, sorry. Main part of my question is how to have several milestones at the same date
Ahh ok. In that case, you simply need to space out the titles by moving the numbers down or up rows. You can also add more rows etc.
How do I add dates related to 5 BC or 10 BC? in my time line?
Can you explain more what you mean. Where do you want to add the dates?
Perfect, thank you for sharing (-:
Thanks very much for that tutorial. it really helps
This was immensely helpful for a presentation I was making, thank you!
Reblogged this on Fasai's Blog and commented:
Milestone in the excel chart is useful.
It’s wonderful.
HieuHoTrung
(hieuhotrung@gmail.com)
This is great! Thanks for sharing! 🙂
Amazing….Just Amazing
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?
Hi,
Which software are you using on the Mac? Excel or Numbers?
Excel 2011
Can you download the file? You should be able to convert the Windows 2010 version and open it directly with Mac 2011
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.
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?
Very helpful, thanks! I was able to tweak this to have days and months instead of months and years.
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…)
For a vertical timeline, try using drop lines instead of a line chart…
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?
Diego
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.
Thanks!
Nevermind! I hadn’t realised that I also need to include a value in column E. Duh! Thank you anyway =)
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.
You just need to space out the labels. It’s a little difficult, but possible
Thank you for your post. It is very useful for me. I am trying to make one and practicing as your guide.
Thanks very useful !
Thank you! This was perfect!
Excelent guide! I used it in Excel 2010. Thank you a lot!