When creating line charts in Excel, plot data points along an X axis and a Y axis. This is useful for the trend of the data over time, however, and if you also want to have a trend to which How far away are these data points from their “ideal” or how much do they vary over time?
Error margin and standard deviation are the most common reasons people use the error bar feature in Excel charts. When you add error bars to Excel, you may see an error or standard deviation for each marker in the chart.
However, you can use error bars for various purposes. Basically, whenever you want to include high and low points next to individual data points, error bars can help.
Error bars are available in Excel for area, bar, column, line, scatter, and bubble graphs.
Margin of error and standard deviation
Before you know how to add error bars to Excel, it is important to understand what both the margin of error and the standard deviation are.
- Margin of error is the “uncertainty” of the data point. It is typically used in statistics when data come from a sample that constitutes a larger population. The margin of error indicates the extent to which the data in this sample can vary from the “real” result of the entire population.
- Standard deviation is a component used to calculate the margin of error. The standard deviation measures the distribution of your data. Indicates how much data points are distributed around the average or overall average of all data points.
You can calculate the margin of error and standard deviation for yourself (Excel even provides standard deviation functions). Or, you can add error bars to Excel and let Excel do the calculations for you.
How to add error bars to Excel
To add error bars to Excel, you must start with an existing chart that you have already created.
1. To begin, click on the chart and select the Chart Elements button (the + symbol) at the top right of the chart.
2. Select the Error Bars check box to enable error bars in the graph. Then select the arrow to the right of the error bar selection.
3. The pop-up box you see gives you several options for setting the amount of error that the error bars will show on the graph.
Before selecting one of the preconfigured options, it is important to understand what each option means and how it works.
Error bar options in Excel
You can choose from three preconfigured error bar options in Excel.
- Common mistake: Displays the standard error of each data point
- Percentage: Excel calculates and displays the specific error rate for each data point
- Standard deviation: Excel calculates and displays the standard deviation (one value) for all values
The actual calculation of the standard deviation is somewhat complex and beyond the scope of this article.
If you want to calculate the standard deviation yourself and show this value, you can do so.
In the error bar drop-down box, select More options at the end of the list. The Error Bar Options window will open. Select the graph icon at the top to switch to the vertical error bar options.
Under Error Amount, you can select Fixed Value, Percentage, or Standard Deviation and enter a value in the numeric field to specify these amounts. Choose Standard Error to show only the standard error of all data points.
Or, you can select Customand choose the calculated standard deviation from the spreadsheet.
Select the cell with the standard deviation calculation for both Positive error value and the Negative error value.
Displays the constant value represented by the global deviation of the data points. It may be a wide range (as in the previous example), so you may need to adjust the Y-axis scale at the lower end of the range so that it is not shown below the X axis.
Customizing error bars in Excel
Using the Custom Error Bars feature is even more useful if you have calculated the margin of error in the spreadsheet. This is because the error bars will display the range of values above and below each data point in the graph that represent the interval where the error is at each point in the line graph.
Other custom error bar options that allow you to fine-tune how these bars are displayed:
- Direction: Displays the error line only above (Plus), only below (Less), or above and below (Both).
- Final style: Tria Cap if you want a small horizontal line at each end of the error bar, or choose No head if you only want the vertical line.
If you select the paint icon or the pentagon icon, you will see many other ways to customize the appearance of the error bars in Excel.
These include changing the type and color of the error bar, transparency and width, and more. Most people leave this setting as the default, but they know it’s available if you want to fine-tune how your error bars appear on the graph.
Need to add error bars to Excel?
Normally, error bars are not required in graphs unless you perform statistical calculations and have to show the size of the error that exists for the sample dataset you are analyzing.
Error bars are really very important when you try to convey correlations or conclusions through data, so that your audience understands the accuracy of these calculations.