|
Scientific
Graphs
This tutorial covers the
production of XY (scatter) charts from data in Excel
worksheets. XY charts are used to graph paired data. X is
usually assigned to independent variable, the variable whose
value is controlled or set by the experimenter. The Y
variable is then the depended variable, the values that
follow from the each value of X set in the experiment. In
some cases, neither the X or Y is set by the
experimenter.
In Excel and other graphing
programs, XY charts are quite different from line charts or
graphs. XY charts plot the actual values of both X and Y
while line charts plot the Y value against the row number
from the spreadsheet. For more information visit the
Microsoft Office Update article, "Creating
XY (Scatter) Charts in Excel 97 and
2000".
Words in RED
refer to menu or dialog box items, or words to enter.
top
of page
|
Using
the Images
The images below are shown
at a reduced scale. Click on the image to jump to a full
size picture. Then use the Back and Forward arrows on
your browser to jump back and forth between this page and
the image as often as needed.
|
|
Select
Data
Click
on the first
X value and drag
 to
select the entire X
range. If the Y data is
located in the next column, also select it. This example
uses the weight and height example in which weight is in
column
B and the calculated
height values to plot are in column
E. To give Excel two sets
of values to start working with, select column C, even
though it does not contain the actual Y values. In this case
the actual Y range is selected in a later step.
top of
page
|
|
|
Start
the Chart Wizard
Start the Chart
Wizard by clicking
 on
the tool bar. This will guide you through four steps in
making a chart.
|
|
|
Chart
Wizard Step 1 - Select Chart Type
In step 1, select scatter plot
and retain the default chart sub-type that is automatically
selected on the right side of the dialog box.
Click
Next.
top of
page
|
 .
|
|
Chart
Wizard Step 2 - Chart Source...
This step shows the selected
Data Range and allows us to change it if desired. Click on
the
Series Tab to see the
details of the X
and Y
Values. The
X
Value: =Sheet1!$B$2:$B$12
is
correct. The
Y Value: needs
to be changed to =Sheet1!$E$2:$E$12.
Click
on the
square at the right end of the Y
Value line
known as the Collapsing Dialog button. This shrinks the
dialog box allowing us to see the worksheet.
top
of page
|
.
|
|
|
Collapsing
Dialog button
|
|
|
Change
Data Range
As shown in the graphic on the
right, we simply drag
the dotted selection
around the desired data series. Note that the Y Value: line
has already changed to
=Sheet1!$E$2:$E$12.
Click
the Collapsing
Dialog button again to
display the full dialog box in order to complete Step 2.
Click
Next. top
of page
|
|
|
Chart
Wizard Step 3 - Chart Options
Step 3 deals with the labeling
of the chart. Five tabs allow us to work with Titles,
Gridlines, Axes, Legend, and Data Labels. Not all are
relevant in this example.
In the graphic at the near
right, the Chart
Title and titles for the X
and Y Axes have just been typed into their respective edit
lines. Note that they already appear on the preview chart.
Series
1 on the right side of the
chart is called a Legend.
Since
there is only one Y series there is no need for a Legend.
Click
the
Legend tab and
click the
Show Legend check mark to
remove the legend. Note that the Series
1 legend
disappears immediately (see far right). Click
Next to go
to the final step of the Wizard.
top
of page
|
|
|
|
|
Enter
Titles
|
Remove
Legend
|
|
|
Chart Wizard Step
4 - Chart Location
The default to
save as Object
in Sheet1
is fine for our example.
Click
Finish. The option to save
as a new sheet can be used when more than one chart is
produced. top
of page
|
.
|
|
Fine
Tuning the Chart
The nearly
finished chart appears, but several features need fine
tuning. To remove the gray background, bring the cursor area
into the Plot
Area as
shown on the cursor label, then double click. (graphic 1)
The Format
Plot Area dialog box opens.
(graphic 2) On the right
side, in the Area section, click None and
click OK. Click the
X
in the
upper right of the
Chart Tool Bar to
close it.
Move the whole
chart to part of the sheet away from the data by
dragging
on the outside border of the chart with the  .
Enlarge the chart by dragging on the handles (small black
squares). When the double headed arrow appears, you are
ready to start changing size. (graphic 3). top
of page
|
|
|
|
1.
Select Plot Area
|
2.
Format Plot Area
|
|
|
.
|
|
3.
Size Arrow
|
.
|
|
|
Change Minimum
Values on Axes
The chart has
a cluster of data in the upper right quarter and is
otherwise empty. To get a better view of the data, we can
change the minimum value on the Y axis from zero to 50
inches.
Click
on the
Y axis
to open
the Format
Axis dialog box.
Click the
Scale
tab and
change Minimum
to
50.
Likewise open the dialog box for the X
axis and
change Minimum
to
100.
|
|
|
Add
Trendline
The chart
shows that weight generally increases as height increases (a
direct proportion). We can get a better view of this
relationship by fitting a linear curve to the data. Excel
calls this a Trendline.
Begin by clicking
on one of the data points until the points turn yellow.
(graphic 1)
On the
Menu
bar,
click Chart
and click
Add Trendline.
This opens the Add Trendline dialog box. The default
settings for Trend/Regression
type are
Linear, Series 1.
Keep these unchanged. (graphic 2)
Click
the
Options tab and
click
Display Equation on Chart.
(graphic 3) The resulting chart is shown in graphic 4. The
equation is of the form Y=mX+b. The slope m and intercept b
are constants for the relationship that is useful in
predicting new values. For example, we could predict the
height of a person weighing 180 lb. assuming that the same
general height/weight relationship. ( 180 * 0.2775 + 27.62
=77.55 inches or about 6.5 ft) The Options tab on Add
Trendline also allows us to forecast forward or backward
(known as extrapolation in math and science).
top of
page
|
|
|
|
|
1.
Select Points
|
2.
Line Type
|
|
|
|
|
3.
Line Options
|
4.
Final Chart
|
|