Very basic - how to display call log data? Starting with time of calls.

I used to know a good bit about Excel, but that knowledge has slowly slipped away it seems. Any help is appreciated.

I've got Excel spreadsheets of the system log files from our telephone PBX system. I understand them, but they're rather incomprehensible to my co-workers in the row/column format they're in. So I need to display call data in a graphical format to show trends and patterns with calls. I've forgotten everything I might have known about graphs in Excel, and I had to do a Google search for "what is a pivot table", just to let you know where I am with this. (Last I used Excel it was version 2007. I've got Excel 2016 now.)

I'd like to end up with graphs that show what extensions were dialed, what calls went to voice mail, incoming and outgoing calls (and multiple calls by the same number), etc., but let me start out with some basic data. These are the times our phone system handled calls on a Sunday (we're closed Sundays, so this will be a small data set).

Time
8:24
9:52
10:08
10:29
10:36
10:47
11:36
11:59
13:27
14:33
15:09
17:13
19:17
19:26
19:35
20:42

At each one of those times a unique call came in to our system (I've already filtered for transfers to different extensions, transfers to voice mail, and similar which add their own time entries and seemingly create additional "calls" but are actually part of the same transaction - if that makes sense - there's another field in the data that links the parts of a call through the initiation through transfers to the end person hanging up the phone using a unique number). I've left off the rest of the data for now, such as the extension it went to, whether it was incoming or outgoing, how long the call was, and so on. I hope to one day understand how to represent all of that on a series of graphs.

How would I display this graphically? I'd like to show some type of range of when the calls came in. Whenever I try to make a graph with just this data, it pulls the row number of the entry into the chart as another list of values and ends up as nonsense on a graph. What format of graph would best show when the bulk of calls happen? Pie chart? Line graph? X/Y pattern thing? It's binary data - a call happened or it didn't, so I don't really have another data set I want to use with the times a call appeared in our system (until I understand this better).

I'm hoping this will click after my morning coffee, but I've been staring at this data all this week and I'm stuck. And embarrassed. Programming a PBX, no problem. Explaining it to humans...er, not so hot.

BPB

If you split the day up into hourly segments, then you can count the number of calls for each hour. In your example data you have 4 calls within the hour from 10:00 to 10:59, and 2 calls in the next hour, and so on. You can achieve this summary table using a COUNTIFS function.

Then you could graph this table as a simple histogram (bar chart), with the hours going across and the counts on the Y axis, which would tend to give a double-hump curve with slightly fewer calls over lunch.

There are many other graphs that you could produce from call data.

Hope this helps.

Pete

You are going to chart this in 2 dimensions (3 is cool, but really unreadable on a 2D screen)
So you will want to pick your items for X and Y axis,:
like: 1 Extension on 1Day , X=Time, Y = Call result count
or: 1Day ,X = Extension, Y= CallCount

then use a chart wizard to select these columns to chart.

Olly - yes, this. Let me check out that sample you attached. The following Monday had 743 calls throughout the day, which is much more typical, so it would have to be automated graphing in some part. I suppose that's what the pivot table does.

I used to analyse outgoing call data for my clients before I retired. I've got rid of most of the files from my PC, but I managed to find one from some years ago (2004), so I've turned that into a pdf and attached. This shows a table and several graphs that were produced automatically using formulae - I had it set up so you just put the new call data in and adjusted the named ranges. There are various other tables and charts that were produced by the template, but I hope this gives you some ideas of what can be produced.

Hope this helps.

Pete

Thank you all for the info! Pete_UK - those are the type reports I want to get to, once I expand my knowledge of pivot tables/charts/etc. As for now, Olly - that's my ideal starting point, what you provided. I don't know how to go about doing that, but I'm working on it.
Let me "sanitize" the data to remove and personally identifiable information from it and I'll upload a sample of the full data. It's very similar (but not quite) to what Pete_UK has in his .pdf (but not exactly).
Thank you very much all! I'm extremely grateful!
BPB

I've just had a quick look at this before dinner. It would help if you split the date and time into different columns. You can do this quite easily by inserting two new columns, A and B before your current column A, and then you can use this formula in A2 to get the date only:

=INT(C2)

format as a date. Then in B2 you can use this:

=C2-A2

format as time, then copy both formulae down. You can then fix the values and then delete column C.

I'll be going out after dinner, but hope this helps for now.

Pete

I don't know why I was so dense last week; these are syslog messages so of course they're uniform length in the fields exported. D'oh. Anyway, they're properly sorted now and hopefully I'm on the way to figuring out pivot tables. I'm just not sure how to sort them without mixing up the order of data across multiple columns.
But, a big thanks to this forum for getting me going; I probably would have still been struggling with ordering my data but for this advice! Perhaps some Excel knowledge is coming back to me.