What custom error bars do and when you need them

Custom error bars in Excel show the range of uncertainty around each data point in a chart. Instead of using a fixed percentage or standard deviation that Excel calculates for you, custom error bars let you specify your own values — useful when you have measured the actual error for each point, or when your uncertainty varies across the dataset.

You need custom error bars when your data comes with its own error measurements. For example, if you ran an experiment three times and got slightly different results each time, you might calculate the standard deviation for each measurement and want to display that specific range. Or if you're showing survey results where the margin of error differs by group, custom bars let you show that variation directly rather than forcing the same error range across all points.

Excel's built-in error bar options (fixed value, percentage, standard deviation) work fine when all your data points share the same uncertainty. Custom error bars are the tool you reach for when they don't.

Key Takeaways

  • Custom error bars require a separate column of error values in your spreadsheet, one for each data point you're charting.
  • You create a chart first, then add error bars to it by right-clicking the data series and selecting "Format Data Series" or "Add Error Bars".
  • In the error bar dialog, choose "Custom" and then specify the range containing your error values for both the positive and negative directions.
  • If your error values are the same in both directions, you can use the same range for both positive and negative error bars.
  • Excel will not automatically update error bars if you change the values in your error column — you must edit the error bar range manually.

Set up your data with a separate error column

Before you add error bars, your spreadsheet needs three columns: one for your category or x-axis labels, one for your measured values, and one for the error amount for each point. The error column should contain only the size of the error range, not the upper and lower bounds — Excel will add and subtract these values from your data points automatically.

For example, if your data point is 50 and your error is 5, you put 50 in the data column and 5 in the error column. Excel will then draw the bar from 45 to 55. If your error is asymmetrical (different above and below the data point), you'll need two error columns — one for the positive direction and one for the negative.

Make sure your error values are in the same order as your data points. If row 3 contains your second measurement, the error for that measurement must be in row 3 of the error column, not somewhere else.

Create your chart before adding error bars

Insert your chart first using only the data column and your category labels. Go to the Insert tab, choose your chart type (column, line, scatter, or bar charts all support error bars), and select your data range. At this stage, ignore the error column — you'll add it after the chart exists.

Once your chart is in place and you can see all your data points plotted, you're ready to add the error bars. The error column stays in your spreadsheet but not in the chart data range yet.

Add error bars through the Format Data Series dialog

Right-click directly on one of the data points in your chart (the bars, dots, or line itself — not the background). A context menu will appear. Look for an option that says "Format Data Series" or "Add Error Bars" depending on your Excel version. Click it.

In the dialog that opens, find the Error Bars section. You may need to click on "Error Bars" in the left sidebar if it's not already visible. Look for a dropdown that says "Error Amount" or similar, and select "Custom" from the list.

Once you choose Custom, Excel will show you fields for "Positive Error Value" and "Negative Error Value" (or similar labels). These are where you tell Excel which cells contain your error amounts.

Specify your error value ranges

In the Positive Error Value field, click the small button with an arrow icon next to the text box. This lets you select a range from your spreadsheet instead of typing it. Click and drag to select all the cells in your error column that correspond to your data points, then press Enter. The range will appear in the field — it should look something like $D$2:$D$10 if your errors are in column D, rows 2 through 10.

For the Negative Error Value field, do the same thing. If your error is symmetrical (the same amount above and below each data point), use the exact same range. If your error is asymmetrical, select a different column that contains the negative error amounts.

Click OK. Excel will draw error bars on your chart using the values you specified. Each data point will now have a bar extending up and down (or left and right, depending on your chart type) by the amount you provided.

Adjust error bar appearance and direction

After you add error bars, you can change how they look. Right-click on the error bars themselves (not the data points) and choose "Format Error Bars". Here you can change the line color, thickness, and style. You can also choose whether the bars extend in both directions, only up, or only down — useful if you want to show only the positive uncertainty or only the negative.

If you need to change which cells contain your error values, right-click the error bars again, choose "Format Error Bars", and look for the Error Amount section. You can edit the range the same way you entered it the first time.

Common problems and how to fix them

If your error bars don't appear, check that you selected the correct range and that the range contains only numbers. If Excel shows an error message about the range, make sure you included the dollar signs (like $D$2:$D$10) and that the range has the same number of cells as your data series.

If your error bars look wrong — too large, too small, or pointing the wrong direction — verify that your error values are the right size. Remember that the error column should contain the distance from the data point to the edge of the error range, not the absolute upper or lower value. If your data point is 100 and you want the error bar to go from 90 to 110, the error value should be 10, not 90 or 110.

If you edit the numbers in your error column, the chart will not update automatically. You must right-click the error bars, choose "Format Error Bars", and re-enter or re-select the range to see the new values reflected in the chart.

Frequently Asked Questions

Can I use different error values for positive and negative directions?

Yes. In the Format Data Series dialog, enter one range in the Positive Error Value field and a different range in the Negative Error Value field. Each range should contain the same number of cells as your data series, with the error amounts in the same order as your data points.

What if my error values are percentages instead of absolute numbers?

Convert them to absolute numbers first. If your data point is 50 and your error is 10%, calculate 50 × 0.10 = 5 and put 5 in the error column. Excel's custom error bars work with actual values, not percentages.

Do error bars work on all chart types?

Error bars work on column, bar, line, scatter, and bubble charts. They do not work on pie charts, doughnut charts, or area charts. If you try to add error bars to an unsupported chart type, the option will be grayed out.

Can I copy error bars from one chart to another?

No, you must add error bars to each chart separately. However, if both charts use the same data and error columns, you can use the same ranges when you set up the error bars on the second chart, which will be faster than recalculating.

What happens if I delete a row from my data?

The error bars will not update automatically. If you delete a row, you must edit the error bar range to match your new data range. Right-click the error bars, choose "Format Error Bars", and adjust the range to exclude the deleted row.