USING EXCEL 2010 FOR LINEAR REGRESSION, HISTOGRAMS AND BAR GRAPHS  AND TO FIND THE MEAN AND STANDARD DEVIATION 
 Dr. Susan Petro

printer icon Click here for printer friendly version

TABLE OF CONTENTS                                

Topic

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
             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

7. Solving for Unknowns
         a) To solve for x when you know y        
         b) To solve for y when you know x

8.  Adding the Figure Number and Title to Your Graph

9. Making a Multi-line Graph

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

12. Calculating the Mean

13. Calculating 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

            c) Histogram
         
             d) Bar graph
 

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.

OPENING  EXCEL

       1. Click on Microsoft icon at the bottom left of the screen. Click on All Programs.

      2. Scroll down to and then click on Microsoft Office.

      3. Click on Microsoft Excel.

          Excel open

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..

          Open excel

   Making your Table       
          Title of the table
                
In the first row, A1, type in the table number and the table title.
     
         Table title

       Below is an example of a table title.  Don't do anything further with the title at this point.

           TAble title-inserted
        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).

               TAble heading x axis
          
          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

                 Data in columns
 
 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).

                Data in rows

     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.

Significant figures

     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.
  .

          Significant figures

Inserting (°) symbol

        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..)

Inserting a Δ  symbol

          To insert a Δ symbol click on Insert on the task bar. Click on Symbol icon.

Symbols

          Find the Δ symbol on the table and select it. Click on Insert then click on Close.     

                  delta step 2
 

 
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.

Merging cells

 

To merge cells select the cells you want to merge (in the case below that would be
cells B through E in Row 2 (1) and then click on the Merge and Center icon on
the tool bar (2).   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 . 

           Merge cells 1

  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

               merge cells 2

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. 

  
                  Super and subscripts

          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.

        superscript2_d8

Scientific Notation
          To enter your data in scientific notation click on General

           significant figures step 1
           
           A drop down menu will appear.  Click on Scientific

             Scinetific notation step 2

 

Formatting Your Table
     What if your title is too long to fit on one line?
         
See what letter cell on the right of your document will fit in the print area within
           the dotted border line.  In this case H.  Remember this letter.

          Too ong title step 1
  
        Now place the cursor on the line between A and B and drag so your title fits
           completely in the new A cell.

Too long title step 2

         Click the mouse in the title cell so it is selected as above (has a dark border around it).
         Click on Wrap Text.

          too long tilte step 3

          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.

Title length 2
          Click on Merge and Center.

               too long title step 5

          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.

               too long title step 6

Centering Data
          Select the entire table.
          Click on the Center icon on the toolbar.

                 centering table data
 

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.
 
     
                  Table formatting

 
To add borders to your table            
             Select the entire table.
            
Click on border icon arrowhead.
           
                Table borders

             A drop down menu will appear.  Click on All Borders

                          Table borders step 2        

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.

.Making graph step 1
      Click on Insert (1) on the menu bar.  Then click on Scatter (2).  Then click on the
      point graph (3).   See below.

      Making graph step 2

     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.

                     Switching columns and rows

       
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
     dotted line.
                  

         Delete any writing in the Chart title box. You will be titling your graphs under the graph.

           Delete chart title

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.

Labeling x axis

    

     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.  

Labeling y axis
   
 
 
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.

                       Selecting graph points

        Click on Layout on the menu bar (1).  Click on Trendline (2).  Click on More
         Trendline Options (3).

             Trendline selection 

        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 R-squared value on chart'. Ris 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.

       Set y intercept

          Click on Close.

          In the legend box Linear will apear.  See below.

          Delete linear
         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.

        Moving formulas

       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.

                            Formula formatting
       

To add the correlation coefficient ( r ) to your graph:

Click in any empty cell to the right of your graph. A heavy border will appear
around the cell.

Method 1
Type in =sqrt(the R2 value from your graph) e.g. if R2 was 0.949 then type
 =sqrt(0.949). Hit Enter. The r value will appear in the cell.

Method 2
Type in =CORREL(select column of x values from table, select column of y values
 from table).  Make sure you type the comma between the two sets of data points.
Hit Enter.  The r value will appear in the cell.

Now click over formula box and under your R2 value type in r = whatever you
got above e.g. r = 0.974.  If your slope value (b) was positive the r value is
positive (See graph labeled Figure 1), if the slope value was negative then
the r value will be negative (See graph labeled Figure 2).  So if your slope
was negative type a negative sign in front of your r value.

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.

Formatting Your Graph

To change the graph axes labels
            Click twice on the item to be changed.  (Note: this is not a double click, but two               
            separate clicks.)  The cursor will appear and you can make your changes.

To format the axes labels
           Right click on the axis labael.
           Change font, font size, etc.

To format the axes numbers
          Put the cursor on the axis whose numbers you wish to format. If the cursor
           is correctly positioned over the axis it will read Horizontal (Value) axis or Vertical
          (Value) axis.
          Right click on the axis line.
          Format your axis numbers as you wish.
  
Click outside graph and scroll back to your table.

Solving for Unknown x or

       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.

       unknown x
       

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.

Click to see an example of a single line graph.

MAKING A MULTI-LINE GRAPH

       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.

       Multiline step 1

       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.

       Multiline step2

       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.

       multiline 3

       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

                  Labeling formulas

       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.

                       
                                       multiline step 4

       Repeat for the remaining lines.

       Now add the r values using directions for a single line graph.

       Note: Since the slopes for the lines in this sample graph are negative,
          
the r values will also be negative when you type them in.

            To put the legend symbol used for each line next to the label above each formula set   
            as seen below.

                                     multiline shapes 1    
       make sure your cursor is in the chart area so chart Tools is in the menu bar. 
      Click on Insert.  Click on Shapes.


   Adding shapes    

       Click on the shape that matches your legend symbol.
       Click on an empty cell. 
       Your shape will appear surrounded by a box.

                               Moving shapes

     Size your shape and change to the appropriate color.

      Size shape

        Move shape next to label over formulas.
   
                                         multiline shapes 1

       Click to see an example of a multi-line graph.
 
 

HOW TO MAKE 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)
   
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.80-36.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.77-37.72.  Third range is 37.73-38.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).

Insert histogram

     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
     Axis (5).

label axes histogram

     Repeat for Primary Vertical Axis Title.

Label vertical axis histogram


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.

Format histogram
   

  Since you are making a histogram set the gap width to 0. Click on Close.

histogram gap width


How to Insert a Mean and Standard Deviation Line on your Histogram

          Click on Insert (1).  Click on Shapes (2). Click on the straight line (3).

        Insert mean and st dev

     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.
mean_stdev_line
    
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.

Adding mean and st dev values
 

                                                mean_stdev_line_final

Click to see an example of a histogram.  

HOW TO MAKE A BAR GRAPH USING EXCEL  

  Bar graphs are used when you have data that fit into discrete categories rather than ranges. You will follow the directions above for making a histogram but,  DO NOT select the entire table.  Select only the y value column of the table.  See example below.

bar graph table

Now continue your histogram directions until you reach the section titled Formating your Histogram.
For Formatting a Bar Graph use the following directions:

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.

Now continue with the rest of the histogram directions

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 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.  

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 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.


HOW TO EMBED YOUR EXCEL GRAPHS AND TABLES INTO A WORD DOCUMENT

      Open your Excel file
      Select the table and graph
      Click on Control C or Edit/Copy
      Now open your Word document and when you come to the space where
      you want to add your Excel document
click on Control V or Edit/Paste
      To save
      Click on File
      Click on Save




SINGLE LINE GRAPH

single line graph
MULTILINE GRAPH

multiline graph


HISTOGRAM

histogram

BAR GRAPH

Bar graph