Search code examples
excelexcel-formulaexcel-charts

Which chart type counts the amount of timestamp instances inside a column?

Last week, I wanted to count the amount of times an exception occured in a logfile.
Therefore, I had done a grep "Exception" in my file and used an awk -F "." '{print $1}' to show only the timestamps, the result looked as follows ("Tijdstip" is Dutch for "timestamp"):

Tijdstip
07:18:24
07:18:24
07:25:10
07:25:10
07:25:31
07:34:18
07:34:18
07:34:18
...

Then, I wanted to show the amount of times that timestamp was shown, which I did using a helper column, containing the formula =COUNTIF(A$2:A$3013,A2).

This led to the following chart ("Aantal" is the name of the helper column):

enter image description here

The information on the top was provided, dragging my mouse above the top point, showing that information as a tooltip. This was indeed what I was looking for and it solved my problem.

This morning, I thought by myself "Jezus, man, using a CountIF() formula with a mixture of absolute and relative references, that's quite complicated. There should be an easier way to obtain this result.", but after some tests with other chart types (column, histogram, ..., even pivotchart), I did not find one chart type which can select a group of entries, count them and show them and their frequency in a clear way.

Am I right or is there a chart type which covers this?

Edit after first comment:
There seems to be an easy solution, but Excel messes it up, trying to analyse the values and turning them into hours and minutes, and perform the count on this, as you can see in the following screenshot:

enter image description here

Solution

  • To sum up the proposed solution:

    You can use the Pivot table (and Pivot chart) feature where you would apply the timestamp column for both fields:

    • rows and values, and choose to count as aggregation.

    In order to adjust the way Excel handles date and time you may want to group/ungroup the components of the timestamp to match with your requirements (reference links see also in the comments).

    Extra remark:
    At first, the pivottable might look as follows (grouping the timestamps per hour):

    enter image description here

    Ungrouping can be done, right-clicking in the first column:

    enter image description here

    This results in the following correct result:

    enter image description here