How To Create Custom Error Bars In Microsoft Excel
In this article, you will learn how to create custom error bars. Error bars is an excellent functionality in Excel that provides flexibility to the user to show detailed chart. We can use error bars to show variability in the KPI’s, which are plotted in the chart.
Let us take an example to understand:
- We have Sales Report that contains Month & Sales.
- Click on any cell in the above table of data.
- Click on Insert tab
- From Charts group, click on Column Charts
- Select Clustered Column Chart option
- Drag the Chart to the empty space (away from table)
- Click on the Chart & click on Chart Elements on the upper right hand corner (plus sign)
- Select Error Bars, and you will find three options to select i.e. Standard Error, Percentage, Standard Deviation & More options
- If we click on Standard error then, the Chart will look like this:
- If you click on More Options, you will find the following Error bar options
- In Vertical Error bar, we can show the error bar by Minus, Plus & both as well.
- The End Style has two options, Cap & No Cap; the default selection is Cap and if we select No Cap then, the chart would look like the below shown snapshot:
- There are various Error Amount available to play with like Fixed value, Percentage, Standard deviation, Standard error, Custom, etc.
- With Custom option, you can show custom error value that you want it to appear.
To show Custom value; we need a helper column that contains errors for each month.
After adding Error column; the data looks like the following snapshot:
- From Format Error Bars, select Custom & click on specify value
- The Custom Error Bars dialog box will appear
- In Positive Error Value, select the range C2:C13
- Select the same range again in Negative Error Value
- Click on OK button; and now we have Custom Error Bars.