How To Add an Average Value Line to a Bar Chart

AverageLineLeadWhen you are comparing values in a bar chart, it is useful to have some idea of what the average value looks like relative to the data set. By default, however, Excel’s graphs show all data using the same type of bar or line. In this quick tutorial, we’ll walk through how to add an Average Value line to a vertical bar chart by adding an aggregate statistic (Average) to a data set and changing a series chart type.

Updating the Data Set

In our example, we are starting with a basic set of sales figures broken out by employee. We have quarterly figures and an annual total.

DataSet

We want to display the average annual sales for the entire group in our chart, so we need to calculate a new column that we’ll call Average. Since this is going to be a line, we need every cell in the column to have the same average calculation, so we’ll lock the cell references before we drag down. The AVERAGE formula will be as follows:

=AVERAGE($G$2:$G$16)

The column will look like this:

AverageColumn

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

13 thoughts on “How To Add an Average Value Line to a Bar Chart

  1. Is there any way of hiding that average column from the table, because having column entirely of one value is a bit … lame. And when you try to hide the column by the mean of right clicking and chosing “Hide”, data from graph (line) also disappear.

    1. Hi Budbreaker,

      Excel is a bit weird about hiding columns in charts. You’re right that if you chose to “hide” the Average column in your data, it will make the series disappear on the chart. If you are just looking to visually hide the column but keep the data in the chart, I recommend changing the column width to a small value like “0.1” to shrink it to near invisible. This should have the same effect as “Hide” but keep the data in the chart.

  2. Hi. Thanks for the post, but what’s the trick when you have horizontal bars. In other words, I need to rotate my ‘ave’ line and having trouble doing so. Thanks.

  3. I have followed your steps exactly. I want a horizontal line for a company wide average on a vertical bar chart. Whenever I chose “change series chart style to a line, the line disappears entirely. I don’t see any horizontal line. What am I doing wrong?

  4. Hi,

    Is there any way to make the average line touch the y-axis on this chart without halving the first and last column?

  5. Hi there

    Is there a way of the chart automatically changing colour if below the average line? for example if the ave is 150 but you hit 145 it goes red, if 160 it goes green.

    Cheers

Leave a Reply to Paul Cancel reply

Your email address will not be published. Required fields are marked *