A Bar Chart is used to represent data using horizontal bars. One way in which you can augment a bar chart is to add an average line.

Create a bar chart

Create a bar chart in Excel using any particular data set at your disposal. In our case, we took the Forbes list of the richest people on earth (2009 figures). Here’s how the data looks like.

To create a bar chart, select the data range shaded in grey and insert a bar chart. The output of this step would look something like this. Please note that we have eliminated the chartjunk from this one and therefore this looks a bit better than the default chart Excel would’ve produced.

Add a new series to the bar chart

Now to adding the average line. If you noticed, we added another row in our dataset that shows the average of the records in the original data set. (There are a number of way in which an average line can be added to a chart. The below highlights one such method.) Add a new data series to chart and specify the average as the value to plot as shown below.

Convert new series to a XY Chart

Once you’ve added another series to the chart, select it and then change the chart type to XY chart.

The result is a combination chart with bars and a single point.

Specify X-Y coordinates of the point in the combination bar chart

To properly align the point with the right X-Y coordinates, we edit the data source and change the X and Y value of the point. We set the Y value to 0 so that it aligns with the bottom axis. We set the X axis to the average.

Add Y Error bars to the series

To add the average line that cuts across the horizontal bars, we double-click the point and select the tab labelled “Y Error Bars”. In the error amount, enter the appropriate values. In our case we fill the custom values of +ve and -ve with the average.

Format Y Error Bars and the chart

At this point we carry out the following steps:

1. Edit the vertical axis Scale so that is has minimum = 0 and maximum = 1. Once set, you can then go ahead and delete the scale using the delete key.
2. Delete the top horizontal (secondary) axis.
3. Format the Y error line so that the marker shape is change to that of a rectangle with a solid fill
4. Format the Y error line so that the it is changed to a dotted line with a lighter shade that goes well with the overall chart.
5. Format the chart to that the bottom (primary) axis scale is visible

Here is a snapshot of the bar chart as it would look like as you carry out the above steps. The average line that cuts across the bars, would move as the chart data gets modified.

And here’s the final bar chart. For the stickers for convention, you can arrange the data series in the descending order. You can even add a label the average line which lets the reader know where the average lies. (averages lie, don’t they !)

You can download a sample worksheet with a bar chart with average line here or click on the button below: