Add an average line to a column chart – How to

Add an average line to a column chart – How to

How to add an average line to a column chart.

Add an average line to a column chart.

This lesson shows you how to add an average line to a column chart in Excel. For the purpose of this lesson, we use a previous column chart that we already created.

In addition, all functions in this lessons were done on a Windows PC, in Excel 2007 or higher.

Steps to add an average line to a column chart

The steps

Here are the steps to add an average line to a column chart. If you do not have a column chart, then click here to create one.

Step 1: Create an average column
Add an average line to a column chart - Add an average column to the worksheet.
  1. Next your values column, enter Average as the header. Below the header, in a new cell, enter the following formula: =AVERAGE($B$2:$B$5). So, notice how we have dollar signs around the cell references. Thus, this notation represents an absolute range. Press Enter.

    A number representing the average should appear in the cell.

Shortcut alert: Press Alt + F4 to cycle through relative and absolute ranges.

Average data.
  1. Click on the cell with the average and copy it (Ctrl + C). Select the remaining cells that have a value next to them, and paste (Ctrl + V) the formula.
Step 2: Add the Average column to the chart
Column chart
  1. Click on the chart.
  2. From the menu, select Chart Design.
  3. Under the Chart Design ribbon, click Select Data. Afterwards, the Select Data Source box appears.
Add an average line to a column chart - Select data source.
  1. In the Select Data Source box, click Add. Afterwards, the Edit Series box opens.
Edit series.
  1. First, under Series name, select or enter the cell where you typed Average, your column header.
  2. Secondly, click inside the Series values box, and select the values under the average column.
  3. Finally, click OK. Then, click OK again.

Now, you should have another column, right next to the first column. But, we want a line, not a column. So, we need to change the Average column to a line.

Step 3: Change the chart type of the average column
Change chart type.
  1. Click on the Average series. It should be a difference color than the other column. Also, when you click on the column bar, the data should highlight in the sheet.
  2. Again, select the Chart Design menu.
  3. Now, select the Change Chart Type item, under Type. We are moving now.

Afterwards, the Change Chart Type box opens.

Add an average line to a column chart - Change chart type.
  1. Make sure you are under the All Charts tab.
  2. Click the Average series dropdown, and select a Line chart. It should be the first line chart option.
  3. Click OK.

Success!

Add an average line to a column chart - Success.

If you followed all the steps, then you should have a column chart with an average line in your sheet.

So, now you can see what values are above or below the average. Good stuff.

Thanks for viewing this lesson. We hope you enjoyed it.


Related


Excel lessons  Education home  Techronology home