GRAPHING YOUR DATA USING EXCEL

 These are instructions for making graphs of your data using Microsoft Excel Version 5.0. It is best to go through this page in order the first time.

    The following topics, covered below, give practical instructions for using Excel to:
  1. Opening Microsoft Excel
  2. Making a Scatter Plot: Entering Data
  3. Making a Scatter Plot: Selecting the Type of Plot
  4. Making a Scatter Plot: Error Bars
  5. Making a Column Graph: Entering Data
  6. Making a Column Graph: Calculating the Mean
  7. Making a Column Graph: Calculating the Standard Deviation
  8. Making a Column Graph: Selecting the Type of Plot
  9. Making a Column Graph: Error Bars
I do not personally like Microsoft Excel, but it is available to all of the students on the student computers and it allows you to do graphing and stats on your data set. The Excel help screens are not very useful. Here is a joke that was e-mailed to me by friends that work at Microsoft (microserfs).

A helicopter pilot was totally lost over Seattle in a thick fog. Suddenly the fog cleared a little and the pilot and the copilot realized that they were hovering next to a large office building. Seeing people working inside the skyscraper, the copilot found a large piece of paper and scrawled, "We are lost. Where are we?" and held it up to the window. The office workers saw the sign and scurried to make their own sign. "What's it say?" the pilot asked. "You are in a helicopter," read the disheartened copilot aloud. The pilot immediately turned left, flew a quarter mile and landed directly at the airport. The surprised copilot asked, "How did you know where we were?" "Oh," replied the pilot, "that was the Microsoft building." "How did you know that?" asked the copilot. "Well," the pilot knowingly replied, "They gave factually correct information that was totally useless, just like their help screens."

A few words about these instructions. They should not be necessary and you can always use the Excel help screens, but let the previous joke serve as a caveat hacker. The paths I have given you have worked the best for me but there are other ways of doing these things in Excel. These instructions are for simple sets of data. If you have complex data sets and need help, you may wish to work through these examples with a simple data set first. These instructions are for using Microsoft Excel 5.0 which is the version available on the Physiology lab computers. If you are using a different version you may have a little difficulty but the instructions should still be useful. In order to read this WebPage and work in Excel simultaneously, it helps to be a little bit Windows-savvy. To be reading this you must have a web browser open, this would be Netscape Navigator if you are on the Physiology lab computers, so the instructions will assume this configuration. If you are using Office Ď97 or some other system you need only click on the appropriate icons to open both applications.

OPENING MICROSOFT EXCEL:

1. Hit the alt and the tab key simltaneously (alt-tab).

2. This should open Program Manager. If you have other applications open it will cycle through them, but keep holding down alt and hitting tab until you get to Program Manager.

3. Open Program Manager and use the mouse to select Microsoft Applications.

4. Use the mouse to click on Microsoft Excel.

5. You can use alt-tab to toggle back and forth between this screen and Excel.

Once you have opened Microsoft Excel you will face an empty spread sheet. Do not feel threatened by its vast, lonesome, emptiness. When faced with emptiness a scientistís response is to enter data. However data must not be entered stochastically (randomly). If you have data that can best be described as pairs of values, each pair containing an x value (independent variable) and a y value (dependent variable), you will want to make a scatter plot. If these terms are unfamiliar to you, check out dependent and independent variables on the Statshelp page. If it sounds like your data set, follow the instructions below under making a scatter plot. Examples of appropriate data for scatterplots are: if you had mass and heart rate for each subject and you were interested in determining if these variables were correlated, or you might have a time and BP for each subject. If your data consists of values in categories such as a series of heart rates or plasma glucose levels for males and one for females, or a series of values recorded before (contol) and after (experimental) subjects took a drug or did something such as exercise, you will want to make a column graph. The most common type of column graph we will use in Physiology is when we compare the mean of some physiological parameter that was measured under treatment A (or in females) with the mean from under treatment B (or in males). Often one of these will be a control. Scroll down to find directions below for making a column graph.

MAKING A SCATTER PLOT

Entering Data for a scatter plot:

These instructions are for entering individual data values. If you want to plot mean values, scroll down to the instructions on column graphs or go to the
Using Excel for Statistics page for help on calculating means and standard deviations. Then enter your means as if they were individual data points.

1.Cell A1 should be selected automatically. (If someone has recently used Excel and not closed their workbook you may find yourself in their workbook. If so just click the mouse on File and select New.

2. Enter the header or name for your first set of data into cell A1.

3. Hit Return. (You can also use arrow down or use the mouse to select the next cell down).

4. This will automatically select cell A2.

5. Enter your first data value into cell A2 and press enter.

6. Continue entering each individual data value into its own cell (A3, A4, A5 etc.) until you have entered all of your data.

7. Use the arrow keys or mouse to select cell B1

8. Enter the header or name for your second set of data.

9. Hit Return, which will move you down to B2.

10. Continue entering data and hitting Return.

Selecting the type of plot:

1. Put the mouse cursor in the upper left hand corner of your entered data set.

2. Click and drag to highlight the entire data section.

3. Put the mouse cursor on the tool bar on Insert and get the pull-down menu.

4. Click and select Chart and then select the option As New Sheet.

5. This will give you a pop-up box (ChartWizard Step 1 of 5) that asks if you have selected the correct data cells. Unless you screwed up, select Next>. If you have made a mistake, you can either just click and drag to highlight the data cells you do want or select the Back key

6. This will move you to another pop-up box (ChartWizard Step 2 of 5) where you should select XY (Scatter), right in the middle of the box. Either double-click here or click it once and then click Next>.

7. This will move you to a 3rd pop-up box (ChartWizard Step 3 of 5) where you should select which type of scatter plot you want. For Physiology you will usually want type 1 (the Basic Scatter Plot) or type 3 (the Basic Scatter Plot with a grid). Choice 2 connects the data points and if there is any logical reason to order and link the data points, such as a time sequence it is a good choice. Choices 4 (semi-log) and 5 (log-log) are figures which are appropriate in some cases (log-log for any physiological variable plotted against mass) but not usually necessary for our class. Choice 6 doesnít show the data points, just smooth curves and that is appropriate for warm and cuddly humanists, but not hard-thinking pragmatic physiologists. Make your choice and double-click or hit Next>.

8. This will give you a 4th pop-up box (ChartWizard Step 4 of 5) where you can see a sketch of your plot and verify that you have got the right variable as x and as y etc. Assuming that you have not made any errors, you should select Next>. If the sample figure doesn't look like what you want, then select the Back key.

9. This gives you the 5th pop-up box in the series (ChartWizard Step 5 of 5) where you should be sure to enter an X axis label with units and a Y axis label with units. Be sure to enter a Chart (Figure) Title. If you arenít sure about what goes in these places, check out the graphing dos and doníts page.

10. If you have followed these directions, you will be looking at an excellent figure on its own page. You can toggle back and forth between data pages and chart pages using the tabs at the bottom of the page. There will be one flaw in your figure, however. The title will be at the top. To move to the bottom where it should be, click on the title to highlight it and drag it to the bottom. You are now the proud parent of a quality scatterplot, if you have done proper labeling and title creation.

Error bars

Error bars are a necessity if you are plotting mean values. Excel does not make adding error bars easy, but it is possible. The first thing that must be done is that the values for the error bars must be entered. Put the standard deviations or standard errors (see the Statshelp page for help if these are unfamiliar, or go to the Using Excel for Statistics page for help on calculating means and standard deviations) for your means in column C, aligned with the means for your Y values in column B. Then,

1. Click on your chart or on the line connecting your means in your scatterplot. This should make your next choice available or clickable. You may have to select your scatterplot a few times to make this work. I know that I just did.

2. Pull down the "Insert" menu and click on "Error Bars."

3. You will almost always want to select "Y Error Bars" in physiology because it is the variance in the dependent variable (y) for which we have a measurement.

4. Unless you have multiple lines in your scatterplot and you want to minimize overlap between them, select "Both" under the display in the Y error bar window. In the same window select "Custom."

5. Put your cursor on the "+" line after "Custom" and then go back to your data sheet by clicking on Sheet 1 at the bottom of the Excel window. This may require rearranging the different windows you have open. Do this by clicking and dragging the colored bar at the top of these windows to move them out of your way.

6. When you have a visual on your data sheet, click and drag on the column (C) containing your standard deviations or errors. This should put the Excel jargon for that column into your "+" line in the "Custom" spot of your "Y error bars" window. It will look something like this - =Sheet1!$C$1:$C$7.

7. Normally you will have the same "+" and "-" error bars in Physiology. Therefore, next you will want to put your cursor on the "-" line in the "Custom" spot of the "Y Error Bars" window and follow the same procedure for adding the standard deviation and standard error column (C).

MAKING A COLUMN GRAPH

Entering Data:

1. Assuming that you want to plot mean values in columns, you will find below directions for entering data and calculating means. You will find this useful if you want to compare values from males (or treatment A) with values for females (or treatment B). If you have already calculated the means you can jump ahead to "Selecting the type of column graph."

2.Cell A1 should be selected automatically. (If someone has recently used Excel and not closed their workbook you may find yourself in their workbook. If so just click the mouse on File and select New.

2. Enter the header or parameter name for your first treatment (or "males") into cell A1.

3. Hit Return. (You can also use arrow down or use the mouse to select the next cell down).

4. This will automatically select cell A2.

5. Enter your header or parameter name for the second treatment (or "females") into cell A2 and press enter.

6. Enter your data values, one per cell, going across. Put the values for your first treatment (or males) into row 1 and the values for treatment 2 (or males) into row 2, until you have entered all of your data.

Calculating the Mean:

1. Use the mouse cursor to select the empty cell at the end of your data in row 1.

2. Then use the mouse cursor to select the Function Wizard (fx on tool bar or Function on Insert pull-down menu)

3. Under Function Wizard select the function category Statistical.

4. Under the Statistical category select Average, then click Next>.

5. This will give you the chance to enter the appropriate cells for which you desire to know the mean (ex. B1:G1 or B1,C1,D1,E1,F1,G1).

6. After you enter the cells by typing (this can be done more easily by clicking and dragging but that is harder to explain), click Finish> and the mean will be placed into the empty cell you had highlighted earlier.

7. Do the same for your second row for second data treatment (or sex)  

Calculating the Standard Deviation:

1. Use the mouse cursor to select the empty cell at the end of your data in row 1, to the right of the cell where you have placed the mean value.

2. Then use the mouse cursor to select the Function Wizard (fx on tool bar or Function on Insert pull-down menu)

3. Under Function Wizard select the function category Statistical.

4. Under the Statistical category select STDEV, for standard deviation, then click Next>.

5. This will give you the chance to enter the appropriate cells for which you desire to know the mean (ex. B1:G1 or B1,C1,D1,E1,F1,G1).

6. After you enter the cells by typing - be sure that you don't include the mean you previously calculated in your standard deviation calculation (this can be done more easily by clicking and dragging but that is harder to explain), click Finish> and the standard deviation will be placed into the empty cell you had highlighted earlier.

7. Do the same for your second row for second data treatment (or sex)  

Selecting the type of column graph:

1. Cut and paste or copy and paste your treatment names, means and standard deviations so that you have the two treatment names stalked above one another and the appropriate means in the cells just to the right and the appropriate standard deviations in the cells just to the right of that. Use "Paste Special" and then "Values" when pasting calculated parameters such as mean and Standard Deviation.

2. Click and drag to highlight only the treatment names and the means.

3. Put the mouse cursor on the tool bar on Insert and get the pull-down menu.

4. Click and select Chart and then select the option As New Sheet.

5. This will give you a pop-up box (ChartWizard Step 1 of 5) that asks if you have selected the correct data cells. If you made a mistake you can select the Cancel key and start over. If you have the right data, select Next>.

6. This will move you to another pop-up box (ChartWizard Step 2 of 5) where you should select Column, right in the top middle of the box. Either double-click on it or click once and then select Next>

7. This should move you to another pop-up box (ChartWizard Step 3 of 5) where you will be asked to select the format for the column chart. If you are plotting mean values select option #2. If you are plotting all of the values for two different treatments then select option #1 or #4.

8. This should give you ChartWizard Step 4 out of 5 which gives you a sample of what the chart will look like. If everything is copasetic click on Next>, but if it does not look right use the Cancel key and start over or the Back key.

9. This will give you ChartWizard Step 5 out of 5 which gives you a chance to name the figure (Chart Title) and label the x and y axes. You should always exercise these options. The Chart title should always begin with Figure 1 or 2 or 3 etc. and a full explanatory title that will stand on its own to explain the contents of the figure. The x & y axes must always be labeled. On the option, "Add a legend" click "no." If you have followed the other instructions you should have your columns labeled so the legend is unnecessary.

10. Then click on Finish to see the almost finished product. Excel automatically puts the title above the figure and it should be at the bottom in science publications. Just single click on the Title at the top and drag it to the bottom.. If you have plotted mean values you must also put error bars on your columns.

Adding Error bars to a Column graph

1. Click on the bars in your graph.

2. Pull down the "Insert" menu and click on "Error Bars."

3. You will almost always want to select "Y Error Bars" in physiology because it is the variance in the dependent variable (y) for which we have a measurement.

4. For column graphs we usually use only the "+" error bars. Therefore select "Custom" in the display in the Y error bar window

5. Put your cursor on the "+" line after "Custom" and then go back to your data sheet by clicking on Sheet 1 at the bottom of the Excel window. This may require rearranging the different windows you have open. Do this by clicking and dragging the colored bar at the top of these windows to move them out of your way.

6. When you have a visual on your data sheet, click and drag on the column containing your standard deviations or errors. This should put the Excel jargon for that column into your "+" line in the "Custom" spot of your "Y error bars" window. It will look something like this - =Sheet1!$C$1:$C$7.

7. Now your figure should be done. You may want to check the Graphing Tips page to see that your figure is all it should be.


Copyright ©1999, Andrew T. Gannon. This file may be copied on the condition that the entire contents, including this copyright notice, remain intact. These pages do not constitute official Birmingham-Southern College publications. Views and opinions expressed are those of the author (Andrew T. Gannon) and comments on content of these pages should be addressed to the author.

Birmingham-Southern College Homepage
Return to Andy Gannon HomePage.
Return to BSC Physiology HomePage.