How To Add Lines To Existing Excel Chart

In the previous article, we learned how to add an average line in an Excel clustered column chart using the combo chart from scratch. In this article, I will tell you how to add lines to an existing Excel chart if you do not want to do it using the Excel combo chart from scratch.

1. How To Add Lines To Existing Excel Chart.

1.1 Preparing The Example Data Cells.

1. Below is this example’s source cell data. Column A is the monthly list, column B is the revenue of each month, and column C is the average revenue.
   
2. Column C‘s value is calculated by the formula =AVERAGE(B2, $B$13), the first parameter is a relative reference to the first cell and the second parameter is an absolute reference to the last cell in column B.
3. Input the formula =AVERAGE(B2, $B$13) in cell C2 and press enter key to get the first number 90 in cell C2.
4. Select cell C2 and mouse over the small square in the cell’s bottom right corner.
5. When the mouse pointer is changed to a cross icon, click and drag the mouse pointer from cell C2 to cell C13.
6. Then you will get the above numbers in column C.
7. When you click one cell in column C such as C10, you will find the formula in cell C10 is changed to =AVERAGE(B10, $B$13).
8. You will find the average number is not calculated with all column B‘s cell numbers, it just calculates the numbers with the current row’s cell in column B to the last cell in column B.

1.2 Draw The Clustered Column Chart.

1. Now, we will first draw the clustered column chart based on the data of column A and column B.
2. Select the cell range A1:B13, then click the Insert tab on the Excel top menu bar.
3. In the Charts group, click the Insert Column or Bar Chart down arrow and click the Clustered Column icon in the 2-D Column section.
4. Then it will create a clustered column chart on your worksheet.
5. The chart only contains the revenue column for each month, now we will add the line to the clustered column chart using the data in column C.

1.3 Add Lines To Existing Excel Chart.

1. It is time to add the line based on the data in column C to the above existing column chart.
2. Right-click the above column chart, then click the Select Data… menu item in the popup menu list to open the Select Data Source dialog window.
3. Click the Add button in the Legend Entries(Series) section on the dialog window left side.
4. It will open the Edit Series dialog window.
5. Input the series name such as Average in the Series name text box.
6. Then click the Series values text box and remove the existing text ={1} in the text box.
7. Now select the cell range C2:C13 from the Excel worksheet, then it will insert the formula such as =’Sheet_name’!$C$2:$C$13 in the Series values text box.
8. Click the OK button to save and close the dialog window.
9. Click the OK button in the Select Data Source dialog to save and exit the dialog window.
10. Now it will add the second series the Average series in the column chart, the Average column is in orange color.
11. Right-click the newly added series column ( orange column ) in the chart, then click the Change Series Chart Type… menu item in the popup menu list.
12. It will open the Change Chart Type dialog window.
13. It will select the Combo item in the bottom left side of the All Charts tab by default.
14. On the window’s right side, it will select the Custom Combination icon on the top area by default also.
15. You can see the series list on the window bottom area, the area title is Choose the chart type and axis for your data series.
16. In the newly added series row ( Series Name is Average ), select the item Line from the Chart Type drop-down list.
    
17. Now when you click the OK button to close the window, it will change the second series column chart to a line chart.