Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list of date/timestamps, and I would like to plot these to a chart in Excel. What I'm looking for is a chart that shows "events per hour" or something similar. Can this be done easily if I just have one column of data (the list of timestamps)? I'm using excel 2007 and looking at two data sets. One is 56k events and the other 750, both over the span of a few days.

share|improve this question
    
Did you mean 750 or 750k? –  Toc Apr 11 '10 at 10:48
    
I meant 750, thanks. It's a subset of the other data. –  Segfault Apr 16 '10 at 16:52
add comment

3 Answers

up vote 5 down vote accepted

Give your column of timestamps a name, in the first row, say "Timestamp".

Now create a Pivot Table with Timestamp used twice - once in the Values area (Count) and again in the Row Labels area.

You can then select grouping on the Row Labels as days, hours, minutes, whatever.

Then select Options | PivotChart.

share|improve this answer
    
Thanks Mike, this is great. I'm just can't quite figure out how to get it to group by hours. I selected the dates in the pivot table, and the "Group Selection" button on the ribbon activated, but when i click it it just throws all of the dates into "Group 1" and there is no Group By dialog or options anywhere. –  Segfault Apr 16 '10 at 16:51
1  
Don't select ALL the dates. Just have one of the dates selected before you click on Group Selection. Also, if your data spans days and you want to differentiate them, you should select group by days as well as hours. –  Mike Fitzpatrick Apr 17 '10 at 1:08
    
perfect thanks! –  Segfault Apr 19 '10 at 17:08
add comment

Several thoughts -

First, search histogram in Excel's built-in help. You should have the ability to install the Data Analysis add-in which has a function to do just that.

Otherwise, if you're willing to shell out the $30 - Addins.com has an addin for this purpose. (I have not used it so I can't say what is better or worse compared to the built-in functions.)

Either way - you may run into trouble with charting limitations (I'm not sure what 2007's limits are, but 2003 only will allow 65k rows of data and other restrictions on points in a chart).

Finally, a plug for 'R' - this software could very simply import arbitrarily large data sets and give you histograms with the data you're looking for. As an added bonus, it's open-source freeware and can import many data file formats. See 'R' at cran.r-project.org

share|improve this answer
    
Thanks for the advice JDB. –  Segfault Apr 9 '10 at 20:51
add comment

Make a pivot table, group date field by hour as explained in http://www.contextures.com/xlpivot07.html, then insert a chart based on pivot table.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.