PivotChart (bar graph) of average with standard deviation as error

[Priesemut]

Hello everybody out there using Excel,

Is there a way to create a PivotChart with a group average as the height of the bars and the standard deviation as the length of the error indicator?

I managed to generate a pivot table with the average and the standard deviation as columns, but Excel would consider each column as an individual group of bars (which the one with the standard errors isn't).

[#2]

@Priesemut 

I think you can do this, but not easily with a PivotChart. Instead, do this:
1. Select a cell outside your PivotTable, then insert a standard column chart, not a PivotChart.
2. (updated based on reply from @JonPeltierMVP) Open the Data Source dialog for the chart and click "Add" to add a series for the range containing the average values in the PivotTable. Don't enter the PivotTable range in the "chart data range" field, because this will convert the chart into a PivotChart.

3. You can edit the Horizontal Axis Labels to show the names from the row header range in the PivotChart.

4. Add Error Bars to your chart.
5. Format the error bars and choose custom error bars. Click the Specify Value button.  Choose the range of your PivotTable that contains the Std Dev.

You will need to be careful about refreshing the data in the PivotTable. If new rows appear, then you may need to adjust the ranges in the chart Data Source and the error bar values.

[#3]

@Steve_SumProductCom is mostly correct, but there are a few caveats when making a regular chart from a pivot table. I describe the procedure in Making Regular Charts from Pivot Tables.

If you try to select data using the Chart Data Range box at the top of the Select Data Source dialog, the chart will instantly revert to the pivot chart you would get from the pivot table, meaning you have to plot all of the data from the pivot table. This includes the values you want to use as error bars.

You have to add series one by one using the Add button under Legend Entries, which is the left side of the bottom half of the dialog. This is more tedious than selecting the entire range, but it preserves the regular chart.

[#4]

@Priesemut 

After replying to Steve's suggestion, I thought of another approach.

Create a second pivot table: one has averages and standard deviations, the other just averages.

Make a pivot chart using the pivot table with averages only.

Using the approach Steve described (or that I wrote about in Custom Error Bars in Excel Charts), use the standard deviations in the other pivot table to define custom error bars.

This approach makes the chart more dynamic if the pivot table reshapes itself during a refresh, though you may have to reapply the error bars after refreshing the pivot tables.

[#5]

@JonPeltierMVP To clarify, would you use a PivotChart in this other approach? I think that's what you mean, because the PivotChart will automatically show a new column for any new rows that appear in the PivotTable. Then you would just need to select the ranges for the error bars to include the new rows.

[#6]

@Steve_SumProductCom - Yes, I did say pivot chart. This would mean you don't need to update the columns in the chart, just the error bars.

[#7]

Thanks. I see that now.

[#8]

@JonPeltierMVP , @Priesemut 

In addition, with two PivotTables, if filtering is needed - better with slicers connected to both.

[#9]

@SergeiBaklan Good point, easier to keep the two pivot tables synchronized.