Skip to main content

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.

1
2
3
4
5
6
7
A B C
Name Total Sales Average
Ann 2508 =AVERAGE($B$2:$B$7)
Bell 3542 =AVERAGE($B$2:$B$7)
Fox 4530 =AVERAGE($B$2:$B$7)
Mark 5508 =AVERAGE($B$2:$B$7)
Owen 4508 =AVERAGE($B$2:$B$7)
Robin 3500 =AVERAGE($B$2:$B$7)

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.
    Add Column Chart
  3. Right-click any of the columns in the Average series on the chart, In the quick menu, click Change Series Chart Type…
    Change Series Chart Type
  4. In the Change Chart Type dialog, choose a basic Line chart.
    Change Chart Type
  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".
    Chart Elements
  3. Press CTRL+1.
  4. On the Series Options tab, under Plot Series On, click Secondary Axis, then Click Close.
    Format Data Series

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.
    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.
    Format Axis On tick marks
  6. On the Line Color tab, under Line Color, click No line.
    No Line Color
  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.
    Formatting Vertical Axis
  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.
    Formatting Secondary Vertical Axis
  4. On the Line Color tab, under Line Color, click No line.
    No Line Color
  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.
    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.
    Marker Type Built-in
  6. Click Close.

At this point, all the major steps completed, your chart will looks like this:
Average Line in Column Chart
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.

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>