Welcome to the Surten Excel blog.  For the first topic I have chosen timelines.  Very often when studying an event to find out why and how it happened, a time ordered graph of what happened just before the event can lead directly to an understanding of the causes.  Indeed, graphical timelines can yield very powerful insights into what is going on and should always be constructed when you are looking for the causes of events.

I have used a number of specialized programs for creating timelines, some of which create excellent timelines.  However, you can easily automate a spreadsheet to quickly create a perfectly adequate timeline.  Some of the spreadsheets I have used before produced nice-looking and informative timelines;  however, the formulas used to tie the data to the graph were a bit complex and not very tolerant of insertions or deletions of entire rows.  This made it somewhat difficult to use an older timeline as a template for a new one or to add new data quickly to an existing timeline.

Accordingly, I set out to use the table features of Excel 2007 – 2013 along with some of the techniques I picked up from Excel Hero Academy to see if I could improve on these results.  You can download the example file described in the article here:

http://www.surtenexcel.com/wp-content/uploads/2012/10/TimelineTemplate.xlsm

The date and comment text live in an Excel table with a third, calculated column to set the comment distance from a central time axis.  The chart uses error bars to point to the comments.  In the calculated column, the Index function uses the row in the table to select a value from a Named Array Formula constant.  Whenever the data in the table changes, a workbook event fires that temporarily suppresses error messages, forces the worksheet to perform a full recalculation so that the Index function formulas update, and resets the data labels to any new values.  Named Formulas that use the Indirect function to get the x and y values from the table supply the data for the chart series.