EXCEL 97: Two-Way Plots including Regression using Add Trendline

A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

This January 2009 help sheet gives information on For considerable detail on the basics of chart making see:  Excel 2007: Charts (column, pie, line)


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 Insert Tab and the Charts Group and Scatter option.

We use data in file carsdata.xls to produce a scatterplot with CARS on the vertical axis (Y-axis) and HHSIZE on the horizontal axis (X-axis).

The scatterplot is an XY scatterplot, so the data in the Excel spreadsheet should be in two adjoining columns, with the X variable followed by the Y variable.
For our example we need HHSIZE followed by CARS.

Twoway plot

This yields the following plot

twoway plot


A separate trendline (or regression line) can be added after the chart is created by

We have

Add trendline

Then select Add trendline.
This yields the Format Trendline dialog box.
Add trendline

Hitting Close adds a trendline to the data.

Twoway scatter polot with trendline


The look of the scatter plot can be improved. We do the following

We obtain

Twoway scatter plot with trendline

For further information on how to use Excel go to