Constructing a Least-Squares Graph Using
Microsoft Excel
Simple spreadsheet programs such as
Microsoft Excel or
Quattro Pro are powerful tools
for many types of calculations in chemistry. One of the most often used functions of a
spreadsheet program is to construct graphs. The procedure for constructing a least-squares graph
using
Microsoft Excel is outlined below.
Note that "
click" means to put the mouse cursor on a position and press the left mouse
button once; to "
double-click" means to press the left mouse button twice rapidly; to "
drag"
means to place the mouse cursor on a position, press and hold the left mouse button, move the
mouse cursor to a different position, then release the mouse button. Dragging is used not only
to move objects but also to highlight text, data, etc.
- Enter your data into the spreadsheet. It is often easier to put similar types of data
into columns rather than rows (although this is not a requirement).
- Select (highlight) the data that you want to include in the graph. For example, if
you want to plot the data contained in cells 1-4 of columns A and B, place the
mouse cursor on cell A1 and drag the mouse cursor to cell B4.
If the data you want to include happen to be in two columns that are not adjacent
(e.g., cells 1-4 of columns A and C), place the mouse cursor on cell A1 and drag
the mouse cursor to cell A4. Then, while holding down the CTRL key on the
keyboard, place the mouse cursor on cell C1 and drag to cell C4. Both columns
of data should be highlighted.
- Click on
Insert on the menu bar.
- Click on
Chart....
- Under
Standard Types,
Chart type:, click on
XY (Scatter).
- Under
Chart sub-type:, click on the chart with only data markers and no lines.
- Click on
Next>.
- Click on
Next>.
- Under
Titles,
- click in the text box under
Chart title: and enter a title for the graph.
- click in the text box under
Category (X) axis: and enter a title for the
x-axis.
- click in the text box under
Value (Y) axis: and enter a title for the y-axis.
- Click on the
Gridlines tab. Click in the checkboxes to turn gridlines on or off.
- Click on the
Legend tab. Click in the checkbox next to
Show legend to turn the
legend on or off.
Placement of the legend on the graph can also be selected here.
- Click on the
Data Labels tab. Click on the radio buttons to turn data labels on
or off.
- Click on the
Data Table tab. Click in the checkboxes to turn a data table on or
off.
- Click on
Next>.
- Under
Place chart:, click on the radio button next to
As new sheet:. Enter a
name for the graph in the highlighted text box.
- Click on
Finish. At this point you have created an X-Y plot of the data.
- Move the mouse cursor to
any data point and press the left mouse button.
All of the data points should now be highlighted. Now, while the mouse
cursor is still on any one of the highlighted data points, press the right
mouse button, and click on
Add Trendline... from the menu that appears.
- From within the
Add Trendline window, under
Type, click on the box
with the type of fit you want (e.g.,
Linear).
- Click on
Options at the top of the
Add Trendline window.
- Click in the checkbox next to
Display equation on chart and the
checkbox next to
Display R-squared value on chart.
Do not click on the
checkbox next to "Set Intercept = 0".
- Click on
OK.