Did you ever find it difficult to add an average line in Excel?
If so, you are not alone! Many users tend to seek how to add average lines in Excel while working on their projects. It could be an entirely new thing to learn adding a grand total line in a regular chart. With an average line, you can simply highlight the distribution of the user data in a given field.
Today, we will discuss how you can add an average line by using different hacks.
How to Add Average Line in Excel Graph
With an example, you can have a better understanding of how to add an average line to a column graph. Below are some easy-to-follow steps:
First, find the average with the help of the AVERAGE function. For this, we’ll add the below-given formula in C2 and copy it in the column:
=AVERAGE($B2:$B$7)
- Choose the source data such as the Average column (A1:C7).
- Open the Insert tab and click on Charts group.
- From this, choose Recommended Charts option.
- Turn on the All Charts tab and click on the Clustered Column – Line template.
- Click OK.
All done!
You will see a horizontal line is added to the graph. You can also see how the average value looks relative to the dataset:
Likewise, you can add an average line in a line graph as well. Following the above-mentioned steps will let you add an average line. The difference comes when you need to choose the Line or Line with Markers for the Actual data series:
Bonus Tips
By using the same trick, you can add a median as well. For this, you just need to use the MEDIAN function instead of AVERAGE.
A target line in the graph can be added smoothly. You just have to put the target values in the last column and choose the Clustered Column – Line combo chart.
You can even choose the Custom Combination type in case none of the predesigned chart combos are suitable for you.
How to Add a Line to an Existing Excel Graph?
When you already have a graph, adding a line to it will need you to follow more steps:
- Add a new column with your source data.
- If you need to add an average line, you have to fill the newly added column with an Average formula. On the other hand, when you are using a target line, enter the target values in the new column.
- Right-click the existing graph and click on the “Select Data” option given in the context menu.
- From this menu, choose the Add button in the Legend Entries.
When the Edit Series dialog window opens, follow the below-given steps:
- Enter the name you want in the Series name box, such as “Target line”.
- Press the Series Values box and click the target values without the column header.
- Hit the OK button twice and the dialog boxes will be closed.
- You will notice the target line series is added to the graph. In the context menu, choose Change Series Chart Type after right-clicking on the target line series.
- Open the Custom option from the Change Chart Type box and ensure that the Custom Combination template is selected. Normally, this selection occurs by default and you don’t need to manually perform it.
All done! You can see a horizontal line is added to the graph.
Wrap Up
So, this is how to add the average line in Excel for those who seek to find an easy way to perform this action. Normally, users of Excel are highly eager to learn new tricks every day that’s why practice is very important.
