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