How To Add Error Bars In Excel. when you create line charts in Excel, you plot data points along the X and Y axes. This is useful for tracking trends in data over time, but what if you also want to track trends, how far do these data points differ from their “ideal” or how much do they change over time?.
The margin of error and the standard deviation is the most common reason people use the error bars feature in Excel charts. When you add error bars in Excel, you can view the standard error or deviation for each marker in the chart.
However, you can use error bars for different purposes. Basically, any time you want to include the high and low points along with individual data points, error bars can help you.
Error bars are available in Excel for the area, bar, column, line, scatter, and bubble charts.
The margin of error and standard deviation
Before you can learn how to add error bars in Excel, it is important to understand what the margin of error and standard deviation are.
- The margin of error is the “uncertainty†of the data point. This is commonly used in statistics when data is taken from a sample that makes up a large population. The margin of error indicates how much data from this sample may differ from the “real†results for the entire population.
- The standard deviation is the component used to calculate the uncertainty. Standard deviation is a measure of how scattered your data is. It tells you how scattered the data points are in relation to the overall mean or average of all data points.
You can calculate the margin of error and standard deviation for yourself (Excel even provides standard deviation functions</a. Or, you can add error bars to Excel and let Excel do the calculations for you.
How to add error bars in Excel
To add error bars in Excel, you need to start with an existing graph that you have already created.
1. To get started, click the chart and then click the Chart Elements button (+ symbol) in the upper right corner of the chart.
2. Select the Error Bars check box to include error bars in the graph. Then click the arrow to the right of the Error Bars selection.
3. In the pop-up window that you see, you can choose several options to customize the number of errors that will be displayed on the chart.
Before you can choose one of the pre-configured options, it is important to understand what each option means and how it works.
Error bar options in Excel
You can choose from three pre-configured Excel error bar options.
- Standard Error: Displays the standard error for each data point.
- Percentage: Excel calculates and displays a specific error percentage 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 display that value instead, you can do that.
From the error bar drop-down list, select More options at the bottom of the list. The error bar options window opens. Click the graph icon at the top to switch to the vertical error bar options.
In the Error Amount section, you can select Fixed Value, Percentage, or Standard Deviation and enter a value in a numeric field to indicate these amounts. Select Standard Error to display only the standard error for all data points.
Or you can select Custom and select the calculated standard deviation from the spreadsheet.
Select the cell that calculates the standard deviation for both positive and negative error values.
This displays a constant value that represents the total deviation of the data points. This could potentially be a wide range (as in the example above), so you might have to adjust the Y-axis scale to the lower end of the range so it doesn’t appear below the X-axis.
Customize error bars inExcel
Using the custom error bars feature is even more useful if you have calculated uncertainty in your spreadsheet. This is because error bars will then display the range of values ?? above and below each data point in the chart, which represents the range where the error is at each point in the line graph.
Other custom error bar options that allow you to fine-tune the display of these bars:
- Direction: Display the error bar only at the top (Plus), only below (Minus), or above and below (Both).
- End Style: Select End Style if you want a small horizontal line at each end of the error bar, or select No Capital Letter if you only need a vertical line.
If you choose the paint icon or the pentagon, you will see many other ways to customize the appearance of error bars in Excel.
These include changing the type and color of the error bar line, transparency and width, and more. Most people leave these settings at their default, but know they are available if you want to fine-tune the display of error bars in a chart.
Read:How to Create a Flowchart in Word and Excel
Should you add error bars in Excel?
Typically, error bars are not needed in graphs unless you are doing statistical calculations and are not expected to show the error size that exists for the dataset you are analyzing.
Error bars are actually very important when you are trying to convey correlations or conclusions using data so that your audience understands how accurate these calculations are.
How To Add Error Bars In Excel