How To Add an Average Value Line to a Bar Chart

Creating the Chart

Now that we have our data updated with the average column, we can create the chart. Select the cells you want to be included.

Selection

Under the Insert menu tab, in the Charts section, click the Column button and choose a basic vertical column chart.

ChartType

At first glance, our bar graph isn’t off to a good start.

BadChart

The chart has too many columns of data to display clearly. Let’s narrow it down to just the Total and Average columns. With the chart selected, click one of the left corners of the blue selection box (blue represents the data points), and drag it to the right until just the last two columns are selected.

ChangeChartData

Now the chart is looking a bit better, but we still need to change those averages to a line.

BetterChart

Right-click one of the red bars in the Average data series on the chart. In the menu that appears, click Change Series Chart Type…

ChangeSeriesType

Then choose a basic Line chart from the Change Chart Type dialog and click OK.

LineType

We’re basically where we want to be! I made a few extra cosmetic adjustments…

I deleted the Total line from the legend by clicking until just “Total” was selecting and pressing Delete on the keyboard.

AverageLegend

I moved the bars closer together in the chart by double-clicking one of the blue Total bars and setting the Gap Width to 0% in the Format Data Series dialog.

NoGapFormat

I set the y-axis to focus on the difference between the bars rather than showing from zero to the maximum value. This is possible by changing the Axis Options once you double-click the y-axis.

AxisOptions

Finally, I made the Average line a dashed line by double-clicking on the solid red line and choosing Line Style from the Format Data Series dialog.

LineStyle

That’s it! You can use this technique to add any guide line to a bar-type chart, whether you want an average, a maximum and minimum bound, or last period’s figure.

FinalChart

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

14 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

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