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
- Assume you have a class result card, as seen in the display image below.
- Now we will select the data range and go to Insert>> Recommended Charts
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.
- Finally, the Average Line is now added to the Chart like in the image below.
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.
- Lastly, this line graph with the average line will appear.
- How to Average Negative and Positive Numbers in Excel
- Find Average of Specific Cells in Excel (3 Handy Ways)
- How to Average Values Greater Than Zero in Excel (4 Ways)
- Ignore #N/A Error When Getting Average in Excel
- How to Calculate Average Numbers in Excel (9 Handy Methods)
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.
- 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.
- This pop-up will appear in Excel. We will create the Pivot Table in the existing worksheet and click OK.
- First of all, in the PivotTable Fields check Name and Marks then Insert>> 2-D column.
- The student’s graph of Marks will appear in the worksheet like the image below.
- Now, in the PivotTable Fields check Average Marks also and a new column in the chart will appear beside each of the Marks column.
- 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.
- 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.
- Finally, we will get the Average Line to the charts of Marks.
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.
- How to Average Filtered Data in Excel (2 Easy Methods)
- Calculate Average from Different Sheets in Excel
- How to Average Every Nth Row in Excel (3 Ways)
- Calculate Average of Averages in Excel (with Easy Steps)
- How to Calculate Average of Multiple Ranges in Excel (3 Methods)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- How to Calculate Average in Excel (Including All Criteria)