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:
- 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.
- Delete the top horizontal (secondary) axis.
- Format the Y error line so that the marker shape is change to that of a rectangle with a solid fill
- 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.
- 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:
Nice trick. In the xls file the average is missing the formula – it’s just in as a value. Quick fix though, and it all works perfectly
June 7th, 2010 at 11:07 amI’ve seen another workaround for adding an average line to a chart. You’d think that this should be a standard feature in Excel!
October 29th, 2010 at 12:34 amThe error bars are gone in 2010. This no longer works.
March 6th, 2011 at 2:06 pmThanks for the article. It was very helpful.
To Matt:
Error bars are not gone in excel 2010. They are just hidden a little.
Click on the graph and choose “Layout”.
March 28th, 2011 at 1:15 pm