# How To Add an Average Value Line to a Bar Chart

When 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.

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:

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

Andrew 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!

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

1. Natalie says:

This was very helpful. It taught me exactly what I needed to know.

2. Budbreaker says:

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.

1. Budbreaker says:

Neat trick you suggested. It should really do the trick. Thanks !

3. Dmitri says:

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.

4. Paul says:

Awesome, took me 1 minute to figure this out while having spent about an hour trying to do it myself

5. me says:

Thanks for this! Great tutorial!

6. Greg says:

7. Camille says:

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?

1. It is there but is too small on the vertical scale to see… You can select it indirectly by choosing it from the drop-down in the Current Selection box of the Layout tab under Chart Tools.

8. Thang says:

Hi,

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

9. Scott says:

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