Adding error bars at once for multiple series (in a line graph)

Question

When adding error bars to a line graph, Excel requires me to add the error bar for each series one by one. This is useful when you have few series and a lot of data points, but I have the reverse: a lot of series and few data points. So I want to be able to add error bars for all series at once for one data point and repeat manually for the other data points. How can this be accomplished?

(When I say error bars, I mean error bars with custom values, which are precalculated standard deviations on the datasheet)

Answer 1

Hello,

as far as I know, error bars can only be applied to one series at a time. There is no ribbon or dialog command to create error bars for more than one series in one swoop.  The only way to automate error bar creation is with VBA.

The following code could be adjusted to your needs. Here a line chart has seven series. Each series has three data points. A table in the workbook has the error bar values for each data point. A loop sets the number of the series and the range of the error bar values and then applies the error bars with the custom values pointing to these ranges. 

Sub setErrorBars()

Dim TheSeries As Integer

Dim i As Integer

Dim ErrorValue As Range

Dim ErrorRange As Range

TheSeries = 7 ' how many series in the chart

' the first set of error bar custom values is in this range

Set ErrorRange = ThisWorkbook.Sheets("Sheet1").Range("B12:B14")

ActiveSheet.ChartObjects("Chart 1").Activate

For i = 1 To TheSeries

    Set ErrorValue = ErrorRange.Offset(0, i - 1) ' offset moves the range to the right

    With ActiveChart.SeriesCollection(i)

        .HasErrorBars = True

        .ErrorBar Direction:=xlY, _

             Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, _

             Amount:=ErrorValue, MinusValues:=ErrorValue

    End With

Next i

End Sub

Answer 2

Thank you thank you!! You've solved my long standing issue!

Since excel automatically changes the X and Y axis of graphs depending on XY dimensions, I have changed your script slightly below to set error bars to line graphs that have more rows than columns. Transpose the graph, then run this script. I hope this helps someone else :).

Sub setErrorBars2()

Dim TheSeries As Integer

Dim i As Integer

Dim ErrorValue As Range

Dim ErrorRange As Range

TheSeries = 8 ' how many series in the chart (# of rows)

' the first set of error bar custom values is in this range

Set ErrorRange = ThisWorkbook.Sheets("Sheet1").Range("B5:E5")

ActiveSheet.ChartObjects("Chart 1").Activate

For i = 1 To TheSeries

    Set ErrorValue = ErrorRange.Offset(i - 1, 0) ' offset moves the range down by one

    With ActiveChart.SeriesCollection(i)

        .HasErrorBars = True

        .ErrorBar Direction:=xlY, _

             Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, _

             Amount:=ErrorValue, MinusValues:=ErrorValue

    End With

Next i

End Sub