In our previous article we covered adding an average line to a bar chart. At times, however, it may be useful to show an average line for each period or group when the chart consists of more than one such period or group. The advantage here is apparent – not only can the reader view the data points individually, he/she is also able to build a map of how various groups are placed with in relation to each other.
The data for the above bar chart is shown below. For the purpose of this exercise, we have taken the year-on-year GDP growth numbers under various US Presidents for a given time period.
Create a bar chart
We begin by creating a simple bar chart with the shaded column being used as the value range and the first two columns jointly being used as the X-axis label range. (Using more then one column/row as the axis label produces the multi-tire label effect as you can see in the charts above). We then clean the chartjunk from the bar chart.
Insert new series for the average lines in the bar chart
Before we insert a new series for representing the average lines, it is important to construct the data properly. We insert a fourth column which contains the average of the values. In the example above, it is labelled “Average”.
Right click on the new chart series and convert to X-Y chart. Once done, this is how the chart would look like.
Add Horizontal Error Bars to the new chart series
The final step in the process is to add the X Error bars (shown as horizontal lines) to the bar chart. To ensure that the bars properly cover only those values that they represent the average of, we add the fifth column to the chart data labelled “X Axis Error” that stores the information on how long each error bars needs to be. We use this information as the custom +ve error amount.
As you can see multiple average lines appear over the chart and cover only those bars for which they represent the average of. You can turn on the label for the value to ease comprehension. All that needs to be done is to dress up the chart – perhaps add a little color if you please to provide proper separation and contrast to the various groups.
For those who prefer lines over bar charts, here’s for you:
You can download a sample worksheet with the bar chart with average lines for each group in chart here or click on the button below: