8

I have an Excel workbook that has nothing but dates (sorted), in the following manner:

11/14/2011 11:53
10/14/2012 11:53
11/23/2012 19:28
12/24/2012 12:27
...
12/1/2013 11:48

I would like to create a line chart that tells me how many entries there are in each month. How do I pull this off?

3 Answers 3

6

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.

1
  • +1. Updated my answer to include grouping by year, if required. Sep 10, 2013 at 1:52
5

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.

2
  • 1
    In Excel 2010, that group option is so hard to notice that I had to triple check the different locations, drop-downs and what not before I found it in the Options ribbon and only active when you click on the actual table column. Jul 22, 2014 at 2:24
  • Right click right on the dates you want to group, and one of the items in the pop-up menu is Group. Jan 21, 2016 at 19:21
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .