Third, you are limited to two different sets of dates. Second, the two date axes must be manually synchronized each time the data changes. You can always hide the secondary date axis to clean up the chart, but you cannot remove it altogether. 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.
#How to plot a graph in excel with a range of points series
Format series B in the two-series line chart so it resides on the secondary axis. 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. The limitation that all series in a Line chart must use the same dates is not completely true.
So how can we plot multiple time series on a chart with nice date labels? 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. The data is plotted correctly, but the date axis isn’t as nice as the one we had in the line chart. The X and Y values of separate series in an XY chart may be completely independent. The obvious alternative is to make an XY chart with the two data 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. This illustrates a limitation with Line charts in Excel: the category labels or dates are defined by the first series. Time series B has more data points, at irregular intervals, over a shorter time span.Ĭreate 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. Time series A has weekly data, but with two values omitted. The two time series are plotted separately below. 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. This was such a good question, it deserves its own post. What makes his question more challenging is that Felix is using Excel 2007. I can then apply a custom date format to show only the year in the horizontal axis.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. Then enter Januas the minimum and as the maximum. To show only years, and make sure the line is displayed correctly across the range, I can set units to 1 year. If I plot the data as a line chart, Excel correctly interprets the dates and builds an automatic horizontal axis to fit the date range, with the unusual setting of 8 months for units. This is monthly stock price data over a period of more than 15 years, from July 2001 through May 2017, in more than 100 rows. Notice that even though the axis type is now text, Excel still understands the dates.įor example, I can apply a different date format, and the chart immediately updates. Now the data points are evenly spaced, and line up with the dates shown on the horizontal axis. In that case, you can switch the type to text.
However, there may be times where you want a simple, even distribution. The axis shows an accurate distribution of the data. Now it's clear that the stock price data is plotted across the date range, and the data points don't necessarily line up with the dates in the axis. It'll be easier to see what's happening if I add data labels and drop lines to the chart.
This is a little strange and maybe even confusing. Notice since we have a large date range, the interval is set to 1 year, and all dates are January 1. You can see category in the name, and if I open the format task pane to axis options, you can see Excel is using the Automatic setting, which, since we have valid dates, means we have options for minimum and maximum dates, as well as date intervals. If plot this stock price data as a line chart, the horizontal axis is automatically set up as a category axis with a type of "date". When you create a chart using valid dates on a horizontal axis, Excel automatically sets the axis type to date.įor example, this stock price data is spaced out over a period of more than 10 years, in random intervals. In this video, we'll look at an example of how Excel plots dates on a horizontal axis.
0 kommentar(er)
