Forum Discussion
PivotChart (bar graph) of average with standard deviation as error
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 ...
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.
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.
