Excel – Generate event distribution chart in Excel

chartsdate timedistributionmicrosoft excel

I have a single column in Excel with lots of date/time stamps (in UTC) of certain events, like this: 

2014-02-20 23:04:57.9263690 +00:00
2014-02-20 23:05:02.3117944 +00:00
2014-02-20 23:05:02.7490564 +00:00

I'd like to generate a distribution graph of these events against the time.
So the X-axis should indicate the time, while the Y-axis shows how often these events happened. How can this be done in Excel?

Best Answer

Excel can do this easily. Here's one method:

  1. Convert your data into an Excel Table

  2. Convert your data into a Date/Time format that Excel can understand. To do this add a second column to your Table and use this formula (assuming your data Table starts in A1)

    =DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID([@Column1],12,8))

  3. Apply a Custom Format to Column 2, mm/dd/yyyy hh:mm:ss.ss
  4. Create a Pivot Table from your data Table, using the following values:
    • Row Labels: Column 2
    • Values: Column 1 (Count)
  5. Group your data by any date/time level you like, from seconds up to years.
  6. Create a Pivot Chart from your Pivot Table.

Now you have a chart that displays the count of your events, based upon whatever level grouping you prefer. As you update your data Table (or better yet, connect it to your data source for automatic updates), you can refresh your Pivot Table and Pivot Chart for current information.

EDIT:

The solution doesn't require a table, but Excel's use of the Table simplifies both the additional column maintenance and the updating/refreshing of the linked Pivot's. Also, the Table provides a great path towards connecting a live data source (e.g. linking a SQL table).

The Custom format isn't necessary, but converting the value into an "Excel friendly" format is. The Custom format just allows you to see as much of the original data as possible, beyond any of Excel's built-in formats.

Related Question