Add Error Bars in Excel – Easy Step by Step Guide

Depending on what kind of data we are working with the values aren’t always going to be one hundred percent accurate. Sometimes the values represented in a chart, especially forecasted values over time, may have a slight range of error that could be associated with them.

When creating visualizations using this data it can be really handy to have a way that this potential range of error is also factored in – this is where error bars come in super handy!

In this simple step by step guide we will run through how to add error bars to charts in Excel.

What are Error Bars?

Error bars are basically what they sound like – bars that sit over the top of a chart to clearly indicate the potential error within a set data point. On a bar chart they look like the below:

The overall length of the bar indicates the potential error possible above and below the end point of the chart itself. Looking quickly at the above example that means that while the first value is sitting at the 40,000 line, in reality it could end up being somewhere around 37,000 to 43,000.

How to Add Error Bars in Excel

Excel makes it super easy to add Error Bars to charts. To do so, simply click anywhere on the chart, then press the green plus sign in the top right corner and then select Error Bars:

You will see that there are a few different options to work with here. A brief summary of each is as follows:

  • Standard Error: The standard error across all of the values in our dataset
  • Percentage: A specified percentage range above and below the actual value. Defaults to 5 but can be customised.
  • Standard Deviation: The Standard Deviation for all of the values in our dataset. Default value is at 1.

Let’s go with the Standard Error option, and see how it looks:

Quite straight forward! Assuming you are happy with Standard Error then this is pretty much all there is to it.

Let’s have a look at some other options and formatting.

Formatting our Error Bars

This time let’s use a line chart as Error Bars are quite versatile in their use. In some ways this is a better way to visualise it as its easier to read without the bar itself crossing over into the visual like with a bar chart. To start with we have a basic line chart, and the default option for a percentage error bar:

If we click on the error bars themselves, or in the plus button menu we head over to More Options… we will see some formatting and data controls.

Let’s say we have a much broader range of error in our data. We can very easily modify the percentage by typing in a new value. In this case we will go with 15% as the data we are working with isn’t known to be very accurate. On top of this we also don’t care about any potential lower figures but only want to see how much higher they could end up being. We can very easily modify our bars to only go in a single direction by clicking either Plus or Minus in the controls – in this case we will select plus.

We can also head over to the Fill and Line tab to modify the look and feel of the bars. Keeping it simple we have changed the colour to a dark red, and increased the width of the bars just to make them stand out a bit more. You can also change things like transparency, make arrows and more.

The end result looks like the below:

The changes are small but really do make a big difference in readability!

This sums up our quick tutorial on how to add Error Bars to charts. For more handy guides on working with Excel, be sure to check out the rest of our tutorials here.

Similar Posts