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.

img1

  • Click on any cell in the above table of data.
  • Click on Insert tab
  • From Charts group, click on Column Charts

img2

  • Select Clustered Column Chart option

img3

  • Drag the Chart to the empty space (away from table)

img4

  • 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

img5

  • If we click on Standard error then, the Chart will look like this:

img6

  • If you click on More Options, you will find the following Error bar options

img7

  • 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:

img8

  • There are various Error Amount available to play with like Fixed value, Percentage, Standard deviation, Standard error, Custom, etc.

img9

  • 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:

img10

  • From Format Error Bars, select Custom & click on specify value

img11

  • The Custom Error Bars dialog box will appear

img12

  • In Positive Error Value, select the range C2:C13

img13

  • Select the same range again in Negative Error Value

img14

  • Click on OK button; and now we have Custom Error Bars.

img15

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube