How To Calculate Error Bars - Techwalla

Get the latest news and alerts! Later Notify

1. Tech Support
2. How To

How to Calculate Error Bars By Lee Johnson Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here. Image Credit: Compassionate Eye Foundation/DigitalVision/GettyImages

Error bars give a visual representation of the variability or uncertainty in data. Although it might seem a little dry, putting error bars on graphs underlines the important point that statistical data is usually based on limited samples of the whole population, so while data can narrow down the range of potential results, it rarely provides precise and definitive answers. The error bar calculation you need to perform depends on what you want to show, but the calculations can be performed easily using software like Microsoft Excel.

Advertisement

Error Bars on Graphs Explained

Video of the Day

Error bars on graphs tell you about the variability of the data points within the complete set or about the uncertainty in the values. Most of the time, they do this using the standard deviation or the standard error of the mean, but some also use 95 percent confidence intervals as error bars.

Advertisement

We Recommend

Tech Support How to Find the Upper and Lower Bounds in Excel By Lee Johnson Tech Support How to Calculate the P-Value & Its Correlation in Excel 2007 By Lee Johnson Tech Support How to Use Excel to Calculate a Confidence Interval By Lee Johnson

Video of the Day

Standard deviation error bars tell you how much the points of data in the group vary from the group mean, based on the variation in the whole group. These error bars are probably the simplest type and are widely used in statistics.

However, the standard error of the mean is a better approach for error bars in many cases because the standard error tells you how much variation you can expect in the mean of a group, assuming that the data is normally distributed. This option is often the better choice for error bars because it's the most directly relevant when you're comparing two means. Standard error is always smaller than standard deviation.

Advertisement

Calculating Standard Deviation With Excel

If you're using standard deviation, the error bar calculation you need is the formula for the standard deviation of a dataset. The formula is tedious to work out by hand, especially with a large dataset, but spreadsheet programs such as Microsoft Excel have a built-in function for calculating the standard deviation.

Advertisement

We Recommend

Tech Support How to Find the Upper and Lower Bounds in Excel By Lee Johnson Tech Support How to Calculate the P-Value & Its Correlation in Excel 2007 By Lee Johnson Tech Support How to Use Excel to Calculate a Confidence Interval By Lee Johnson

Find an empty cell, ideally on the same sheet as the data, and enter "=STDEV(first data point:last data point)" with the location of the first data point to the left of the colon and the location of the last data point to the right of the colon. So if your data runs from A2 to A21, you write, "=STDEV(A2:A21)" to get the standard deviation. If you have multiple groups of data you're comparing, calculate the standard deviation for each group separately.

Advertisement

Advertisement

If the data isn't all in a row or column, you can specify each cell containing data, separating each with a comma.

Calculating Standard Error With Excel

The standard error is the standard deviation divided by the square root of the number of data points in your sample, so you can easily work out the standard error in Excel based on the standard deviation calculated in the previous section. If you have a lot of individual data points, use the "Count" function to find the precise number. Enter "=COUNT(first data point:last data point)" in an empty cell to find the number. With example data running from A2 to A21, you write "=COUNT(A2:A21)," and you get a result of 20.

Advertisement

Imagine you have the standard deviation for the data in D1 and the count in C1. Enter "=D1/SQRT(C1)" to find the standard error. In general, you divide the standard deviation by the square root of the number of data points to find the standard error, so you can do it all in one step by writing "=STDEV(A2:A21)/SQRT(COUNT(A2:A21))" if you prefer.

Advertisement

Adding Error Bars in Excel

Plot the data as a graph in whichever way you prefer. For example, go to "Insert," click the drop-down arrow under "Line" and choose "Line with Markers" to add a basic line graph. To add error bars, either go to the "Layout" section of "Chart Tools" and find "Error Bars" in the "Analysis" group or – from Excel 2010 onward – click anywhere in the chart and select the plus symbol to open "Chart Elements" and choose "Error Bars" from there.

Advertisement

Advertisement

You can choose what you want to show in the error bars including the standard error, standard deviation, a fixed percentage of the value or others. Choose "More options" or "More Error Bars Options" to bring up a detailed window. Here, you can use "Custom" to choose any value or multiple values, if you have different groups, from your sheet. You have to add both a positive and a negative error value, but in most cases, you want the same number for both. Enter the relevant cell reference in both fields and click "OK" and close the error bars window to finish.

Advertisement

Advertisement

references

• Berkeley Science Review: What Are Errorbars, Anyway?
• Peltier Tech: Custom Error Bars in Excel Charts
• Microsoft Office: Add, Change, or Remove Error Bars in a Chart
• Pryor: Add Error Bars & Standard Deviations to Excel Graphs
• Roger Williams University: Adding Error Bars to Excel Graphs

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

People are Reading

1

How to Use Probability Paper in Excel

2

How to Calculate Normalized Data in SPSS

3

How to Calculate Averages in Microsoft Access

You May Also Like

1

How to Use the Outliers Function in Excel

2

How to Calculate Range in Excel

3

How to Make Column A the X-Axis in Microsoft Excel