Intraday Time Series Charts

Today’s Author: Scott Ruble, a lead program manager on the Excel team who focuses on the area of data visualization.  Scott is going to discuss how to create an intraday time series chart. 

Periodically, users need to create a chart where the data occurs within a single day such as by the minute or hour.  This is actually fairly easy to do but unfortunately isn’t very obvious.  A typical scenario is you own a restaurant that takes phone orders and you want to reduce the wait time for customers placing an order.  This is influenced by a number of factors but a big one is the number of people calling at the same time.  As such, you would want to staff your phone lines with more people during the busy times.  The key here is to determine when the busy times are and by how much.

With this restaurant scenario, we’ll use the following data set.  To keep things simple, I’m only showing data from 11:00 AM to 4:30 PM which is the busiest time for this restaurant.  The data reflects how many people are waiting on the phone during this time frame.  Also, notice that there are gaps in time where no one is waiting.

Sample Data

First, I’d like to illustrate a few common mistakes that people make.  You may first be inclined to create the ever popular line chart shown below.   However, upon closer inspection, you will notice that this chart isn’t really reflecting the true nature of the data.  There was no one waiting between 1:12 PM to 3:59 PM.   However, this chart shows the data markers uniformly spaced even though the times are significantly different (highlighted by the red markers).  This is because the default axis for a line chart is a category axis which uses an equal-interval scale. 

Determined to not be blocked by this inherent limitation of category axes, your next thought may be to change the axis type to a “Date axis” located in the axis formatting dialog.

As shown by the resulting chart, this unfortunately won’t get you very far either.  All of the data is collapsed into a single day.  For a date axis, the minimum unit is a day.

The key to creating a chart that reflects units smaller than a day (such as hours or minutes) is to use an XY (Scatter) chart.  This works properly because the horizontal axis for an XY (Scatter) chart is a value axis and time is encoded internally as a serial number.  So with that, here are the specific steps to create a time series chart that reflects the true span of time.

Steps

1) Select the data and in Excel 2007, click on the “Insert” tab in the ribbon.
2) Within the “Charts” group in the ribbon, click on the “Scatter” drop-down icon.
3)  Select the chart type: “Scatter with Straight Lines and Markers.” 
4) Select and delete the legend. 

This will produce a chart like the following.  We aren’t quite done because the chart is scrunched in the plot area. 

To make the chart fill the entire plot area, you need to explicitly set the start and end points of the horizontal axis. 

5) Right click on the horizontal axis and select “Format Axis”.
6) As shown in the following dialog, click on the “Fixed” radio button for the “Minimum” and enter “0.45833”.
7) Click on the “Fixed” radio button for the “Maximum” and enter “0.70833”.

Excel allows you to specify time values for the axis options.  Time values are a percentage of a 24-hour day.  As such, 11:00 AM is represented as 11/24 = 0.45833.  For the maximum value, I’ve rounded this up to 5:00 PM which corresponds to 17 in military time (17/24 = 0.70833).  These settings will produce a chart like the following.  As you can see, the shape of the scatter chart is very different from that produced by just a line chart.

Those of you familiar with this technique of converting time to a decimal may recall that Excel 2003 allowed you to enter a date and time like “1/1/07 11:00 AM” directly in the  axis option min/max fields and Excel would calculate the appropriate decimal representation.  This currently does not work in Excel 2007 but will be fixed in a subsequent release. 

In summary, whenever you need to create a time series chart where the units are smaller than a day, avoid using line, column, bar, or area chart types.  Instead use an XY (Scatter) chart to accurately reflect the span of time.