How To Add an Average Line to Column Chart in Excel 2010

When you create a chart to analyze the trend of the data, it is useful to add a horizontal Average Line across the chart, so that you can compare the data clearly and easily. In this tutorial, I'll show you how to add an Average Line to Column Chart in Excel 2010.

Add a New Average Value Column

In this example, we have a group of sales data (range A1:B7). You may copy A1:C7 to a new Worksheet to start this tutorial.

We want to display the average sales for the group in our chart, so we need to insert a new Helper Column that we called Average. We'll use Average function to calculate the average of entire group. In Column C2, type this formula:

=AVERAGE($B$2:$B$7)

then drag down to C7.

Create Chart

1. Select range A1:C7.
2. Under the Insert menu tab, in the Charts group, click the Column button and choose Clustered Column in 2-D Column.
   
3. Right-click any of the columns in the Average series on the chart, In the quick menu, click Change Series Chart Type…
   
4. In the Change Chart Type dialog, choose a basic Line chart.
   
5. click OK.

Formatting Series "Average"

1. Select the chart, this displays Chart Tools.
2. On the Layout tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click Series "Average".
   
3. Press CTRL+1.
4. On the Series Options tab, under Plot Series On, click Secondary Axis, then Click Close.
   

Formatting Secondary Horizontal Axis

1. On the Layout tab, in the Axes group, click Axes.
2. Click Secondary Horizontal Axis, and then click Show Left to Right Axis.
   
3. Select Secondary Horizontal (Category) Axis.
4. Press CTRL+1. The Format Axis dialog box is displayed.
5. On the Axis Options tab, In Major tick mark type, select None. In Axis labels, select None. under Position Axis, click On tick marks, then Click Close.
   
6. On the Line Color tab, under Line Color, click No line.
   
7. Click Close.

Formatting Vertical (Value) Axis

1. Select Vertical (Value) Axis.
2. Press CTRL+1. The Format Axis dialog box is displayed.
3. On the Axis Options tab, under Axis Options, in Minimum, click Fixed, input  value 0, in Maximum, click Fixed, input value 6000, in Major unit, click Fixed, and input value 1000.
   
4. Click Close.

Formatting Secondary Vertical (Value) Axis

1. Select Secondary Vertical (Value) Axis.
2. Press CTRL+1. The Format Axis dialog box is displayed.
3. On the Axis Options tab, under Axis Options, in Minimum, click Fixed, input  value 0, in Maximum, click Fixed, input value 6000, in Major unit, click Fixed, and input value 1000. In Major tick mark type, select None. In Axis labels, select None.
   
4. On the Line Color tab, under Line Color, click No line.
   
5. Click Close.

Formatting Average Point Label

1. Select Series "Average".
2. Click Series "Average" Point "Robin" (the last point).
3. Right-click, in the quick menu, select Add Data Label.
   
4. Press CTRL+1. The Format Data Point dialog box is displayed.
5. On the Marker Options tab, under Marker Type, select Built-in, then select a type, in this example we select Round.
   
6. Click Close.

At this point, all the major steps completed, your chart will looks like this:

You may add the Chart Title or delete Major Gridlines and Legend to make it more beautiful. If you use Excel 2013, all the steps will be clearly and easily, just do it.