EXCEL 97: Two-Way Plots including Regression using Add Trendline
A. Colin Cameron, Dept. of Economics, Univ. of Calif.
- Davis
This September 1999 help sheet gives information on
-
two-way scatter plots
-
trendline (regreesion line) for two-way scatter plots.
For considerable detail on the basics of chart making see
The presentation here is much shorter.
TWO-WAY SCATTERPLOTS
A two-way scatter plot has one variable (say Y) on the vertical
axis and one variable (say X) on the horizontal axis.
This is done by choosing the Chart Wizard and the XY scatter plot option.
-
For cross-section data chose the default scatter option.
-
For time series data, with time on the horizontal axis, choose one of the
other options that include a line:
If many observations choose scatter with data points connected by lines.
If only a few observations choose scatter with data points connected
by lines without markers.
Then customize the chart in the usual way.
TRENDLINE FOR TWO-WAY SCATTERPLOTS
A separate trendline (or regression line) can be added after the chart
is created by
-
clicking on a point within the chart so that all the point on the chart
are highlighted (this is tricky)
-
right-click to get options including Add Trendline
-
select Add Trendline and choose the default of linear
-
Hit okay.
This adds a trendline to the data.
This trendline is actually two-variable regression.
To obtain the formula for the regression line, which is then displayed
on the chart, in Add Trendline choose options and select Display equations
on chart.
To obtain the R-squared for the regression line, which is then displayed
on the chart, in Add Trendline choose options and select Display R-squared
on chart.
For more detailed regression output use Tools | Data Analysis | Regression
or more detailed Excel commands.
For further information on how to use Excel go to
http://www.econ.ucdavis.edu/faculty/cameron