Timeline chart in Excel

Question

I'd like to see a timeline of events from a database in a "timeline chart", that should look like this:

I've made me a small c# program that paints this onto a Bitmap, but that isn't the way to go.

I have input data that has 3 fields:

StartX  EndX    Y
2596    15008   1
5438    6783    2
5450    5453    4
5456    5459    4
5462    5466    4
5470    5474    4
5477    5657    5
5662    5665    4
5668    5671    4

As the picture shows, for each line I'd like to have a line from StartX to EndX with a Y value of Y. Stacked bar chart almost solves my problem, but I don't want to have a new line on the chart for every row, I have thousands of rows and I'd like to have X axis as the time axis, and view which events (Y is the type of the event) happened simultaneously. The image (see above) I've generated with a simple C# program shows that the event SYSTEM was active all the time, and the events TECH and BREAK were almost exclusive, but had some overlaps.

I'd like to at least know the correct direction which I should take; I'm lost in the multitude of Excel chart types.

Answer 1

The only way I've found to accomplish this is to use stacked bar charts where the blank time in between actual events is a series that is set to a blank fill. This method also requires helper columns to assist in feeding the data that the stacked bar chart expects. This method would need a table with as many rows as Y values. This makes it tough since the requirement about new line for every row seems to indicate you want to leave your data in the format it currently uses.

Other thoughts: Have you considered perhaps a charting component to the c# program instead of trying to force Excel to make the graph? Microsoft's Chart Control is a clone of Dundas and is free.

Answer 2

Timeline charts like grantt charts are pretty hard to make in native excel, but if you know your way around programming language like C#, you may easily make a chart using javascript. This language comes with a lot of different data visualization libraries giving you a lot of features for your chart.

I made this chart using javascript, with the data you uploaded in your question:

https://www.funfun.io/1/#/edit/5a5732ea8dfd67466879ebbb

I use a Json file to get the information from the embedded spreadsheet as such:

{ "data": "=A1:C10" }

After having access to the data I create my chart with a javascript chart library, here I used Google Charts.

Once you created your timeline chart, you can directly load it by pasting the URL in the Funfun add-in. Here is how it looks like:

This add-in makes it easier to create complex charts in excel. I hope it helps.

Disclosure : I’m a developer of Funfun.