Add an Average Line to a Column Chart – Tutorial

Column chart with average line.

This tutorial shows you how to add an average line to a column chart in Excel. An average shows you what a typical value may be in a data set. In this lesson, we may also use the word “mean” to refer to the average.

We use Excel 2016 for the purpose of this tutorial. However, you can follow along in previous versions of Excel. Furthermore, we base this lesson on our column chart tutorial.

Steps to Add an Average Line to A Column Chart

If you do not have a column chart, we suggest you go through our column chart tutorial first. It will be easier to follow along.

Below is the chart we will use to add our average line to.

Basic column chart to add an average line to.
The column chart we want to add a mean line to.
Step 1: Calculate the mean for the data set.
Average formula for our data set.
Average formula for the data set.

In C2, type =AVERAGE($B$2:$B$13) and press the Enter key. The average of your data should appear.

The dollar ($) sign makes the formula absolute or static. The result will not change when you copy it to another location.

Average line data for the chart.
Average for the data set. Notice we include a title for our data set.
Step 2: Copy the mean through the rest of the data set.
Home ribbon.
Home ribbon.

Click in cell C2, press keyboard shortcut Ctrl-C. Select the remaining cells for the data set in column C, and press Ctrl-V. Or, you can do the same thing on the Home ribbon, under the Clipboard group.

Another Way
Selected cell showing node to copy down formula.
Selected cell showing node to copy down formula.

Click in cell C2. Then, click and drag the node on the bottom right of the cell. This will copy down the formula.

Data set with mean data.
Step 3: Show options to add data to the chart.
Steps to show data source dialog box to add data for average line.
Steps to show data source dialog box.
  1. Click on the chart. This will display chart tools options on the ribbon.
  2. Under Chart Tools, click Design.
  3. Under Design, click Select Data.
Select Data Source dialog box.
Select Data Source dialog box.

The Select Data Source dialog box appears.

Step 4: Add the average data to the chart.
Adding average line data to a chart through Select Data Source dialog box.
Adding data to a chart through Select Data Source dialog box.
Edit Series dialog box.
Edit Series dialog box.
  1. In the Select Data Source dialog box, click Add under Legend Entries (Series).
  2. Under Edit Series, click inside the Series name box and click cell C1. Or, type in the reference for your title.
  3. For Series values, select only the data (numbers) for the average. Note: If there is anything in this box before your selection, then delete it.
  4. Click OK.
Select Data Source dialog box with new entry for the average data set.
Select Data Source dialog box with new entry for the average data set.

You should now see another entry under Legend Entries for the average data set. Click OK.

Column chart with bar for average line.
Column chart with average bar.

A column bar is now added to your chart. Your column bar may overlap the other column a little, which is okay.

Step 5: Change the chart type of the average column series.
Steps to show the Change Chart Type dialog box.
Steps to show the Change Chart Type dialog box.
Steps to convert the average column to an average line.
Steps to convert the average column to an average line.
  1. Click on the average column in the chart.
  2. Under Chart Tools, click Design.
  3. Under Design, click Change Chart Type.
  4. Click inside the Chart Type list for the Average series.
  5. Select the Line icon.
  6. Click OK.

Success!

Column chart with average line.
Column chart with average line.

You have done it! A column chart with an average line.

Additional Information

Related Tutorials

External Links

Available chart types in Office


Tutorials

Techronology Home