Friday Challenge Answers – Cumulative Events Over Time

In our recent Friday Challenge,

Friday Challenge – Creating an Excel Graph of Cumulative Events Over Time

where a user wanted the following:


Creating a graph of cumulative events over time

I currently have a table of various events that occurred and the associated date. How can I go about plotting a graph of the cumulative incidence of specific events over time?

For example my table looks something like this:

1 6/4/2014 A
2 6/25/2014 B
3 7/1/2014 C
4 7/4/2014 A
5 7/4/2014 A
6 7/4/2014 B
7 8/8/2014 C

How do I create separated line graphs of the cumulative incidences of A, B and C over time ?


I received several great submissions.

This one from Crazy Dog 64 is the exact chart that I created when first trying to solve this problem.  Here is what their final chart looked like:CrazyDogsSolution

Only issue with this chart versus the original request is that the line progresses from Zero to 1 in a sloped/trend type of format, but the incidents were zero right up until the first incident.  So I feel that this chart should be more of a step type chart, but this is a really close solution.

You can learn more about step charts here:

Understanding How-to Make a Step Chart in Excel Using Index and Match Functions

How-To Create an Excel Step Chart Formula Using the Small Function

How-to Create a Step Chart in Excel with 3 Quick Steps

How-to Easily Create a Step Chart in Excel


I also receive this great submission from Magrifa:

Magrifa-s Solution

This is a real great visual for incidents by date.  Only issue that I see is that the user wanted a line chart to show the data and that they wanted cumulative incidents by date.  This shows number of incidents, not a cumulative value, but I think that I like really this graphic representation of the data.  What do you think?

I believe that Magrifa plotted the data points in a line graph, added labels and then hid the line.  Then Magrifa used drop lines to connect the data to the date in the horizontal access.  Great job Magrifa!

Next I also received the following solution from Piotr from Poland:


I too like this format.  Piotr is representing the data in a Step-Area type of format.  I think that he is using a column chart and closing the gap.  Also, Piotr has stacked the charts on top of each other in a Vertical Panel chart type format.  Only issue is that this isn’t the line chart that the user requested, but I also really like this one too.  Thanks Piotr!

If you want to learn more about panel charts, check out this post:

How-to Make a Wall Street Journal Horizontal Panel Chart in Excel


Finally, here is the solution that I created:

creating a graph over time of cumulative events

It represents the data in a line chart and it is also cumulative data.  It looks like a step chart but I had to significantly change the formulas so that would create a cumulative value of all incidents by type.  Check out the download file to see how the formula was updated for these issues.  I of course am most partial to my version, but I want to know what you think?  Which chart do you like best?  Let me know in the comments below!


Video Demonstration:


Free Excel Sample Download File: