Error Bars in Excel
Excel error bars are graphical representations of data variability. They demonstrate the precision of a measurement. Typically, the bars represent the standard deviation and standard error. They indicate how far the true value is from the determined value.
Error bars on charts provide a more comprehensive view of the data set and allow users to see the data’s margins or errors.
How to add error bars in Excel?
You can add error bars to certain types of charts in Microsoft Excel, such as line charts, bar charts, and scatter charts.
The steps below will assist you in adding error bars to your Excel charts:
- Select your chart by clicking on it.
- Select the Chart Elements option (plus sign).
- Check the Error Bars box and then click the arrow next to it. You will be presented with a list of items that you can include in your chart.
- You can incorporate the following error bars into your chart:
- Standard Error: This value represents the average error for all values in the dataset.
- Percentage: Calculates the percentage error range and amount for each value.
- Standard Deviation: This shows the standard deviation for each value.
How to add custom error bars in Excel?
In most cases, the standard error bars provided by Excel are sufficient. However, if you want to display your own error bars, you can do so as well.
To create custom error bars in Excel, follow these steps:
- Select a location in your chart by clicking anywhere on it. It will make the three chart option icons available.
- Select the plus sign (the Chart Element icon)
- To the right of the ‘Error bars’ option, click the black triangle icon.
- Select ‘More Options’.
- Check the Custom box in the ‘Format Error Bars’ pane and click on ‘Specify Value’ button.
- Enter the positive and negative error values in the Custom Error dialogue box that appears. You can delete the existing value in the field and manually enter the value (without any equal to sign or brackets). In this example, the error bar value is set to 50. Click ok.
How to make individual error bars in Excel?
Follow the below steps to make individual error bars in Excel:
- Perform steps 1 – 5 described above(How to add custom error bars in Excel?).
- Delete the contents of the Positive Error Value box in the Custom Error Bars dialogue box, place the mouse pointer in the box (or click the Collapse Dialog icon next to it), and select a range in your worksheet(C2:C5 in for example).
- Apply the same logic to Negative Error Value. If you don’t want to see negative error bars, enter 0.
- Click the OK button.
How to add horizontal error bars in Excel?
So far, we’ve only seen vertical error bars. Which are the most frequently used in Excel charting (and can be used with column charts, line charts, area charts, scatter charts)
However, horizontal error bars can be added and used. These can be used with both bar charts and scatter charts.
Here’s an example of how we plotted quarterly data into a bar chart.
The method for adding horizontal error bars is the same as the method for adding vertical error bars that we saw in the previous sections.
How to make error bars for a specific data series?
Adding error bars to all data series in a chart can make it appear cluttered and messy. In a combo chart, for example, it is common practice to assign error bars to only one series. This can be accomplished by taking the following steps:
- Select the data series to which you want to add error bars in your chart.
- Select the Chart Elements option.
The following screenshot demonstrates how to create error bars for a data series represented by a line:
As a result, only the Estimated data series that we chose has standard error bars inserted:
How to modify error bars in Excel?
Follow these steps to change the type or appearance of the existing error bars:
- To access the Format Error Bars pane, do one of the following:
- Select Chart Elements > Error Bars > More Options…
- Select Format Error Bars from the context menu by right-clicking the error bars.
- In your chart, double-click the error bars.
- Change the type, direction, and end style of the error bars by going to the Options tab (the last one).
- Go to the Fill & Line tab to modify the color, transparency, width, cap, join, and arrow type (the first one).
How to delete error bars in Excel?
It’s simple to get rid of the error bars. Simply select the error bar you want to delete and press the delete key.
This will delete all the error bars for that series. If you have both horizontal and vertical error bars, you can select only one of them to delete (again by simply selecting and hitting the Delete key).