Let’s start with a line chart using only the 2008 data, to give us our preferred date axis format.Ĭopy the 2009 data (select A14:A25, hold CTRL and select C14:C25, A1, and C1, then press CTRL+C), select the chart, and use Paste Special to add the data as a new series. The line chart is needed if we want “nice” date scale axis labeling. This causes some ticks to occur before the start of a month (3 and 3 in the chart below), and some after the first of the month (0, 0, and 0). In the XY chart, we can set the same min and max, and, but we have to use the inconvenient values of 60.917 and 30.4585 days as our major and minor tick spacing (731 days divided by 12 major and 24 minor divisions). In the line chart, we can set our min and max as and, and the major and minor tick spacing as 2 months and 1 month (and don’t forget the base unit should be days). Line charts allow us to adjust the scale to put a label at the first of each month, regardless of the number of days in the previous month. The line chart has a date label for every second month, but the XY chart’s axis shows August and October 2008 instead of September and November 2008. Why not just make an XY chart? Looks the same as the line chart with the homemade trendlines.īut wait, on closer inspection, the date axis looks wrong. That’s not too bad, but is there any way to use only two series and the native chart trendline feature? The data in these two columns is added to the chart, and a little formatting gives us two distinct timelines with distinct trendlines: The slopes and intercepts are calculated as follows:Ĭolumn D has trendline data for 2008 with this formula in D2 and filled down to D13:Ĭolumn E has trendline data for 2009 with this formula in E14 and filled down to E25: Well, we can always roll our own trendlines. The trendline dialog allows us to extend a trendline beyond the data in the forward or backward direction, but it doesn’t allow us to shorten a trendline (the forward and backward text boxes only accept positive values or zeros). These trendlines are too long, each extending across the entire date range.
Here’s the chart with a trendline for each series. To add a trendline to a series, right click on it and select Add Trendline. Here’s the data in a two-series timeline chart:
In Category Axis Tricks for Line and Area Charts – 1 I extended this technique to show how to format parts of a line chart in distinct colors:Ī reader asked how to show two years of data on two separate lines and show a trendline for each. Note: this approach worked in classic versions of Excel (2003 and earlier) but inconsistencies between Line and XY Scatter charts introduced in Excel 2007 interfere with XY series trendlines on Line charts.Įlsewhere in this blog I’ve showed how to Plot Two Time Series With Different Dates:
0 kommentar(er)
