How to Make a Chart With Average & Standard Deviations in Excel
By Laura Gittins
In statistics, an average represents the sum of a set of data, divided by the total number of data entries in the set. The standard deviation shows the dispersion of the values of a data set from their average. Excel has two functions, "average" and "stdev," respectively, that calculate these two values from raw data that you would enter into a spreadsheet. After calculating the average and standard deviations, you can show these values graphically by plotting them on a line chart.
Open a new Excel spreadsheet. Enter your raw data in a logical manner. For example, if you have 10 values of data, type your headers in cells A1 through A10, and then the data values in cells B1 through B10. Use additional columns for further data entry.
Click the cell where you want to display the average of your data. Type "=AVERAGE(B1:B10)" (without quotes). This calculates the average of the values in the range B1 to B10. Repeat this for every other column of data you have.
Click the cell where you want to display the standard deviation of your data. Type "=STDEV(B1:B10)" (without quotes). This calculates the standard deviation of the values in the range B1 to B10. Repeat this for every other column of data you have.
Click and drag your mouse across all of the cells with your averages to highlight them.
Click on the "Insert" tab. Click on the "Line" chart and then "Marked Line." This will create a line chart for your averages with markers for each data point.
Click on the line on the chart to select it. If you have more than one line, perhaps because you have more than one set of data, select the line associated with the first set of data.
Click the "Layout" tab. Click on the "Error Bars" button and then "More Error Bars Options" on the drop-down menu. This opens the Format Error Bars menu, where you set up the display of standard deviation.
Click the "Custom" radio button and then the "Specify Value" button.
Click the "Collapse" button in the Positive Error Value section of the dialog box. Click and drag your mouse over the range of standard deviation data on your spreadsheet to select it, and then click the "Expand" button in the dialog box. Repeat this to select the same data set for the Negative Error Value section of the dialog box. Click "OK."
Repeat step 6 to add the error bars to to any other lines on your graph if you have additional data series represented. Click "Close" to close the Format Error Bars dialog box.
Customize the chart title, axis titles, labels and colors of your chart as needed to finish making your chart that displays average and standard deviation.
Laura Gittins has been writing since 2008 and is an expert in document design. She has a Bachelor of Science in English, Professional and Technical Writing. She has written education and document design articles for eHow.