How to Create a Standard Deviation Graph in Excel

The standard deviation is one of the most important pieces of statistical information about any set of data, and learning how to graph standard deviation in Excel is an essential skill for anybody analyzing data. The process is similar in recent and older versions of Excel, but the option you use is easier to access in versions from Excel 2013 onward. After learning the basics of a standard deviation graph, you can create one using any version of Excel.

Image Credit: Rawpixel/iStock/GettyImages

Standard Deviation Graph Explained

A standard deviation graph is a graph of the mean values of a set of data with the standard deviations added. The standard deviation is a measure of the variation around the mean in the dataset, so graphing standard deviations alone doesn't provide much useful information. A standard deviation graph can be a line graph, column chart or bar chart with the standard deviations added as error bars around the mean values. A standard deviation graph in Excel is a graph with error bars.

Standard Deviation Graph: Excel 2013 Onward

For Excel 2013 through Excel for Office 365, the process of producing a standard deviation graph has remained the same. The first step is to produce a simple graph of the data in any format you want. A simple approach is to use a 2D column graph, but line graphs or horizontal bar graphs are also suitable. Select the data and produce a basic graph of the means.

For example, you might have the average monthly expenditures from different departments in a company. Next to these means, add the standard deviations in separate cells. Do this by typing "=STDEV" into the cell where you want the standard deviation to appear and then selecting the data for which you want the standard deviation. For example, if the data for each department is in columns, running from column B to column F and from row 2 to row 40, you type "=STDEV(B2:B40)" into the cell for the standard deviation of the first column and so on for the other columns.

Click on the chart and then select the "+" symbol for "Chart Elements" that appears beside it. Choose "Error Bars" from the options that appear. Choose "More Options" at the bottom of the submenu to open the "Format Error Bars" window with options for the directions of vertical and horizontal error bars. On the appropriate option, which is usually vertical, choose "Both" under "Direction" to tell Excel that you want error bars above and below the means and make sure "Cap" is selected. Under "Error Amount," choose "Custom" to bring up a window that allows you to select the cells containing the standard deviations. Do this for both the positive and negative error fields. Click "OK" to confirm the error bars.

Excel Standard Deviation Graph: Older Versions

To produce an Excel standard deviation graph in Excel 2010 or older versions, you use a process that is similar to the one used in the more recent versions of the software, but you access the window differently. Produce a graph and compute the standard deviations as previously described.

Click on the graph and then choose the "Layout" tab from the "Chart Tools" section at the top of the window. In the "Analysis" section, click the drop-down arrow beside "Error Bars" and choose "More Error Bars Options."

This takes you to the same "Format Error Bars" window described in the previous section. From there, the process is the same as described for Excel 2013 and newer versions of the software.

references