2d ago

Looking for a way to make a proportional timeline out of an Excel or CSV file

Simple concept, say I have two columns. The first contains a time or date, and the next a word or two describing an event.

    
1970, born
1987, graduated highschool
1991, graduated college

Does there exist a program, or a feature within a program like Excel, that takes this data and spits out a timeline where the dates are spaced proportionally and not just evenly spaced? Something like this:

    
                                          1991
                                          Graduated college
+----------------------------------+------+
1970                               1987
born                               graduated high school

The best solution I've found, at least in Excel, is to treat the dates like a histogram or scatter plot so the dots show up in the right place, then add labels after.

4 comments

What you’ve found is probably sufficient if you have a small amount of data or only need this type of display rarely.
Another approach to solving this would be to create a date table listing all of the date units between the start and end of your timeline and represent those as having no value. When you add your values to this data set you ensure proportional spacing, as every date unit in the set will be represented. This type of work is much easier to do using Power Query, if you have access to that Excel add in.
Is there a specific format you want it in at the end? That's a relatively easy python script and would probably produce a clearer timeline than Excel if all you want is an image or something at the end.
I'm not where I can get to a computer, but as I recall you can do this with a line graph by opening the formatting option on the X axis and setting it to be a date.
I would do something similar to what you did: create a scatter plot using the years as your x-axis to make the spacing between them proportional, hide the y-axis, but also add labels to the year and event columns whose data you are using for your chart so that Excel can automatically update the data labels to the chart in case you change or add any events.