Is there a way to automatically or programmatically insert dynamic errorbars in a pivot-chart in Excel?

Question

I have a pivot-chart that uses averages to plot each of my grouped points. I'd like to add errorbars, but Excel doesn't seem to support automatic errorbar generation (or does it?).

Should I just generate a standard graph / pivot-chart all in VBA to include this capability? I'd be losing most of the nice slicer features (if the standard chart is the solution) and it makes me sad...

EDIT: Let's look at an example!

Here is a picture of a pivot-chart I made using data from a table in another sheet.

Example

I'd like to add errorbars that are automatically calculated form the standard deviation of the mean. But since a new mean is calculted each time I filter my pivot for a specific combination of filtered fields, then I can't just add errorbars manually each time I change the filtering of my data!

Answer 1

One way to to this is to create two pivot tables. One containing the average Values ("PivotTable1" on "Sheet1") and a second one containing the StdDev ("PivotTable2" on "Sheet2"). The following code creates a clone of the current pivot table which is set to StdDev:

Sub CloneWorkSheetSetSTDEV()

Dim PIVOT_mean As Worksheet
Dim PIVOT_stdev As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

' Check if "PIVOT_stdev" sheet exists and delete it if it does
On Error Resume Next
Set PIVOT_stdev = Sheets("PIVOT_stdev")
If Not PIVOT_stdev Is Nothing Then
    Application.DisplayAlerts = False
    PIVOT_stdev.Delete
    Application.DisplayAlerts = True
End If
On Error GoTo 0

' Set the worksheet that contains the selected PivotTable
Set PIVOT_mean = ActiveSheet

' Rename the active sheet to "PIVOT_mean" if it isn't already
If PIVOT_mean.Name <> "PIVOT_mean" Then
    PIVOT_mean.Name = "PIVOT_mean"
End If

' Ensure the worksheet has charts before attempting to delete
If PIVOT_mean.ChartObjects.Count > 0 Then
    ' Delete all chart objects in the worksheet
    PIVOT_mean.ChartObjects.Delete
End If

' Copy the worksheet to the second position in the workbook
PIVOT_mean.Copy After:=Sheets(1)

' Set the new worksheet that contains the new PivotTable
Set PIVOT_stdev = ActiveSheet

' Rename the new worksheet
PIVOT_stdev.Name = "PIVOT_stdev"

' Set the PivotTable and change data field functions to StDev
Set pt = PIVOT_stdev.PivotTables(1) ' Get the first PivotTable
For Each pf In pt.DataFields
    pf.Function = xlStDev
Next pf

' Switch back to the original PIVOT_mean sheet
PIVOT_mean.Activate

End Sub

Whenever you do changes in PivotTable1 run the code above to keep PivotTable2 synchronized.

Now to add a PivotChart with errorbars just use the following code:

Sub ErrorBarsToPivotChart_OLD()
Dim cht As Chart
Dim ser As Series
Dim ptAve As PivotTable
Dim ptDev As PivotTable
Dim dataRange As Range

' Update the pivot table references to match the new sheet and pivot table names
Set ptDev = Worksheets("PIVOT_stdev").PivotTables(1) ' The new pivot table for standard deviation
Set ptAve = Worksheets("PIVOT_mean").PivotTables(1)  ' The original pivot table for mean

' Create a new chart on the "PIVOT_mean" sheet
Set cht = Worksheets("PIVOT_mean").Shapes.AddChart2( _
    251, xlColumnClustered).Chart
    
' Set the data source for the chart
cht.SetSourceData Source:=ptAve.TableRange1

' Loop through each series in the chart and set error bars
For i = 1 To cht.SeriesCollection.count
    ' Set series object
    Set ser = cht.SeriesCollection(i)
    Set dataRange = ptDev.DataBodyRange.Columns(i)
    
    ' Add error bars to the series
    ser.HasErrorBars = True
    ser.ErrorBar _
        Direction:=xlY, _
        Include:=xlErrorBarIncludeBoth, _
        Type:=xlErrorBarTypeCustom, _
        Amount:=dataRange, _
        MinusValues:=dataRange
Next i

End Sub

The codes work for me in Office365 on Windows