Quick Navigation
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:
This was very helpful. It taught me exactly what I needed to know.
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.
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.
Neat trick you suggested. It should really do the trick. Thanks !
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.
Hi Dmitri,
Horizontal bar charts need a slightly different approach, but I’ve walked through the steps in a tutorial here:
How to Add a Vertical Line to a Horizontal Bar Chart
Awesome, took me 1 minute to figure this out while having spent about an hour trying to do it myself
Thanks for this! Great tutorial!
Andrew your posts are always helpful.
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?
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.
Hi,
Is there any way to make the average line touch the y-axis on this chart without halving the first and last column?
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
This is not a Bar chart it is a Column chart