Error Bars on pivot charts

[Dwalker]

Hi

I'm having some issues with error bars on a pivot chart I have created.

The underlying data set is grouped into 3 separate materials.
Each material has several repeat tests associated (so for example the table will have several rows of numerical data, with the first cell in each row containing "Material 1", "Material 2" or "Material 3").
A pivot table is summarising this data, so it can provide either the raw test data or averages for each material.

When I create a pivot chart from this data, all works as expected until I come to add error bars. It seems to calculate the error bars based on the standard deviation of the entire data set rather than the group. So the error bar is the same width and position for material 1, 2 and 3.

Is there any way to make it so that it uses the standard deviation for the group, ie "material 1"?

Many thanks

 

[Jon Peltier]

You need to calculate the standard deviations you want the error bars to use, and then use these as custom values for your error bars.

I've written a tutorial about Custom Error Bars in Excel Charts.

 

[Dwalker]

Thanks! Is there any way of achieving that in conjunction with a pivot table? Because users are likely to filter and unfilter the chart, changing what standard deviations would be required.

 

[Jon Peltier]

You couldn't really use the same pivot table, because all the data in the pivot table goes into the chart as plotted series, and you don't want to plot the standard deviations as series.

You could do something with a duplicate pivot table, which instead of showing the values shows the standard deviation of the values. You can keep the pivot tables in sync if you use slicers to filter them.

Or you could use some VBA that calculates new standard deviations when the pivot table is refreshed or filtered.