How to add custom ERROR BARS in Excel charts

How to add custom ERROR BARS in Excel charts

Purpose — to add error bars to an Excel chart using custom data

Excel chart showing error bars
What are error bars?

Error bars show the degree of error or variation in a variable or parameter. They are also referred to as confidence intervals or confidence levels. Error bars plot the range of values for which there is a specified probability that the true value of a parameter lies within that range.

Error bar options in Excel charts
Error bars in Excel

There are standard options to choose from when adding error bars to Excel charts: Standard Error, Percentage, Standard Deviation, or a Fixed Value.

There is also the option to add custom error bar data, by selecting on More Options… from the Error Bars dropdown in the Chart Elements selector.

The custom error bar option is the method shown below.

Method — plot custom error bar or confidence interval data on an Excel chart

Custom Error Bars dialog box in Excel
Calculate custom error bar data

The key to adding custom error bar or confidence interval data to an Excel chart is to calculate the difference between the upper and lower error values and the series values to be plotted. Include these values in your data table to load into the chart.

In the data table example below, the data table contains the variable (series) values (Rate), and Lower and Upper confidence levels (CL). The error values to plot in the error bars have been calculated as follows:

  • Positive Error Value = Upper CL minus Rate
  • Negative Error Value = Rate minus Lower CL.

These are the values that are selected into the error values fields in the Custom Error Bars dialog box.

  • Excel takes the Positive Error Value and adds it back to the series value to plot the right-hand end of the error bar.
  • Excel takes the Negative Error Value and subtracts it from the series value to plot the left-hand end of the error bar.
Screenshot of data table and bar chart with error bars
Format Error Bars pane in Excel charts
Chart Elements selector in Excel
Add custom error bars to a chart in Excel
  • Add the error bar data to your data table for the chart — see calculate error bar data above for more details.
  • Select the chart and click on the big plus (+) sign (which appears at the top right hand corner of the chart) to open the Chart Elements options.
  • Select Error Bars | More options…
  • In the Format Error Bars pane, on the tab with the bars symbol, scroll down to find the ‘Custom‘ radio button, and click in the ‘Specify Value‘ box.
  • The Custom Error Bars dialog opens, and you can then select the error values to plot in the error bars. The positive and negative error values go into the separate selection fields.