Excel Error Bars for Each Point: Set Custom Values

To give each point in an Excel chart a different error bar, select the chart, choose Chart Design > Add Chart Element > Error Bars > More Error Bars Options, then choose Custom > Specify Value in the Format Error Bars pane. Select worksheet ranges containing the positive and negative error distances for the series; each value is applied to its corresponding point.

What point-by-point error bars mean

An error bar adds a visual range above and below a plotted value. In a chart with four data points, custom error bars let you specify four separate upward distances and four separate downward distances. This is useful when the uncertainty, measurement precision, or observed variation differs from one category or observation to another.

Excel applies error bars to a data series, not by typing a separate setting onto each marker. For a series with several points, the Custom setting reads a worksheet range and matches its values to the points in order. The first range value belongs to the first point in that series, the second to the second point, and so on. If a chart has multiple series, configure each series separately.

Custom values describe the size of each error bar, not the endpoints of the interval. For example, a plotted value of 18 with a positive distance of 2.5 and a negative distance of 1.7 extends from 16.3 to 20.5. The bars need not be symmetric.

Prepare the worksheet data

Put the values being charted and the error distances in adjacent columns, with one row per point. Keep the order identical to the order of points in the series. A clear layout makes the ranges easier to select and makes it less likely that an error value will be assigned to the wrong point.

Group	Mean	Positive error	Negative error
A	12	1	0.8
B	18	2.5	1.7
C	15	1.5	1
D	21	3	2.2

For a chart using this layout, the four values in the Positive error column supply the upward distances, and the four values in the Negative error column supply the downward distances. Do not include the column headings in the ranges. The custom ranges should have the same number of entries as the plotted points. Use nonnegative distances: a negative error-bar value is not how you tell Excel to draw the bar downward; the negative-value range is already assigned to that direction.

Add custom error bars in Excel for Windows

Select the chart. If it has more than one series, click a point in the series you want to format so that Excel selects that series.
On the ribbon, choose Chart Design > Add Chart Element > Error Bars > More Error Bars Options. Excel opens the Format Error Bars pane. If error bars are already present, you can also select one of them and open its formatting options.
Check that the pane is formatting the intended series. Under Error Amount, choose Custom, then select Specify Value.
In the Custom Error Bars dialog, replace the entries in both the Positive Error Value and Negative Error Value boxes with the corresponding worksheet ranges. Select the cells directly or enter a range reference, then confirm with OK.
Check the chart visually. If the bars point the wrong way or appear on unexpected markers, verify the series selection, point order, and both ranges.

For the example table, assuming the headings are in row 1 and the four observations occupy rows 2 through 5, use C2:C5 for positive errors and D2:D5 for negative errors. Excel may display a full sheet-qualified reference in the dialog. That is normal. The range should contain the error distances only, not the means or category labels.

Use different positive and negative distances

Excel’s two custom fields let you make each point’s upper and lower bars different from one another. In the example, Group B has a mean of 18, a positive distance of 2.5, and a negative distance of 1.7. The upper end is 20.5 and the lower end is 16.3. Group D, by contrast, runs from 18.8 to 24. The examples illustrate how distances are applied; they do not determine whether a particular statistical method is appropriate for your data.

For symmetric error bars, put the same distance for each point in the positive and negative ranges, or select the same cell range in both boxes. If you have only one uncertainty column, you can use that column for both directions. If uncertainty is asymmetric, keep separate columns. A range of lower and upper endpoints is not a substitute: calculate the distance from each plotted value to the endpoint before supplying it.

Choose between custom and built-in error amounts

Custom values are the right choice when you already have a different uncertainty for each point, such as a confidence interval calculated separately for each estimate or known instrument tolerances for individual measurements. They give you direct control, but Excel does not validate the statistical meaning of the numbers. The ranges must be prepared correctly before you add them.

Excel also offers built-in error amounts, including a fixed value, a percentage, standard deviation, and standard error. These are convenient when one consistent rule suits the whole series. They are not equivalent to entering a separate, previously calculated error for every point: for example, a percentage applies the same percentage rule across the series, while a standard-deviation option uses a series-level calculation. If the individual uncertainty values are already in cells, choose Custom rather than expecting a built-in option to infer them.

Error bars can represent different things, including standard deviation, standard error, confidence intervals, or measurement uncertainty. Those meanings are not interchangeable. State in the chart, caption, or nearby explanation what the bars represent, and use the statistical method appropriate to the question. Overlapping error bars alone do not establish whether two groups differ significantly.

Chart types and series selection

Error bars are available for supported chart types, including common column, line, and XY (scatter) charts. The exact choices can depend on the chart type and Excel build; some chart types do not support error bars. A scatter chart is often appropriate when the horizontal position is numerical, while a column or line chart is commonly used for categories or ordered values. The chart type does not change the need to match each error value to the corresponding point.

When a chart contains several series, the error ranges belong to the series currently selected in the formatting pane. Selecting the chart’s background is not the same as selecting a particular series. If one series needs custom bars and another does not, apply the settings to the first series, select the second series, and set its error bars separately. Before closing the pane, look at the series name or the highlighted points to confirm which one is being edited.

Common problems and how to fix them

All points appear to have the same bar. Check whether a built-in amount such as Fixed value or Percentage is selected instead of Custom. Also confirm that the custom ranges actually contain different values.
The values seem shifted by one point. Remove headers from the selected ranges and make sure the first error value corresponds to the first plotted point. If the chart’s source rows were sorted or rearranged, update the error columns to the same order.
The bars look too large or point in the wrong direction. The fields take distances, not endpoint values. Put upward distances in Positive Error Value and downward distances in Negative Error Value. To check a point manually, add the positive distance to its plotted value and subtract the negative distance.
Excel rejects the selection or the result is incomplete. Ensure each range contains one usable numeric value per plotted point. Avoid selecting labels, blank cells, or extra rows. Check that the chosen range is for the series being formatted.
Bars are missing or hard to see. Inspect the chart type and the formatting pane, and check whether the bars are hidden by a very small distance, clipped by the vertical axis limits, or obscured by markers and other series. Changing the axis limits may reveal them, but make sure the adjusted scale remains clear and not misleading.

A useful diagnostic is to temporarily use noticeably different test distances for the points, then restore the intended values once you have confirmed the alignment. Do this on a copy of the worksheet or record the original values first. Also check whether the chart filters or excludes data points: the plotted order and the range order still need to match the points Excel is drawing.

Excel on Mac and Excel for the web

In Excel for Microsoft 365 on Mac, the chart-design controls and Format Error Bars pane may be arranged differently from Windows, but the underlying process is the same: select the series, open the error-bar formatting options, choose Custom, and supply positive and negative ranges. Ribbon labels can vary slightly with the app version. If the command is not visible, first select the chart and look for the chart design tab.

Excel for the web does not provide the same full set of chart-formatting controls as the desktop application for configuring custom error-bar ranges. Open the workbook in the desktop Excel app to add or change point-specific values. If a chart already has error bars, the web app may show the chart, but use desktop Excel when you need reliable access to the custom range settings.

Frequently Asked Questions

Can I give every Excel data point a different error bar?
Yes. Choose Custom error amounts and supply a worksheet range with one positive and one negative distance per point. Excel applies the values in range order to the points in the selected series.

Can I use one error range for both directions?
Yes. For symmetric bars, use the same range in both custom fields, or use two ranges with matching values. Excel will then extend each point by the same distance above and below its plotted value.

Do custom error-bar cells need to be formulas?
No. They can contain typed numbers or results calculated in worksheet cells. What matters is that the range contains numeric distances in the right point order and has the right number of values.

Can I enter the lower and upper confidence limits directly?
Not as if they were error distances. Convert each limit to a distance from its plotted value: subtract the plotted value from the upper limit for the positive distance, and subtract the lower limit from the plotted value for the negative distance.

Why do all my points show identical error bars?
Excel may still be using a built-in error amount, or the ranges may contain repeated values. Choose Custom, inspect both range references, and confirm that the cells differ where expected and align with the plotted series.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.