You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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
11:36 AM
11:44 AM
5
11:45 AM
6
11:46 AM
11:50 AM
11:51 AM
12:01 PM
9
12:04 PM
12:05 PM
8
12:06 PM
12:07 PM
10
12:08 PM
12:09 PM
7
12:10 PM
12:11 PM
12:12 PM
12:13 PM
12:14 PM
12:15 PM
12:16 PM
12:19 PM
12:20 PM
12:25 PM
12:40 PM
1
12:45 PM
12:55 PM
1:01 PM
1:02 PM
1:11 PM
3:11 PM
4:00 PM
4:20 PM
4:23 PM
4:25 PM
4:28 PM
4:29 PM
4:30 PM
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.
Comments: (21) Collapse
Sometimes the chart view can be aided by using 100% Y Error Bars to see the height of the lines better. I would make the line a faint grey color to compensate for adding a chart element.
Using a horizontal bar as the data point marker may also help.
Quote:
<>
Connecting the data points in this case gives erroneous information.
Actually, connecting the data points is never a good idea with this sort of chart. There's no functional relationship between the number of phone orders at, say, 11:30 and 11:31. They're separate data points. They may be correlated, but that's a different concept.
As for the x axis, a wee bit of spreadsheet USER (as opposed to developer) experience would recognize that times are bins. In this example, the bins are single minutes. Use a chart range that includes EACH & EVERY minute from 11:00 AM to 4:30 PM (or whatever), and use formulas to pull in the number of callers in each minute, with no callers represented either as text or #N/A. Then a simple line chart using that derived range would generate the final chart above.
Since Office 2007's charting engine is apparently too stupid to interpret dates/times, and since it appears it may take as long for this to be fixed as it took for Excel to get colored worksheet tabs (9 years from Quattro Pro 5 introducing them to Excel 2002 providing them), it's a lot easier adjusting the Line Chart's x axis than screwing around with scatter chart settings using date/time serial numbers.
Are low information content/negligible USER experience content really the ideal filler for this blog?
BTW, wouldn't it be a better use of the Excel developer team's time to fix date/time interpretation than writing low information content blog postings?
Thanks for the feedback. Sorry to hear that this topic isn’t one that is helpful for you. Please keep in mind that Excel has a very large user base which we consider when selecting topics for the blog. We have gotten direct feedback that these “help” articles are beneficial to a wide variety of users - though not each one will appeal to every person.
I’m not sure I understand your comment about no functional relationship between the number of calls and time. The number of calls (Y axis) is a function of time (x axis). At least that has been my experience whenever I wait too long to order takeout. The call volume is also influenced by other time related aspects such as the day of week, the season, and special events like a football game letting out. As for the difference between 11:30 and 11:31 this isn’t an important aspect given the intent of the analysis; e.g. staffing the phone line with enough people for the entire day. The purpose of the chart was to help expose general patterns of call volume activity like peaks and valleys which is a classic use of a line chart.
It is possible to achieve the same chart using the approach that you described by inserting #N/A and using a normal line chart. However, this process can be very time consuming, error prone, and laborious for large data sets. Depending on your units of measure (minutes, seconds), you may need to insert hundreds or thousands of #N/As at irregular intervals to ensure a uniform sequential time series.
Picky: while the size of the queue varies over time, that doesn't mean the data points are functionally related. It's the means of the underlying counting process distributions in sequential time periods may be functionally related.
Your chart, with lines between each point, implies there's a functional relationship between each pair of points, but if there's any significance to the different number of calls at, say, 11:44 and 11:45, there'd also be some significance to zero calls at 11:43.
Anyway, this is a situation in which a TREND LINE not necessarily running through ANY of the data points would be MUCH MORE MEANINGFUL than connecting the data points. Excel 2007 does still provide trend lines? If you're the data visualization guru, you do have some idea when and how to use them?
Back to Line charts. They allow one to group the data using the category axis scale settings
-Number of categories between tick-mark labels
-Number of categories between tick marks
Myself, I'd set the former to 30 and the latter to 15, so labels every 30 minutes but tick marks every 15. Nice simple numbers of minutes. Less typing than for scatter charts even using Excel 2003, which can interpret date and time entries as chart axis scale settings.
As for my setup being time consuming, name the original data range Data. Define the name hm as =1/24/60/2. In another worksheet enter 11:00 AM in A5, the formula =A5+"00:01:00" in A6, and fill A6 down as far as needed. Enter the formula
=IF(ABS(VLOOKUP(A5+hm,Data,1)-A5)
Harlan, you are reading a wrong blog :) Go here: www.dailydoseofexcel.com
how do i get real-time data into excel. is it still via COM? DDE? please tell me you have something better.
oh by the way excel 2007 is rubbish. i have been using excel for 6 years and this latest version is a real step back.
what has happened to microsoft? if you want stuff to work on, heres an idea - make it faster. vista takes 20 minutes to boot up and my life is too short.
We've beefed up our support for bringing data into Excel from databases and file based data sources(with new top-level connection objects, and improved support for OLAP data) but we haven't made any changes or improvements related to bringing in real-time data in Excel 2007.
When will Microsoft provide us with a proper charting engine? What are the plans for excel 2010? Are we getting a better chart engine or what? We appreciate the work done in excel 2007 but it is grossly inadequate. What is currently available is shocking, the charts are misleading (“3D” charts), full of chartjunk (all excel charts) some are outright nonsensical e.g. the pyramid charts.
And why must we be charting heroes to use the damn charts?! We want charts that work not charts that need some complicated workarounds!!! When are getting true 3 D charts, dashboard charts, water fall charts, etc?
When I am looking at your chart with the two red markers I was wondering how you got the data points to hyperlink? Is that an option in excel or ???
I want to create a chart and have it drill down to more charts / data. Any ideas?
I hope you heave ideas for the question of Patty, because I'm looking for this solution to.
T. Kwetane,
There are many things in the works at Microsoft but I can't comment at this time on the specific details. For the next release of Excel, we are focusing on some of the primary concerns expressed by customers.
Patty,
In my example, there isn't a hyperlink on the red markers. However, there are a few different ways you can hyperlink to other charts/worksheets or drill on a chart.
Option 1
•Excel 2007 doesn’t allow you put a hyperlink on individual chart elements such as a data point. However, it does allow you to put a hyperlink on the entire chart area. Here are the steps:
1. Click on the chart area
2. Click on the Insert tab in the ribbon
3. Click on the hyperlink button in the Links group
4. Enter the relevant address and click OK.
•Now when you click on the chart, you will be navigated to the address specified. This approach is ok for when you want to provide supplementary information about a chart or provide a link to data that is in another workbook. To further edit the chart, you will need to remove the hyperlink because the hyperlink prevents you from clicking inside the chart. To remove the hyperlink, ctrl+click on the chart and then click on the hyperlink button in step 3 above to remove the link.
Option 2
•As noted above, Excel 2007 doesn’t allow you to put a hyperlink on individual chart elements. However, you can insert a shape or textbox and put a hyperlink on those items. For example, you could put a textbox label next to a data point or put the shape right on top of the data point with a hyperlink. The steps for this are largely the same as option 1, except the shape is the item clicked on in step 1. Keep in mind; however, that the inserted shape or textbox are not anchored to the data point. If the data changes, the shape or textbox won’t move. This can be addressed through VBA but I don't have an example readily available at this time.
Option 3
•There are several other options called out in Jon Peltier’s article on hyperlinks: peltiertech.com/.../Hyperlinks.html
Option 4
•To drill down on a chart, you can create a PivotChart with hierarchical data which provides similar drilling capability as PivotTables.
Thanks for the information. Yes, I know how to hyperlink but I was wonderful about the above graph? Do you produce the link in pdf?