How to Add and Modify Error Bars in Excel

A naked bar graph makes your data look uniform. In most cases, that’s misleading.

Wouldn’t it be nice if you could show the variability in your data right in the graph?

That’s what error bars are for. They show (or predict) variability.

It might not seem like much, but error bars can make a big difference in the quality of your graph!

Kasper Langmann, Co-founder of Spreadsheeto

How to turn error bars on

These types of charts can use error bars:

  • Column
  • Line
  • Bar
  • Area
  • Scatter
  • Combo

We’ll concentrate on column and line charts, but the concept is the same for other chart types.

Adding error bars is as easy as adjusting any other chart option.

When you click on your chart, three buttons appear outside the upper-right corner.

Chart options button

Click the first button to display options.

Chart options menu

The image above shows the options for a column chart. Other charts may show fewer options when you click this button.

Kasper Langmann, Co-founder of Spreadsheeto

Click the checkbox next to “Error Bars” to turn them on.

The default error bars show the standard error.

What if you want to display something other than standard error for your error bars?

Get your FREE exercise file

It’s easiest to learn when you’re following along.

Grab the free sample workbook below and learn to add error bars on real data!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Error bar options

You could turn on error bars and display the standard error.

But let’s flex our Excel muscles a bit. 💪

Click the arrow on the right to reveal additional options.

Error bars option submenu

From this menu, you can select Standard Error, Percentage, or Standard Deviation error bars.

Excel automatically calculates both the standard error and the standard deviation for the data used in your chart.

Selecting Percentage uses the default value of 5% above and below the charted data value.

Error bars

But what if you want to use a value other than 5%? Excel makes it easy.

Kasper Langmann, Co-founder of Spreadsheeto

Select More Options… from the menu and a new window will slide in.

Here are some of the options you’ll see:

  • Whether the error bar extends above the bar, below, or both.
  • Whether the error bar has a cap.
  • The error amount that Excel displays.
Error bars more options

You can select the default (standard error) from the final list. But you now have more options, too.

Selecting the radio button next to percentage allows you to change the default 5% to any other value.

You can also select any number of standard deviations or specify a fixed value.

Let’s take a look at how to apply this to some real data.

Kasper Langmann, Co-founder of Spreadsheeto

Open the example workbook and select the first sheet (“Women’s Height”).

You’ll see a simple table with women’s names and their height in inches:

women-height

From this data we can easily create a normal column chart.

Women's height initial chart

Next, select the chart and click the plus sign in the upper-right corner.

Then click the error bar checkbox to turn on the default error bars.

Women's height turn on default error bars

The chart instantly updates with error bars.

Women's height chart std error

Now it’s your turn!

What do you think will happen when you change the error bars to show standard deviation?

Make a prediction, then add standard deviation error bars and see what happens.

Use the error bar options to try out some other types of error bars, too.

Kasper Langmann, Co-founder of Spreadsheeto

Dynamic error bars

The last error amount option the More Options… window is Custom.

We’ll use this to create dynamic error bars for sales projection data.

Open the second sheet of the example workbook.

Sales projections

Click the plus button and select Error Bars > Custom > Specify Values to bring up a new window.

You can now specify different positive and negative error values.

dynamic-error

The default value is a one-value array that includes the number one, but let’s change that to something more useful.

Kasper Langmann, Co-founder of Spreadsheeto

So how can we use this to create error bars that Excel automatically updates?

By entering a cell reference.

Now, Excel looks for any changes to that cell. 👀

And if it sees that you’ve made a change, it updates the error bars.

I’ve included a table of possible sales fluctuations that we’ll use to create these error bars.

market-fluctuations

Let’s assume you want to build error bars to show what happens if you sign a large new client or lose your current largest client.

To do so, update the cell reference in the Positive and Negative Error Value fields:

Custom different positive negative

Now change the values in those cells and watch the error bars update automatically!

You can also link the positive and negative error bars to the same cell.

Try linking them to the Market Fluctuations cell. Then change the value to see what happens.

Wrapping Things Up

Once you know how to add error bars to your charts, you may wonder how you’ve lived so long without it!

Error bars add a professional touch that communicates more information without cluttering your charts.

After you’ve properly graphed your data, give error bars a whirl. You might never create a graph without them again.

2019-07-30T09:12:56+00:00