Categorized | excel chart

Bar Chart with Average Line




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.
bar-chart-data-with-average

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.

bar-chart-in-excel

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.

bar-chart-in-excel-inserting-series

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.

excel-chart-selection

The result is a combination chart with bars and a single point.
bar-chart-in-excel-combination-chart

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.

adding-x-values-to-bar-chart

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.

bar-chart-with-average-line-raw

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 !)
bar-chart-with-average-line-and-label

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

bar chart with average line

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Alex Kerin wrote:

    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

  2. Martin wrote:

    I’ve seen another workaround for adding an average line to a chart. You’d think that this should be a standard feature in Excel!

  3. Matt wrote:

    The error bars are gone in 2010. This no longer works.

  4. evsap wrote:

    Thanks 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”.

  5. Saurabh wrote:

    Exactly what I was looking for and nicely explained. You may want to update the screenshots for Office 2007 or 2010.

    Thanks !

  6. gill wrote:

    Thank you very much!

  1. Bar Chart with an Average Line for Each Group in Chart | da TaB is On wrote:

    [...] 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 [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel