Add an average line to a column chart.
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
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
- 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.
- 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
- Click on the chart.
- From the menu, select Chart Design.
- Under the Chart Design ribbon, click Select Data. Afterwards, the Select Data Source box appears.
- In the Select Data Source box, click Add. Afterwards, the Edit Series box opens.
- First, under Series name, select or enter the cell where you typed Average, your column header.
- Secondly, click inside the Series values box, and select the values under the average column.
- 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
- 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.
- Again, select the Chart Design menu.
- Now, select the Change Chart Type item, under Type. We are moving now.
Afterwards, the Change Chart Type box opens.
- Make sure you are under the All Charts tab.
- Click the Average series dropdown, and select a Line chart. It should be the first line chart option.
- Click OK.
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.