Trend Charts: Line or XY? - This tutorial shows you how to build a trend chart using an XY Chart. It is based on example found in Trend_Control_Chrt_Basics.XLS.
1. Type of Trend Chart: Line or XY?
Trend charts usually show changes in one or more Y variables over time. The X axis may be days, months, years, or any combination of date-time. Excel provides two types of standard charts that can be used for trend data: Line and XY (Scatter). Line Charts are suitable for situations where the X Axis has categorical data. Excel has a special type of Line Chart for trend data that simplifies data formatting.
What is the best way to build a trend chart of our time series data set?
In Step 1 of the Chart Wizard, Excel offers us 14 standard chart types, including the Line chart. Since most trend charts show a trend line for the data being charted, many users assume that the Line chart is the best chart for time series data.
The XY (Scatter) icon does not look like a typical trend chart, many users quickly select the Line Chart without considering the XY (Scatter) Chart. That is unfortunate because the XY (Scatter) chart is an excellent tool for trend analysis.
Jon Peltier's site has a good discussion of Line and XY Chars on his excellent site.
2. What About Time Periods Less Than A Day?
Excel's Line chart Type will not let you use a time period of less than 1 Day, a problem when you want to chart hourly or minute by minute data. With an XY Chart you can plot second by second, minute by minute or hour by hour data if you need to.
I use XY charts for all of my trend chart work.
3. Making an XY Chart Look like a Trend Chart
Now that we have decided to use an XY (Scatter) chart for out trend data, we have two sets of choices which make 5 sub-chart types:
I prefer to use the straight line between points format, the smoothed line usually makes my data look to good.
Once we have created our XY (Scatter) trend chart, we can format the X axis just about any way we want. the screen to the right shows the X Axis Format window.
Notice the X Axis Scales. The Maximum box shows a conventional date (5/31/03) while the Minimum Box shows 37705. XY Charts let you enter dates in either date formats or their numeric equivalent.
I have been using Excel for trend charting for over 10 years and have found that XY Charts give me the most flexibility. While it is a user preference for time periods over a day, I have found XY Charts give me the better versatility than line charts. All of my trend chart examples are based on XY Charts.