USING EXCEL 2010 FOR LINEAR REGRESSION,
HISTOGRAMS AND BAR GRAPHS AND TO FIND THE MEAN AND
STANDARD
DEVIATION
Dr. Susan Petro
Click here for printer friendly version 
Topic
3. Determining the
size of your page for printing
4. Directions for making your table
a) Table title
b) Determining
what goes on the x and y axes
c) Table headings
d) Entering
the data
e) Significant
figures
f) Inserting
a ° symbol
g) Inserting a Δ symbol
h)
Inserting a ±
i) Merging
cells
j) Subscripts
and superscripts
k) Scientific Notation
5. Formatting your table
a)
Title
b)
Centering
data
c)
Formatting
table headings
d)
Adding
borders
6. Directions for making your
graph
a) Labeling the x and y axes
b) Sizing
your graph
c) Adding the trendline and
formulas
d) Deciding
how to set the y intercept
e) Adding
the correlation coefficient ( r ) to your graph
f) Formatting your
graph
To
change the
graph axes labels, etc.
To format the
axes labels
To format the axes
number
10. Making a
Histogram
a) Making the table for
your histogram
b) Formatting your histogram
c) Adding a mean and
standard
deviation line to your histogram
11. Making a Bar Graph
13. Calculating
the Standard Deviation
14. How to embed your
Excel graphs and tables into a Word document
15. Examples of Graphs
On
Following Directions
From We
Took to the Woods by Louise Dickinson Rich
I'm a good knitter, and I'm
proud of it. I can make up my own directions, or I can
follow
printed directions, which apparently is the harder thing to do,
although I don't
see why it should be. I think the
difficulty with people who can't
follow printed
directions for knitting or anything
else is that they
try to understand them. They read
the whole thing through and it
doesn't make sense to them, so they start with a defeatist
attitude. They try to
relate the first few steps to the whole,
and there is no obvious
relation, so they get discouraged and
say, "Oh,
I can't learn things out of books...."
You don't
have to understand directions.
All you have to do is follow them; and
you
can follow them only one
step at a time. What you need is not intelligence, but a
blind
faith. I never read directions through. I never read beyond
the operation I am
engaged in, having a simple
trust that the person
who wrote them knew what he was
doing. That trust is
usually
justified. Oh, there's no trick to following directions, and
if I
don't teach my children one other thing, I'm going to teach them
that.
I think it's important.
2. Scroll down to and then click on Microsoft Office.
3. Click on Microsoft Excel.
DETERMINING PAGE
SIZE FOR PRINTING
Click on File in top left
corner of screen (1).
Click on
Print (2).
Click on the Home tab (3) next to the File tab. A broken line will appear
around the area that will print on an 8 1/2 x 11 sheet of paper..
Making your Table
Title of the table
In the first row, A1, type in the table number and the table
title.
Determining what
goes on the x and y axes
Decide
what data should go on the x
axis (the determinate or independent axis) and
what data
should go on the y axis (the
indeterminate or dependant axis). The information
you knew before
you ran the
experiment goes on the x axis
and the information you got
by doing the experiment goes on the
y
axis.
For example if you are
measuring the
absorbance of a number of dye
concentrations the dye
concentrations will be on
the
x axis since you decided on and prepared the
concentrations you
wanted
to test.
You didn't
know what the absorbances would be until
you put the various
concentrations
in the spectrophotometer,
so absorbance would go on the y
axis.
Table Headings
Type in
the
heading for the x axis in the
box below the title Don't forget appropriate units
e.g. Concentration of New Methylene Blue Dye
(mg/ml).
If the
heading is too long to fit
in the box, you may increase the column width by placing the
cursor on the
line between A and B so
you get a double headed arrow 1
and dragging
to the width you want. If you have data in columns to the right of the broken line page border
they will not show up on your page when you print it.
To have your data in columns
To
have
your
data in columns, type in the heading for the y axis e.g. absorbance (Abs units)
in
the B column
To have your data in rows
To have your
data in rows, type in heading for the y
axis e.g. absorbance (Abs units) in
the
next
box below the one in which you typed your x heading (in the same column).
Entering the Data
Enter the
values
for x (numbers only, no
letters or symbols) in
the A column
under
your heading if
you are doing your data in columns.
Enter values
for x in the row following
your heading if you are doing your data in
rows.
For
data
in
columns, enter values for y (numbers
only, no letters or symbols)
under the y
axis heading e.g. enter values in B4, B5, etc.
For data in
rows, enter values for y in
boxes to the right of your y axis heading
e.g.
B5, C5,
D5, E5,
etc.
DO NOT
enter
values for your unknown in the table at this point.
Decide the
number of spaces you want to the
right of the decimal point based on the
significant figures you used
for your
measurements. Select your table entries. Click on the
appropriate
decimal point icon to
either
increase or decrease the number of decimal places you want.
.
To insert
a
degree (° ) symbol hold down the Alt key
and type 248 on the number pad. If you
are using a
Macintosh
computer hold down the Alt key, shift key and hit the number 8
key
(not
the 8 on the number pad..)
To insert a Δ symbol click on Insert on the task bar. Click on Symbol icon.
Inserting a ± symbol
To insert a ± sign in front of your standard
deviations
move the cursor down one cell,
click on Insert on the task bar. Click on Symbol icon. Click on ± symbol. Click on Insert. Click on Close.
Now type in the standard deviation value you obtained.
To merge cells select the cells you want to merge (in the case below that would be 
Results  You don't have to type absorbance in every cell with pH value Note: Do not select this label row when making your graph 
Subscripts and
Superscripts
To
insert
a superscript or subscript in Excel click on the tiny arrow in the box
to
the right of the word
Font.
A
Format Cells box will appear. Under Effects click on the box
to the
left
of subscript or superscript. A check mark will
appear. Now
click on OK.
Type in the superscript or
subscript. To exit
super/subscript mode repeat the process
above. When you
click
on the box with the check mark it
will disappear.
Click on OK.
Scientific Notation
To enter your data in scientific notation click on General
A drop down menu will appear. Click on Scientific
Formatting Your Table
Click
the mouse in the title cell so it is
selected as above (has a dark border around it).
Click on Wrap
Text.
Select all
cells in that row through the one that you remembered from above
step. In this case H.
Selected empty
cells will be pale blue.
Click on Merge
and Center.
Drag cell
width of A back to its original size to fit table headings. In
the example
below, the A
column fits the heading Temperature °C. Your title will
now fit on the
printed page no matter how long it is.
Centering
Data
Select the
entire table.
Click on the Center
icon
on the toolbar.
Formatting Table
Headings
Select
just
the table headings.
Click on
the
B (bold) icon if you want the
table headings bold.
Choose
font style and size you want.
To
add
borders to your table
Select
the
entire table.
Click on
border icon arrowhead.
A drop down menu will appear. Click on All
Borders
Note: If you
will be calculating
unknowns from your graph wait
until you have completed
that step before adding your borders.
Select the entire table including the headings, but omitting the table title. See below.
.
Click on
Insert (1) on the menu bar.
Then click on Scatter (2). Then click on the
point graph (3). See below.
Check to be sure
your
computer correctly decided if your data was in rows or
columns. Do this
by looking at the values
on the x and y axes and the contents
of
the Legend box. If the legend box says Series (and you know you selected your table headings) then the computer
decided your data was in rows when ist was in columns or vice versa. If this is the case, click on
Switch Row/Column icon.
.
Sizing
your graph
Click on the white area of
the graph so a foursided arrow appears.
Holding down the
left mouse button, drag the chart below your
table. Now click on the four tiny squares
on the bottom edge of your graph so a doublesided
arrow appears. Drag the bottom
of the graph down to number 43
or so to leave room to add a title below later.
Click
on the four tiny squares on the right side of your
graph
so a
doublesided arrow
appears. Drag the
right side of the graph so it fills the width of the page almost to the
dotted
line.
Delete any writing in the Chart title box. You will be titling your graphs under the graph.
Labeling the
x and y axes
Click
on Chart Tools (1)
at the top of the screen. Click on
Layout tab (2).
Click on
Axis Titles icon (3). Click on Primary Horizontal Axis Title (4). Click on
Title
Below Axis (5).
Enter x axis label in the box that appears below the graph
e.g. Concentration
of Methylene Blue Dye (mg/ml). Hit Enter key.
To label the y
axis title, repeat above directions
except click on Primary Vertical
Axis Title and then Rotated Title.
Type in the y axis label e.g. Absorbance (Abs units). Hit
Enter key.
Click on one of
the
points on
your graph. All the points in the line to which that point
belongs will
become
a cross.
Click on
Layout on the menu bar (1).
Click on Trendline (2).
Click on More
Trendline Options (3).
Deciding how to set the y intercept
Click on Set
Intercept when dealing with linear regression data where y was zero
when
x was zero for
example in labs using
the spectrophotometer
to measure
absorbance.(The spectrophotometer is
blanked; set to zero
absorbance
at zero
concentration of the substance whose absorbance
is to be measured).
Otherwise don’t
click on Set Intercept.
Set
Intercept will set the y
intercept at zero so
when your formula (y = bx + a)
comes up the a
value will be zero and
not
appear.
Click on
'Display
equation on chart'. The formula y =
bx + a with appropriate values
for b (slope)
and a (y intercept) will
appear on your graph
Click on
'Display
Rsquared
value on chart'. R^{2 }is the correlation of
determination.
Values for the
correlation
of determination range from zero to one. The higher the
correlation of
determination
(the closer to one) the better the regression line is in
explaining the
variation
of the data.
Click on Close.
In the
legend box Linear will apear. See below.
To remove Linear click on the
legend box. A box with handles will appear. Now click
a
second time on just
the
Linear and the handles will be just around Linear. Hit the
delete key to
remove.
To move
formulas
where they can be more easily seen, click on the formula. A border
will
appear around the formula. Click on the border and
drag it
where you want it.
If you want to
change the formatting of the formulas e.g. point size, right click over
formulas and a formatting box will
appear. Choose a smaller point size or
different font, etc.
To add the correlation coefficient ( r ) to your graph:
The correlation coefficient indicates how
closely the
points on your graph fit a straight line. A value of +1 or 1 indicates a perfect direct or indirect relationship between x and y. If your correlation coefficient is not close to +1 or 1 your data is not linear. (Perhaps it is logarithmic, exponential or polynomial). If this is the case you wouldn’t use linear regression for graphing your data. A correlation greater than 0.8 is generally described as strong, whereas a correlation less than 0.5 is generally described as weak. Note that the correlation coefficient indicates a relationship between the variables of interest. This does not necessarily imply that one variable causes the other (for example, higher SAT scores do not cause higher college grades), but that there is some significant association between the two variables. Link to visual aide on correlation coefficient. 
To format the
axes labels
Right click on the axis labael.
Change
font, font size, etc.
Click outside graph and scroll back to your table. 
To solve for x when you know y.
Type in the word ‘unknown’ in the empty space at the end of the y column.
If your data is in
columns
enter the y value you got for
the unknown in the box
below the one where you
typed ‘unknown’. This is so the instructor knows this is
where you calculated your unknown.
Move pointer to the empty
space at the end of the x
column. Type in the word
'unknown'. Now move pointer
to the box below the one where you just typed
‘unknown’.
Type = then a parenthesis
then the y value you just
entered. ( If the number is very
long you can just type the
letter and number of the cell e.g. B12 to save time.) Now
type  then the a
value (y intercept) from the
formula on your graph and a closing
parenthesis. (If your y
intercept was zero you only need to type the y value without
parentheses.)
Now
type in / and the b value
(slope) from the equation on your graph.
Suppose the equation on
your graph is y = 29.514x. This would mean a, the y intercept,
is zero since it doesn’t
appear and b is 29.514. If
you knew y value for the unknown
was 0.100 for example, you
would type
=0.100/29.514 or
=B12/29.514. Then hit Enter and that
will
give you your x value at that y.
To solve for y when you know x.
Type in the word 'unknown' in the empty space at the end of the x column.
Now enter the x value for
the unknown in the box below the one where you
typed ‘unknown’.
Move pointer to the empty
space at the end of the y
column. Type in the word
'unknown'. Now move
the pointer to
the cell below the one where you just typed
‘unknown’ in the y column.
Type = then type a
parenthesis
then the b value (slope) from
the equation on your
graph, then * and the
x value you just typed under
'unknown' in the x column.
(If the x value is very
long you may type the letter and number of the cell containing
the x vallue you just
entered
e.g. A12) Now type the closing parenthesis then + then
the a value (y intercept)
from your graph. then the closing parenthesis then + then the
a value (y
intercept)
from your graph.
Suppose the equation on
your
graph was y =  0.421x + 27.98.
You would type
=(0.421*A12)+27.98
Hit Enter and that will give you your y value for that x.
Adding the Figure
Number and Title to Your Graph
In a cell below your graph type in the
Figure number e.g.if this is your first graph
in a lab report or journal article type in
Figure 1.
Add a descriptive title to
your graph e.g. Figure 1.
Effect of Concentration on
the Absorbance of New Methylene
Blue Dye at a Wavelength of 675 nm
BEFORE PRINTING YOUR GRAPH be sure to click
outside
the graph area
itself. Otherwise only the graph and and not
the accompanying table will be printed.
To see what your graph will look like prior to
printing,
click on File and click on Print Preview.
Say you want to chart several lines on the same
graph
 for example the effect of temperature
on the calories of energy consumed by several
bird
species say the English sparrow, the purple
finch and the pine siskin.
Type in heading for x axis data, in our example
Temperature (°C) in the A cell below
the cell in which you typed the table number and title.
Say you wanted your data in rows. In the A cell
below the x axis heading type
in
Calories English sparrow, in the next A
cell type in Calories purple finch and in
the next A cell type in Calories pine siskin.
Your temperature values will be typed in the numbered rows e.g. B4, C4, D4, etc.
The calories for the English sparrow will be typed in the 2 row e.g. B5, C5, D5, etc.
The calories for the purple finch will be typed in the 3 row e.g. B6, C6, D6, etc.
The calories for the pine siskin will be typed in
the
4 row e.g. B7, C7, D7, etc.
If you want your data in columns than headings will be in A cell for x and B, C, D etc. for ys.
Select the table as you did for a single line graph
 all cells with heading titles and values.
Follow directions as previously
explained.
The only difference is that
you must repeat
the "Click on one of the points on your
graph" sequence for each line.
When one has several lines the formulas may be
crowded
and it may be difficult to see
which formula goes with which line so click on
the formulas one at a time and drag them
away from the lines.
Make titles for each set of formulas as soon
as each set is made and moved to prevent
confusion about which formulas go with which line by clicking on the
Text Box icon
and then clicking the cursor above the set of
formulas e.g.
English sparrow over the formulas that pertain to the English sparrow
data line.
See below.
To put the legend symbol used for each line next to the label above
each formula set
as seen below.
make sure your cursor is in the chart area so chart Tools is in the
menu bar.
Click on Insert. Click on Shapes.
Click on the shape that matches your legend symbol.
Click
on an empty cell.
Your shape will appear surrounded by a box.
Size your shape and change
to the appropriate color.
Move shape next to label over formulas.
Click to see an example of a multiline
graph.
Histograms are used
to represent continuous data. The bars represent ranges within which
your data points fall.
Below are the water volume measurements for the
histogram at the end of this document.
These were used to make a
histogram with five equal bars.
Water volume (ml) 


35.80 
37.90 

38.00 

38.20 

38.20 

38.50 

38.90 

39.10 

39.50 

40.60 

Mean = 38.47 

Standard deviation = ±1.25 
To get five bars (or any other number you want) on your histogram:
Step 1: Subtract lowest value from largest. For the numbers above that would be 40.60  35.80 = 4.80.  

Step 2: Now divide your answer by the number of bars you want . If you want three bars divide by 3. Since we want five bars divide as follows: 4.89/5 = 0.96 
Step 3: Add this number to your
lowest
value to get your first range. So 35.80 + 0.96 = 36.76.
First range is thus 35.8036.76. 

Step 4: For the second range
begin with
36.77, because if you use 36.76 and one of the measurements happens to
be exactly that you won't know whether to count it in the first range
or the second range when you determine the frequencies. Now add
0.96 to the end of the first range value (36.76) to get the upper value
of the second range. Second range is thus 36.7737.72.
Third range is 37.7338.68 and so on. 
Making the table for
your histogram
Follow previous directions for making a
table. Put range values
in first column
and
frequencies in second column. See
previous directions for
formatting a table.
Select the entire table.
Click on Insert (1).
Click on Column (2).
Click on the left most 2D
Column graph icon (3).
Delete the title on the histogram as this will
be added later
underneath the graph as
you did in the single and multiline
graph directions.
Label x
and y axes. Make sure
Chart Tools (1) and Layout (2) are selected. Click
on
Axis Titles (3).
Click on Primary
Horizontal Axis Title (4). Click on Title Below
Axis (5).
Repeat for Primary Vertical Axis Title.
Click on one of the bars of
the graph. Chart Tools
will appear at the top of the
screen. Click on the Layout file tab if
not already
selected. Click on Format
Selection which is at the far left of your
screen.
Since you are making a histogram set the gap
width to 0. Click on Close.
How
to Insert a Mean and Standard Deviation Line on your Histogram
Draw a vertical line above
where the mean would fall.
Click on
the straight line
icon again to draw a
horizontal line from the minus
to the plus end of the standard
deviation range.
Click on
the straight line icon to
make the
end bar for each end of the horizontal line.
To add the mean and standard
deviation values above the line click on the Text Box.
icon. A dotted line box will appear in
your graph. Type in yuor mean and standard
deviation values and move box over the center
of your mean and standard deviation
line.
Click to see an example of a histogram.
Formatting
a Bar Graph if
you want to
change the gap width between bars
Click on one of the bars of
your graph.
Chart Tools should
appear at the top of
the screen.
Click on the Layout file tab
if not already selected.
Click on Format Selection.
Since you are making a bar graph set
gap width to whatever you want except 0. Click Close.
Enter Microsoft Excel as previously described
Type in heading e.g. water volume in ml in A1 box
Enter values in A column
Skip down a few rows in A column and type in Mean.
Go down to next row and type in =average(range of
boxes
containing your data) and hit
Enter key e.g. if you entered data in box A2, A3, A4,
A5, A6 you would type
=average(a2:a6) or after the first parenthesis
simply select all the boxes containing
the data and the range will appear automatically.
Add the closing parenthesis and hit Enter.
The standard deviation is a measure of the
variablilty of your data. It describes a range within which 68%
of your values lie. For example if you were weighing tomatoes and
got an average tomato weight of 100 grams with a standard deviation of
± 5 grams, it would mean that 68% of the tomatoes you weighed
were between 95 and 105 grams.
Follow all directions for mean as above.
Now skip down a couple rows in column A and type in Standard Deviation.
Go down to the next row and type in =stdev(range of
boxes
containing your data)
and hit Enter. E.g. if you entered data in boxes A2,
A3, A4, A5, A6 you would type
=stdev(a2:a6) and hit the Enter key or after
the
first parenthesis simply select all
the boxes containing the data and the range will appear
automatically. Add the
closing parenthesis and hit Enter.
To insert a ± sign in front of your standard
deviations
move the cursor down one cell,
hold down the Alt key and type 241 on your number pad.
(If you are using a
Macintosh computer hold down the Alt key, the shift key
and hit the + key.).
Now type in the standard deviation value you obtained.
SINGLE LINE GRAPH
MULTILINE
GRAPH