ProcessTrends.Com
Home
Chart Gallery
Video Tutorials
Trend Analysis
Using R
Data Visualization
 Global Warming
Map Excel Data with Google Earth
Chart Doctor
Advanced Chart Techniques
Links
What's New
Downloads
  About      
 Search Site RSS
   04/4/2008 4:30 PM

 

Trend & Control Charts

All Charts Made With Standard Excel Charting Tool, Techniques Shown on Advanced Chart Techniques Page and Recommended Excel Add-Ins. Click link to go to how-to page and link to downloadable file.

Trend Charts: Line or XY?

XY Trend Chart with Events

Day of Year (DOY) Trend Chart

 Control Chart
Interactive Trend Chart - Checkboxes Interactive Trend Chart - Scroll and Data Series Check Box - Dynamic Trend Chart - Plot User Selected number of Days Trend Chart Animation
Select Start and End Dates for Chart Interactive Moving Avg Chart Multiple Trend Lines Temperature Trends

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:

  1. We can show just points, just lines, or both points and lines.
  2. We can show straight or smoothed lines between points

I prefer to use the straight line between points format, the smoothed line usually makes my data look to good.

 

 

 

 

 

 

4. Handling Dates in XY Charts

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.

 

 

 

 

 

 

 

5. Recommendation

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.