How to make a chart that graphs frequency of entries?

Mike Fitzpatrick's answer will work well, if you're trying to count all values for a month-regardless of year. If you're looking to have a chart of counts for each month by year (i.e. able to differentiate between November 2012 and November 2013), you'll need a little more detail.

  1. Create a standard Table from your existing data.
  2. Add a column, named Year, with the formula =Year(A2).
  3. Add a column, named Month, with the forumla =Month(A2).
  4. In your Pivot Table, use Year and Month as your Row Labels, and DateTime as your Values (still with Count).
  5. Your Pivot Table will now differentiate the same Month across different years.
  6. Finally, with the Pivot Table selected, Insert Column Chart to create a Pivot Chart with your monthly counts.

By using a standard Table for your raw data, as you add values, your Pivot Table will update as will your Pivot Chart.


Assuming your data are valid Excel datetimes, give your range a title (eg. "Datetime") and then create a Pivot Table.

Use Datetime (or whatever you called it) as your Row Labels and your Values. Make sure the Values function is Count, not Sum.

Finally, group your Row Labels by Month and optionally by Year.


I noticed that in your sample data, you actually have a relatively large time span with irregular time intervals. While other answers pointed out how you could make a chart in Excel alone, here I propose another solution that could make an interactive line chart that addresses the problems that I mentioned. The pic below shows you the example that I made based on your description.

enter image description here

I made this chart with the Funfun Excel add-in. Basically, this add-in allows you to use JavaScript directly in Excel so you could make use of libraries like HighCharts.js or D3.js to make interactive charts. I used HighCharts.js in this specific example. In this chart, you could use your mouse to zoom in a specific span of the time to view detail numbers. This would be very useful if you have logs for years.

Back to your data. Before making this chart, you do need to count the frequency for each month. This could be done by writing a small function in JavaScript. Here is some sample code.

var frequencyPerMonth = new Object();

  var monthLast = 1;
  var yearLast = 1970;
  for(var i=0;i<data.length;i++){
    var monthThis = parseInt(data[i][0].split('/')[0]-1);
    var yearThis = parseInt(data[i][0].split('/')[2]);

    if(monthThis == monthLast && yearThis == yearLast){
        frequencyPerMonth[data[i][0].split('/')[2]+monthThis.toString()] += 1;
    }
    else{
        frequencyPerMonth[data[i][0].split('/')[2]+monthThis.toString()] = 1;
    }
    monthLast = monthThis;
    yearLast = yearThis;
  }

The Funfun also has an online editor in which you could explore your JavaScript code and result. You could check the detail of how I made the example chart in the link below.

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

Once you are satisfied with the result you achieved in the online editor, you could easily load the result into your Excel using the URL above. But of course, first, you need to add the Funfun add-in into your Excel by Insert - Office Add-ins. Here are some screenshots showing how you load the example into you Excel.

enter image description here

enter image description here

Disclosure: I'm a developer of Funfun