
PDF version
The problem with scatter diagrams  For many purposes, the ideal way to search for a relationship between two variables is to plot them against each other on X/Y axes  the format commonly referred to as scatterplots, scatter charts or scatter diagrams. However, this approach makes the implicit assumption that the universe of points plotted is a single set. This becomes a problem when working with time series in which the relationship might change, being different in different periods. This can be seen in the work IEA has done with the relationship between the unemployment rate and the Social Security Trust Fund's finances, using Microsoft Excel.
The spreadsheet for this project contains columns for the two series of annual data to be plotted, plus a column containing the years, in 2digit form. The two series are selected, and the (XY) Scatter Chart type is chosen from Excel's Chart Type menu, producing the following:
Any linear relationship between these two series would be indicated by points appearing in a more or less straight line. At first glance, there appears to be nothing like that in this cloud of points  trying to use a regression function on the entire set of data would be inappropriate. However, changes in Social Security policies and economic and demographic factors over time might conceivably change the relationship between these two series at various times, leading to internally consistent, but separate, periods. How to tease these out of the cloud?
Solving the problem  Making any such periods visible requires two steps
In Excel, the first can be accomplished by the following
Labeling the points cannot be done in any practical fashion with offtheshelf Excel. However, it can be done easily with is a very helpful third party addon called XY Chart Labeler, available free from Application Professionals. With it installed, the procedure for labeling the points is:
This creates what we call a dateordered twovariable plot, as seen in Figure 2:
Any linear patterns covering a given period of years will then show up within the cloud and can be examined for a relationship by running a linear regression on just the points in that period.
Making multiple linear regressions  In this chart, we found 6 different periods of potential interest. In Excel, the procedure for doing a linear regression on a plot is as follows:
However, Excel doesn't make possible the selection of a subset of points. So if you want to do regressions on one or more subsets of points, each subset must be added as a separate plot superimposed on the fulllength plot, as follows:
For our purposes, we also replaced the automatic equation/R2value texts with our own custom text boxes, and customized the regression lines, ultimately producing the chart in Figure 3:
Note:
XY Chart Labeler is a macro. Once it is installed, Excel must be started with macrovirus protection turned off in order to make use of the utility.
Written: April 5, 2011
Posted: April 5, 2011 
