# 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

 Time Number of Customers Waiting 11:04 AM 2 11:15 AM 4 11:16 AM 3 11:22 AM 3 11:36 AM 2 11:44 AM 5 11:45 AM 6 11:46 AM 6 11:50 AM 4 11:51 AM 5 12:01 PM 9 12:04 PM 9 12:05 PM 8 12:06 PM 8 12:07 PM 10 12:08 PM 8 12:09 PM 7 12:10 PM 8 12:11 PM 9 12:12 PM 6 12:13 PM 6 12:14 PM 5 12:15 PM 6 12:16 PM 4 12:19 PM 3 12:20 PM 3 12:25 PM 2 12:40 PM 1 12:45 PM 3 12:55 PM 2 1:01 PM 4 1:02 PM 2 1:11 PM 2 3:11 PM 1 4:00 PM 3 4:20 PM 2 4:23 PM 5 4:25 PM 7 4:28 PM 5 4:29 PM 8 4:30 PM 6

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.

### Office Blogs Comments

Comments: (21) Collapse

• Patty,

If I understand you correctly, you are referring to the actual chart in this blog.  We use a blog tool that takes care of creating the hyperlink.  As far as I know, PDF isn't involved.  If I'm still missing your point, please post your email address and I'll correspond directly with you.

• Yes Scott that is what I was talking about. Thank you.

• If both columns in the above example are in the format h:mm then selecting an X Y Scatter chart yields a two series line graph.  Why?

• Is there a reason why you would use a time format on the second column?  The “number of customers waiting” isn’t a time unit.  As for why there are two series generated, the heuristics for determining the series don’t account for this data structure.

• "For the next release of Excel, we are focusing on some of the primary concerns expressed by customers"

You certainly  would not divulge the efforts at Microsoft, but may you list the primary concerns?

My primary concern is units of measure.

A very close second are sheets that are individually placeable on a page and are of limited size - instead of automatically being X columns by Y rows, they are only as big as the data they hold. This way a single page could have multiple work sheets.

What would make this complete is to have the column and row names be changeable so that one could create a statement like "=total(sales!extended)" where 'sales' is a sheet and 'extended' is a column on that sheet that is titled 'extended' where the user can see it.

With units, some values could be dollars, others yen, others euros and the total would be whatever currency format the user asked for. If one of the entries does not have a currency unit, the total would fail. Certainly for currency the conversions are not static and would require a means to update them, but for many other units - metric vs imperial, the units are static.

• @Scott Ruble

I believe the point that Harlan was making about the functional relation is that the lines do not represent the change with time between the points. Change could be very irregular, but the lines make the change appear linear.

A column chart would better represent the data, unless column charts don't yet support irregular data.

I suggest a New Year's copy of Edward Tufte's work "The Visual Display of Quantitative Information." It's a must-read for anyone who wants to present readily understood information.

Comments: (loading) Collapse