How to Add Average Line to Excel Chart (with Easy Steps)

Excel users frequently appear to find it difficult to show or add an average/grand total line in a regular chart. An average line in a graph helps to visualize users’ distribution of data in a specific field. In this post, I’ll show you a challenging approach to the topic how to add average Line to Excel Chart.


Download Practice Workbook

You can download the practice workbook from the following download button.


Steps to Add Average Line to Excel Chart

Step 1: Prepare Data and Navigate to Recommended Charts from Insert Tab

  • Assume you have a class result card, as seen in the display image below.

Sample Dataset: How to Add Average Line to Excel Chart

  • Now we will select the data range and go to Insert>> Recommended Charts

Navigate to Recommended Charts from Insert Tab

Read More: How to Calculate Average True Range in Excel (with Easy Steps)


Step 2: Select a Suitable Chart Type from the Combo Section of Insert Chart Window

  • Insert chart window will appear on the screen. Then we will go to Combo>> All Charts>> Clustered Column-Line>>Average Marks>> Line>> OK.

Select a Suitable Chart Type from the Combo Section

  • Finally, the Average Line is now added to the Chart like in the image below.

the Average Line is now added to the Chart


Step 3: Apply Custom Combination If Needed

  • If none of the standard combination charts meet your requirements, select the Custom Combination type. In a line graph, Excel users can draw an average line in the same way. The procedures are the same; you simply select the Marks>> Clustered Column to Line graph like the picture below.

How to Add Average Line to Excel Chart

  • Lastly, this line graph with the average line will appear.

line graph with the average line

Read More: How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)


Similar Readings


How to Add Average Line in Excel Pivot Chart

So far we have learned how to add average lines to Excel charts. Now, what if you want to add an average line to an Excel pivot chart? This section will discuss this.

Steps:

  • First, we must create a Pivot Table and a chart based on the table, as depicted in the picture beneath. Go to Insert>> Table>> Pivot Table>> From Table/Range.

Creating a Pivot Table and a chart

  • This pop-up will appear in Excel. We will create the Pivot Table in the existing worksheet and click OK.

Creating the Pivot Table in the existing worksheet

  • First of all, in the PivotTable Fields check Name and Marks then Insert>> 2-D column.

How to Add Average Line in Excel Pivot Chart

  • The student’s graph of Marks will appear in the worksheet like the image below.

Graph of Marks will appear in the worksheet

  • Now, in the PivotTable Fields check Average Marks also and a new column in the chart will appear beside each of the Marks column.

In the PivotTable Fields check Average Marks

  • However, we want to see the Average Marks as a horizontal line in the column chart. For that reason, go to Right click>> Change Chart Type.

Right click on the mouse and click Change Chart Type

  • After that, Change Chart Type window will appear on the screen. Then we will go to Combo >> Custom Combination >>Sum of Average Marks >> Line>> OK.

Change Chart Type window will appear on the screen

  • Finally, we will get the Average Line to the charts of Marks.

Added Average Line to the charts of Marks

Read More: How to Calculate Sum & Average with Excel Formula


Conclusion

Follow these steps and stages to add an average line to an Excel chart. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section in the comments box of our blog ExcelDemy.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo