If you want to add an average line to an excel chart, you can use the excel combo chart. The excel combo chart can draw multiple figures in one chart. And you can select different chart type for different chart series. This article will tell you how to draw average line in an excel chart using the excel combo chart.
1. How To Draw Average Line In Excel Chart Using Excel Combo Chart.
1.1 Example Data Cells.
- Below is this example source data cells, the first column is the month list, the second column is the revenue of each month, and the third column is the average revenue of all the monthly revenue.
A B C 1 Month Revenue Average 2 Jan 100 85.5 3 Feb 98 85.5 4 Mar 82 85.5 5 Apr 90 85.5 6 May 96 85.5 7 Jun 78 85.5 8 Jul 76 85.5 9 Aug 59 85.5 10 Sep 79 85.5 11 Oct 90 85.5 12 Nov 98 85.5 13 Dec 80 85.5
- We just need to input the data in column A and B, and the data in column C is calculated out by a formula automatically.
- Input the formula =AVERAGE($B$2:$B$13) in cell C2 and press the enter key, then it will display the average number of all the 12 month’s revenue in cell C2.
- The formula uses the excel AVERAGE function to calculate the average number of the provided cell range.
- Please note, the cell range must use the absolute reference, otherwise it will calculate wrong number in the following cells.
- Select the cell C2, and it will display a small square at the bottom right corner of the cell.
- Move you mouse key over the square and the mouse pointer will be changed to a cross icon.
- Press the mouse key, and drag the pointer down to cell C13.
- Then it will copy the original formula =AVERAGE($B$2:$B$13) to every cell in the cell range.
- Then you can see all cells in the range contains the same average number.
- Click one cell in the cell range such as C3, you can see the cell contains the formula =AVERAGE($B$2:$B$13) also.
- If you do not use the absolute reference in the formula, then it will change the formula value in the cell C3 to =AVERAGE(B3:B13), and the result number will be different.
1.2 How To Use Combo Chart To Create A Chart With Average Line In The Chart.
- Select the cell A1, press the Shift key and click the cell C13 at the same time to select the cell range A1:C13.
- Click the Insert tab —> Charts group —> Recommended Charts item to open the Insert Chart dialog window.
- Click the All Charts tab in the above pop up dialog window.
- Then click the Combo item in the window bottom left side.
- On the window right side, it will select the default combo chart type Clustered Column – Line.
- It will list all the chart series at the bottom of the right side, each serie one row.
- The list column name is Series Name, Chart Type, and Secondary Axis.
- In this example, there are 2 series rows in the list.
- The first serie row’s Series Name is Revenue, and the default selected Chart Type item is Clustered Column.
- The second serie row’s Series Name is Average, and the default selected Chart Type item is Line.
- When you click the OK button, it will display the below clustered column chart with the average line in the chart.
1.3 How To Change The Series Chart Type In The Combo Chart.
- You can change the series chart type in the combo chart after you create the above clustered column – line combo chart.
- Click the above chart to select it, then click the Chart Design tab on the excel top menu bar.
- Click the Change Chart Type item in the Type group.
- It will open the Change Chart Type dialog window.
- It will show the All Charts tab and select the Combo item in the window bottom left side automatically.
- On the window top area of the right side, it will select the Custom Combination icon automatically.
- Then you can change the chart type for each series in the combo chart series list on the window bottom area.
- In this example, we change the serie Revenue‘s chart type from the item Clustered Column to Line.
- After click the OK button, you can get the below line chart, the blue line is the Revenue serie and the orange line is the Average revenue serie.
- The above method can be used to draw any line in excel combo chart besides average line.