How to add different error bar values to each data point

Excel lets you add error bars that vary from point to point on a chart, rather than using the same value for every point. This is useful when different measurements have different levels of uncertainty — for example, if some lab readings are more precise than others, or if you collected data under varying conditions.

The process involves creating your chart first, then telling Excel which cells hold the error values for each point. You cannot do this through the standard error bar dialog alone; you need to use a helper column or reference a range of values. The method differs slightly depending on whether you want error bars on an XY scatter chart or a column/bar chart.

Key Takeaways

  • Individual error bars require a separate column of values — one error amount per data point — rather than a single fixed percentage or value.
  • XY scatter charts let you set custom error values directly by selecting a data range; column and bar charts require you to add error bars, then edit the formula in the formula bar.
  • The error values you reference must be in the same row or column order as your data points, or the bars will attach to the wrong points.
  • After you add error bars, you can click on a single bar to select just that one and change only that point's error value if needed.

Setting up your data with an error column

Before you create the chart, add a column next to your data that holds the error amount for each point. If your data is in columns A and B, put the error values in column C. Each row should have the data point in column B and its corresponding error value in column C.

The error values can be absolute numbers (like 0.5, 1.2, 3) or they can be formulas that calculate the error based on your data. For example, if you want the error to be 10% of each measurement, you can use a formula like =B2*0.1 in cell C2, then copy it down. Make sure the error column has a header so you can identify it later when selecting ranges.

Do not include the error column in your chart data yet — you will reference it separately after the chart exists. Create your chart using only the data columns (A and B in this example), not the error column.

Adding error bars to an XY scatter chart

Right-click on any data point in your scatter chart and select Format Data Series. In the panel that opens, click the Error Bars icon (it looks like a point with bars extending from it). Choose Custom from the error bar type options.

Click the small icon next to Positive Error Value to collapse the dialog. Now select the range of cells that holds your error values — for example, C2:C10 if your errors are in column C rows 2 through 10. Press Enter to return to the dialog, then click Close.

If you want the error bars to extend in both directions (above and below each point), make sure Negative Error Value is also set to the same range. If you want asymmetrical error bars — different amounts above and below — use a different column for the negative values.

Adding error bars to column and bar charts

Column and bar charts do not have a direct way to select a custom range in the dialog. Instead, add the error bars first using any method, then edit the formula that Excel creates behind the scenes.

Right-click on a data point and select Format Data Series. Click Error Bars, then choose Custom. Click the small icon next to Positive Error Value. Instead of selecting a range, type a formula that references your error column: =Sheet1!$C$2:$C$10 (adjust the sheet name and cell range to match your data). Press Enter and close the dialog.

If the error bars do not appear or attach to the wrong points, check that your error range is in the same order as your data. The first cell in the error range must correspond to the first data point, the second cell to the second point, and so on.

Editing individual error bars after creation

Once error bars are in place, you can change a single bar without affecting the others. Click on the error bar you want to modify — you may need to click twice, once on the series and once on the specific bar. When a single bar is selected, you will see a small square handle at the end of it.

Right-click and select Format Error Bars. You can now change the value for just that point. This is useful if you discover that one measurement was taken under different conditions or if you need to correct a data entry error without rebuilding the whole chart.

Checking that error bars match the right data points

A common mistake is having the error values in the wrong order. If your data is sorted differently than your error column, the bars will be misaligned. Before you create the chart, sort both your data and your error column together so they stay matched.

After you add the error bars, look at the chart and verify that the largest error values are attached to the points you expect. If a point that should have a small error bar has a large one instead, your ranges are out of sync. Delete the error bars, reorder your data and error column, and add them again.

Using formulas to calculate error values automatically

Instead of typing error amounts by hand, you can use formulas to calculate them from your data. Standard deviation, standard error, and percentage-based errors are common choices.

For standard error (useful for averaged measurements), use =STDEV(range)/SQRT(COUNT(range)). For a percentage of the data value, use =B2*0.05 for 5% error. For standard deviation of a group, use =STDEV(range). Put the formula in your error column and copy it down for each row. Excel will then reference these calculated values when you select the range for your error bars.

Frequently Asked Questions

Can I have different error bar colors for different data points?

You can change the color of all error bars in a series at once through the Format Error Bars panel, but Excel does not let you assign different colors to individual bars. If you need that level of control, consider creating separate charts for different groups of data, each with its own error bar color.

What if my error values are in a different sheet?

You can reference another sheet by using the sheet name in your formula. For example, =Sheet2!$C$2:$C$10 pulls error values from column C of Sheet2. Make sure the sheet name is spelled exactly as it appears in the sheet tabs at the bottom of your workbook.

Do I have to use the same error range for positive and negative error bars?

No. You can set positive and negative error values to different ranges if your data has asymmetrical uncertainty. For example, a measurement might be accurate to +2 but only −1. Use one column for positive errors and another for negative errors, then reference each separately in the dialog.

Why do my error bars disappear when I print the chart?

Check that your error values are not zero or very small relative to your data scale. If the error is smaller than the chart resolution, it may not be visible in print. Also verify that the error range is still valid — if you deleted rows or columns, the reference may have broken.

Can I add error bars to a line chart?

Yes. Right-click on a data point in the line, select Format Data Series, then add error bars the same way you would for a column chart. The bars will extend vertically from each point on the line.