How do I create a timeline chart in Excel?

Question

How do I create a timeline chat which visualizes peoples activities throughout a day in one graph? On the x-axis I plan to place the time. And the y-Axis would be the different persons. First, I thought of a simple stacked bar chart. However, the problem is that Excel would combine all the same activities to one item and don't visualize it on the timeline... I also can't do it by hand as I have several 1000 entries to visualize...

I found this post on Stackoverflow, but there there is only one activity per entry and not mulitple times the same...

It would be great if I could use Excel (as my data set is there). But if there is a better way (i.e. tool) I'd also appreciate new suggestions.

This is what I imagine the timeline to look like:

Solution 1

As I didn't find a solution that let me create a timeline in Excel, I used JavaScript and the visualization framework D3, together with a neat opensource library, d3-timeline: https://github.com/jiahuang/d3-timeline

Solution 2

If you have data prepared in the correct way this can be easily done in Excel. Check the animation below (you can open image in a new tab to see the full resolution).

Comments

[casaout]

hi Mitja. Thanks. Unfortunately, this would not work as a developer works multiple times in the code for example. This should not be visualized as one bar-part (one colored block), but correctly throughout the time axis. Please see my screenshot. Any ideas on this? Thanks!

[Mitja Bezenšek]

Ah, I misunderstood your question. You won't be able to do it with just inserting an existing Excel chart. But with some vba code it is possible for sure. Lets say there are maximum 20 activites a person does in a day. You could then insert a stacked column chart with 20 series. If a person only did 5 activities then the values for the 20 series would be 0 from 6th series to the last. Then you would need some code that would color each data point of all those series with the correct color. With this approach you would probably need to manually generate legend for the chart.

[casaout]

Hi Mitja. Thank you for your comment! That's what I feared ... Is it difficult to learn VBA in a very short time? Each person has about 200-400 activities a day. Could this be too much data or could a VBA script still do that? Creating the legend by hand might be my smallest problem ;). If I could get everything else done somehow, I'll be super happy :) Thank you for your help!!

[Mitja Bezenšek]

If you have any prior knowledge of programming it should go fast. You would mostly need to learn the way to access and manipulate the excel objects (how to add a chart, how to add a series to the chart, how to read the cells,...). Even starting with no prior experience with programming is not so daunting since vba is quite widely used and many tutorials exist. I think vba should easily handle the amount of data you have. I have not yet made a chart with so much data but I think it should be fine. However with 200 stacked columns you would need a really big chart to see what's going on.

[casaout]

hi Mitja. Thanks for your help! I now looked at some javascript visualization frameworks, including D3. I think, I'll do it with D3. Then, it could look like this: bl.ocks.org/dk8996/5538271 Thanks again!