Excel Tutorial, Part 2 - Graphing
How-To Excel: Graphing
Part 2
Note: Some functions in this tutorial cannot be performed without the "data analysis" add-in. To see if you have it, go to TOOLS. If there is no option for "data analysis," you need to add it in. Under TOOLS, click on ADD-INS...
A window will open up. Check "data analysis" and it will automatically install for you (for free)
Note: click here to download the data used for this tutorial
Graphing Data:
To graph data, click on the button that looks like a bar graph.
Follow the chart wizard.
1. Select the graph you want—for most things, a scatter plot is preferable.
2. Click NEXT
3. Follow the instructions on the wizard:
4. If you didn’t highlight your data before you clicked on the chart wizard, it will ask you to select the cells that contain the data and labels you want in your chart. Click on the SERIES tab and highlight the data you want in the chart. The independent variable goes on the X axis, the dependent variable goes on the Y axis.
5. Click NEXT
6. Click the TITLES tab and make sure that each of your axes are labeled and that your chart has a title.
7. Click NEXT
8. Decide where to put your chart. If you want to print it, you should put it as a new sheet, and title the sheet accordingly. If you want to be able to look at the chart alongside the data, put it as an object in your current sheet.
Helpful links:
www.howe.k12.ok.us/~jimaskew/excelgraph.htm
www.millerbiology.com/graphingactivity.htm
Adding a trendline:
You may notice in the above picture that there’s a line running through the scatter plot. This can give a general indication of the trend of your data. Here’s how to do it:
1. Click anywhere on your chart.
2. Go to CHART > ADD TRENDLINE
3. Click the OPTIONS tab and check the “display equation” and “display R-squared.”
4. Hit OK
Histograms
Histograms are a way to display the distribution of quantitative variables (e.g. scores) that vary along a continuum without any necessary gaps.
1. Enter data
2. Enter “bins” on the same data table. “Bins” is the term used to describe the categories under which the scores fall. For example, if you want to group this data into sets of five, your first bin would be 75 (and all scores under 75 would be placed into this bin), the second would be 80 (and all scores from 75-79 would be placed into this bin), etc. up to the highest possible score.
3. Go to TOOLS > DATA ANALYSIS and click on HISTOGRAM
4. For “input range,” highlight the scores
5. For “bin range,” highlight the place in the table where you entered the bin ranges
6. Check “chart output” and click the “new worksheet ply” option button.
7. Click OK
8. Your sorted data and corresponding histogram are displayed on a new sheet.
Adding Error Bars:
Once your histogram is done, you can add error bars (you can do this to virtually any graph). Error bars are an easy way to show graphically the standard deviation or standard error of your data.
1. Right click on the histogram bars
2. Select “Format Data Series”
3. Select the “Y Error Bars” tab
4. Click on “both” for both positive and negative errors
5. Pick either standard deviation or standard error
6. Click OK
Back to Part 1
Back to Computer Tutorial Main Page
References:
All images acquired from Microsoft Excel. Data is fictitious.
