Plot Two Time Series With Different Dates
by Jon Peltier
Thursday, January 29th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
A reader named Felix posted a comment on the blog, asking how he could make a line chart which has two time series, in which each time series has data for different dates. What makes his question more challenging is that Felix is using Excel 2007. This was such a good question, it deserves its own post.
Felix provided the following data:
I’ll describe the various techniques available in Excel 2003 and earlier, with pros and cons, and I’ll compare them to the same techniques as followed in Excel 2007.
Time Series in Excel 2003
The two time series are plotted separately below. Time series A has weekly data, but with two values omitted. Time series B has more data points, at irregular intervals, over a shorter time span.
Create the Time Series A line chart above left, copy the Time Series B data, select the chart, and use Paste Special to add the data as a new series, using the options as shown.
This illustrates a limitation with Line charts in Excel: the category labels or dates are defined by the first series. Any additional series are forced to use the same X values or labels, and if the added series has more points than the first series, those extra points are omitted.
The obvious alternative is to make an XY chart with the two data series. The X and Y values of separate series in an XY chart may be completely independent.
The data is plotted correctly, but the date axis isn’t as nice as the one we had in the line chart. A line chart lets you put a tick on the first of every month, but since the length of a month is not constant, this doesn’t work in an XY chart.
So how can we plot multiple time series on a chart with nice date labels?
The limitation that all series in a Line chart must use the same dates is not completely true. All series on the primary axis use the dates for the first primary axis series, while all series on the secondary axis use the dates for the first secondary axis series. Format series B in the two-series line chart so it resides on the secondary axis.
There are drawbacks to this approach. First, it ties up the secondary axis with data that otherwise fits the primary axis, so you are limited to how else you can embellish the chart. You can always hide the secondary date axis to clean up the chart, but you cannot remove it altogether. Second, the two date axes must be manually synchronized each time the data changes. Third, you are limited to two different sets of dates.
There is an alternative combination chart that gets around these limitations. Make the first series a Line chart series, so you have a nice date scale axis, then add any additional series and change them to XY type series. At first, Excel associates the XY series with the secondary axis, but you can override this setting and assign it to the primary axis, and it will use the nicely formatted date scale of the Line chart. You are not limited to two sets of dates, and you do not have the added responsibility of synchronizing the time axis scales.
But there’s an easier way.
Unfortunately this never occurs to me until I’ve already made a Line-XY combination chart. You can combine the data in the following way, putting the Series B dates below the Series A dates, and the Series B values below and one column offset from the Series A values.
You don’t even need to sort the dates, because a line chart internally sorts the dates before plotting the points, so that it connects them in date order. In contrast, an XY chart connects the points in the order they appear in the worksheet.
Select this larger data range and create your line chart. The points are plotted according to their own dates. The only problem is that, by default, Excel leaves a gap between points if there is a blank value cell between these points (below left). However, it’s easy enough to change this behavior. Go to Tools menu > Options > Chart, and in the top section, choose the option that makes Excel interpolate across the gaps (below right).
Now why didn’t I think of that before I made all those complicated charts?
Time Series in Excel 2007
Excel 2007 works much the same way as earlier versions, but there are a few notable exceptions.
In a line chart, all series use the same categories or dates as the first series, and any extra points are truncated. Just like in Excel 2003.
Two time series can be plotted together, with one on the secondary axis, and the times will be kept independent. This approach is subject to the same limitations as in Excel 2003.
You can change the second series to an XY type series, and when plotted on the secondary axis it works just fine. (Note: you can remove the secondary Y axis and both series will use the primary Y axis.)
The limitation here is that you are tying up the secondary axis with the XY series, thus limiting your ability to use the secondary axis for other tricks. You are not limited to two series, because any number of XY series can be plotted independently on the secondary axis.
In Excel 2003 we could assign the XY series to the primary axis, and it would coexist with the line chart series and use the same axis. However, when we try this in Excel 2007, we notice two flaws. First, it’s unclear what scale the XY series is using for its X values, but it’s unrelated to the primary category axis. Second, there are only as many XY series points shown as there are Line series points in the first series. Whoa! How’d that get through QA?
These glitches are not too important for this particular use of a Line-XY combination chart, but there are other uses which are completely broken by the changed behavior.
Fortunately the easiest method of all, the one which uses the combined data, works just fine in Excel 2007. The dual chart first appears with gaps, but you can change the behavior by opening the Select Data dialog, clicking on the Hidden and Empty Cells button in the bottom left corner, and selecting Connect Data Points with Line.
This is one more example of my favorite phrase: You can spend five minutes with your data, and save yourself five hours of frustration and aggravation.
Related Posts:
- Integer Values on Line Chart Category Axis
- Line-XY Combination Charts
- Clustered-Stacked-Column Combo Chart With Lines
- Order of Legend Entries in Excel Charts
- Add a Target Line
- Plot Two Time Series And Trendlines With Different Dates
- Add Percentages on the Secondary Axis
- US Employment Slump Chart – How To
- Secondary Axes that Work – Proportional Scales
- Stock Charts in Excel 2007
Posted: Thursday, January 29th, 2009 under Charting Principles.
Comments: 23
Comments
Comment from Felix Marusic
Time: Thursday, January 29, 2009, 5:34 pm
Jon,
Thanks for the excellent feedback-it works like a charm! I was totally unaware of why excel was doing this. As far as you know is there anyway you could possibly automate the creation of such a graph….for those really needy end users? When dealing with a number of series, it could get potentially tedious and be error prone.
Best Regards,
Felix M.
Comment from Jon Peltier
Time: Thursday, January 29, 2009, 5:49 pm
Felix -
Glad you like it.
As far as I know, there’s no utility readily available to generate these charts from separate ranges. It would not be a big deal to write something that would combine the ranges and create the chart.
Comment from Lester Beasley
Time: Thursday, January 29, 2009, 6:36 pm
To avoid disjointed looking graphs you might replace missing data with #N/A. Excel will interpolate missing data points.
Comment from Jon Peltier
Time: Thursday, January 29, 2009, 6:59 pm
Lester -
True, you could replace all the blanks with #N/A or with the formula =NA(), and Excel will draw lines across the gaps. But I think changing the Tools-Options setting is easier and neater.
If you’re dealing with formulas, of course, you must use NA(), because Excel will plot “” as if it is a zero. There is no way in Excel of making a formula return something that is interpreted as a blank cell.
Comment from Arnold
Time: Tuesday, February 3, 2009, 5:07 pm
Thanks as always, Jon.
1 more question, if series A is monthly data, i.e. column A only has month input like Jan. Feb,…. Can we still plot in one graph like you did?
Comment from Jon Peltier
Time: Tuesday, February 3, 2009, 10:16 pm
Arnold -
If your monthly data is to be plotted as markers, you need some kind of day to go with the month. Pick one that makes sense: the first, the last, the 15th.
Or you could do a combination line-bar chart, with line on the primary and bar on the secondary (or vice versa), and fudge around with the two axes so the month axis (category or text type) and the day axis (time or date scale) match up as well as possible.
Comment from Steven
Time: Tuesday, April 7, 2009, 6:57 am
I was aiming to do something very similar to what you are doing here, except I wanted the second graph to be a bar chart (I want to show % of things bought while tracking the price over time).
My problem is I’m showing 2 years worth of data, and multiple products being tracked, (the user selects the product and what months data) and the graph switches its ranges. So I can’t format it into one great big table,
Is there any way to get a Bar chart and a Line (line or xy scatter) to plot on 1 time line (x axis)?
Comment from Jon Peltier
Time: Tuesday, April 7, 2009, 7:06 am
I presume you mean a column (vertical bar) and a line together? Use the last approach, where three columns include the dates in column 1 and the two series in columns 2 and 3. Start with a line chart or a column chart, then right click the other series, select Change Chart Type from the pop-up menu, and select the type you want.
Comment from Tom
Time: Monday, April 27, 2009, 1:25 pm
This discussion was helpful.
But what if you wanted the chart to be stacked lines, showing a cumulative total of two sets of data being tracked, where the amounts for each line fluctuate on different days, but you want to see the combined sum plotted over time? Is that possible?
Comment from Jon Peltier
Time: Monday, April 27, 2009, 1:36 pm
Tom -
In order to stack two series, you should have a value at each category (date) for each series. In this case, you can’t keep the blanks in the data. It is probably easiest to sort by date, then interpolate to fill the blanks, either manually or with formulas.
Comment from Bill
Time: Friday, May 1, 2009, 2:05 pm
I have a similar problem, but in Excel 2003. Another added complication is that I need to plot the two data series on different axes, left and right.
Whatever I try, the data does not plot according to the date, but rather plots each data point sequentially. So the monthly data over the same time period takes up only a quarter of the graph taken up by the weekly data.
Do you know how to work around this?
Comment from Jon Peltier
Time: Friday, May 1, 2009, 2:30 pm
Bill -
Have you ensured that you have a Date (Time-Scale) Axis? In the base unit defined as Days?
Are your dates numerical or text? It only takes one text value to mess up the whole list.
Pingback from ggplot2: Two Time Series With Different Dates « Learning R
Time: Tuesday, May 5, 2009, 9:39 am
[...] Different Dates 2009 May 5 tags: business, chart, excel, ggplot2, plot, R by learnr In his blogpost Jon Peltier shows how to “make a line chart which has two time series, in which each time [...]
Comment from christopher
Time: Thursday, May 7, 2009, 8:22 am
Hey John,
Kindly assist me in plotting a XY Graph with Coulombs on X-axis,on the Y-axis I need Phosphate as primary (on the left hand side) and pH secondary (on the right hand side).I use Office 2007 and sometimes Ofiice 2003 and I did manage to plot the graph of Phosphates Vs Coulombs but I am struggling to add pH on the right hand side of the same graph.Please find the sample of the data that I am trying to plot the graphs for.
Thanks in advance,
Christopher
Time(s) Current(A)Coulomb Voltage(V) pH(mV) Phosphate
(A.s/L) (mg/L P)
0 1.12 0 0.5 3.2 0.8
1200 1.12 1344 0.6 2.95 0.72
2400 1.12 2757 0.7 2.88 0.68
3600 1.12 4244 0.8 2.78 0.65
4800 1.12 5812 0.9 2.72 0.56
Comment from Jon Peltier
Time: Thursday, May 7, 2009, 9:06 am
Christopher -
Clean up the data: use only one row for the column headers. Include units in the same cell, or better, leave them off.
Select the “Coulomb” column, then hold CTRL while you select the “pH” and “Phosphate” columns. Create an XY chart. Excel should use the first column as X values and the other two as Y values. Right click the Phosphate series, choose Format, and on the main tab (2007) or on the Axis tab, choose Secondary.
Comment from BD
Time: Monday, November 23, 2009, 3:34 pm
Thanks for covering this issue on Two Time Series with Different Dates. I learned from it. One other question. I made a plot with 3 time series. I used one column for the X values (dates) and used a secondary axis for two of the series because the Y-values are different magnitude parameters. The single column for the dates was possible because eventhough each series had different dates, they all fell in the same months, so I was willing to sacrifice specific dates of the two secondary axis series and use the date from the primary axis data series (made the assumption that the secondary axis dates were close enough to the pirmary axis series).
I have a decent looking plot, but the seondary axies series sets show a point no. instead of a date when I select a point on the graph. The primary axis series shows both the value and the date. Any suggestions to make the dates also show on the two secondary axis series points?
Comment from Jon Peltier
Time: Monday, November 23, 2009, 3:53 pm
Bruce -
You didn’t mention a version of Excel, but Excel 2003 automagically applies the primary category labels to the secondary axis. (If it doesn’t for some reason, go to Chart menu > Source Data > Series tab, copy the address from Category (X) Axis Labels to Secondary Category (X) Axis Labels).
In my little test Excel 2007 also applied the primary labels to the secondary axis. But the protocol to fix your issue is to right click on the chart and choose Select Data. Select a primary axis series, and click on the Edit button under Horizontal (Category) Axis Labels, copy the axis label range address, then select a secondary axis series, click again on the Edit button under Horizontal (Category) Axis Labels, click in the box, and paste the address.
Comment from Sami
Time: Tuesday, December 15, 2009, 10:42 am
Jon,
very nice comments and solutions you have here. Perhaps you can help me.
I am strugling with excel 2007 to plot lines with date axis. It looks like excel 2007 is acting unlogical.
I’ll decribe the problem using a simple example. Let’s make two lines: series1 (column B) and series2 (column C). Have a common date axis on column A. Have the dates running from for example 8-nov-2009 till 22-dec-2009. First plot series1 as a line (with markers). Date axis will be from 8-nov-2009 till 22-dec-2009. After that add series2.
So far so good. Since I like to start the date axis at the first of month I am fixing start x-axis at 1-nov-2009. This is also still fine. The first point of line is positioned above x-axis location 8-nov. But now comes the annoyance.
If I want for series2 to correspond to a secondary axis excel messes up the x-axis placing the lines with respect to the x-axis.
Just realized that somehow excel 2007 makes the secondary x-axis prevailing and I must manually configure secondary x-axis to have the lines displayed correctly. This means that I when secondy axis switched on I am switching on as well secondary x-axis even if I am not displaying.??
Is there a way to link secondary x-axis to primary x-axis (automatically matching ranges)? Is there a more efficient (other) way to plot the chart that I just described?
many thanks for your help,
Sami
ps: By the way I tried as well with combination plot (changing series2 to xy-plot) but di’n't manage.
Comment from Jon Peltier
Time: Tuesday, December 15, 2009, 11:03 am
Sami -
Scroll down to the heading “But there’s an easier way.” This technique only needs one X axis, and only requires a slight realignment of your data.
Comment from Sami
Time: Tuesday, December 15, 2009, 11:21 am
Jon,
thank you for the quick reply.
Have tried contents of heading “But there’s an easier way”, but that does not work. (Maybe I am doing something awfully wrong).
Introducing the secondary axis makes the placement of the lines wrong on the primary axis when you are fixing the start point (in my case first of a month).
I can only get around by matching the range of the secondary horizontal axis with the primary horizontal. Have not been able to find a different way.
cheers,
Sami
Comment from Jenn
Time: Wednesday, December 23, 2009, 9:00 pm
Thanks! This has been very helpful. I sure wish I had known about this blog years ago.
Comment from SamT
Time: Sunday, August 8, 2010, 4:13 pm
Hi – this works great for dates (the combined data ‘easy’ method), but my data all occurs on the same date, at different times.
So I have 2 series all happening over the period of one hour. Series one contains data every second, series 2 data every 30 seconds.
I really cant seem to get Excel 2007 to plot them on the same time line.
Comment from Jon Peltier
Time: Monday, August 9, 2010, 6:20 pm
Sam -
A date-scale axis considers only integers, so it plots all points for a given date at the horizontal position for that date. You need to use an XY chart. The problem solved in this article, aligning multiple sets of X values, occurs only for line charts, not for XY charts.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.