USING EXCEL 2010 FOR LINEAR REGRESSION,
HISTOGRAMS AND BAR GRAPHS AND TO FIND THE MEAN AND
Dr. Susan Petro
|Click here for printer friendly version|
1. On Following Directions
2. Opening Excel
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
b) Centering data
c) Formatting table headings
d) Adding borders
6. Directions for making your
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
9. Making a Multi-line Graph
10. Making a
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
12. Calculating the Mean
the Standard Deviation
14. How to embed your
Excel graphs and tables into a Word document
15. Examples of Graphs
a) Single Line Graph
b) Multi-line Graph
d) Bar graph
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.
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.
Below is an example of a table title. Don't do anything further with the title at this point.
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.
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.
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.
values for your unknown in the table at this point.
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. .
Inserting (°) symbol
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..)
Inserting a Δ symbol
To insert a Δ symbol click on Insert on the task bar. Click on Symbol icon.
Find the Δ symbol on the table and select it. Click on Insert then click on Close.
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 and then click on the Merge and Center
icon on the toolbar. This is helpful when making tables for multi-line graphs measuring the
same thing for multiple conditions e.g. in the case below the absorbance of an enzyme at multiple pHs.
Results - You don't have to type absorbance in every cell with pH value
To insert a superscript or subscript in Excel click on the tiny arrow in the box to
the right of the word Font.
Format Cells box will appear. Under Effects click on the box
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.
To enter your data in scientific notation click on General
A drop down menu will appear. Click on Scientific
Formatting Your Table
the mouse in the title cell so it is
selected as above (has a dark border around it).
Click on Wrap Text.
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.
width of A back to its original size to fit table headings. In
below, the A column fits the heading Temperature °C. Your title will
now fit on the printed page no matter how long it is.
Select the entire table.
Click on the Center icon on the toolbar.
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
Note: If you
will be calculating
unknowns from your graph wait
until you have completed that step before adding your borders.
MAKING YOUR GRAPH
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
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 four-sided 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 double-sided 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 double-sided arrow
appears. Drag the right side of the graph so it fills the width of the page almost to the
Delete any writing in the Chart title box. You will be titling your graphs under the graph.
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.
Adding the trendline and formulas
Click on one of the points on your graph. All the points in the line to which that point
belongs will become a cross.
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.
equation on chart'. The formula y =
bx + a with appropriate values
for b (slope) and a (y intercept) will appear on your graph
value on chart'. R2 is the correlation of
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.
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.
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
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
Right click on the axis labael.
Change font, font size, etc.
|Click outside graph and scroll back to your table.|
Solving for Unknown x
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
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
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
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
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
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
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.
MAKING A MULTI-LINE GRAPH
Say you want to chart several lines on the same
- 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
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
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
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
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
English sparrow over the formulas that pertain to the English sparrow data line.
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 multi-line
A HISTOGRAM USING EXCEL
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)
|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
value to get your first range. So 35.80 + 0.96 = 36.76.
First range is thus 35.80-36.76.
|Step 4: For the second range
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.77-37.72.
Third range is 37.73-38.68 and so on.
Making the table for
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 multi-line 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
Repeat for Primary Vertical Axis Title.
How to format your histogram.
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.
to Insert a Mean and Standard Deviation Line on your Histogram
Draw a vertical line above
where the mean would fall.
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
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
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.
Click here for an
example of a bar graph
HOW TO CALCULATE THE MEAN (AVERAGE) USING EXCEL
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
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.
HOW TO CALCULATE THE STANDARD DEVIATION USING EXCEL
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
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
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