Excel: Add an Average Line to Your Graph

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Excel: Add an Average Line to Your Graph

Post by Admin »

In Excel when you make a graph or chart to analyze your data, an average line of the plotted data gives a better overview of the whole scenario. And it's very easy to plot such an average line to a graph.

In the following example, Column B contains sales information. I will calculate the average of the sales in Column C. For the calculation I will use absolute cells references (with the $ sign).


So in cell C2 enter

=AVERAGE($B$2:$B$8 )

Copy it down the column. The average sale is 8857 dollar.


Image


So your data is ready now.



Plot the graph:

Select your data range (A1:C8)

Go to Insert tab > from the Charts group > click on Column > from the 2-D Column select the first one (Clustered Column)


Image


Right click on any of the average bars > click Change Series Chart Type…


Image


The Change Chart Type window opens. From the Line group select the first one > click OK.


Image


Now instead of the average bars, you have the average line in your graph.


Now right click on this average line > click on Format Data Series...


Image


Format Data Series window opens. From the Series Options select Secondary Axis > click Close.


The graph is ready so far. Now do the final polishing – like adjusting the values for the vertical axes (click on any of the axes > right mouse click > click on Format Axis.... > adjust the maximum and major unit values etc. as per your preference), giving a title to your chart (you can do the title from Chart Layouts that you will get under Design tab > select any layout of your choice :-))


Image


Have fun :-)
Reatha Adaway
Posts: 16
Joined: Tue Oct 08, 2019 9:46 am

Re: Excel: Add an Average Line to Your Graph

Post by Reatha Adaway »

Adding an average line to your graph is a remarkably insightful decision that elevates the overall data presentation. It not only enhances the visual appeal but also aids in a more profound comprehension of trends and patterns. The site link you've incorporated at the base of the graph seamlessly directs viewers to further information, providing an invaluable resource for a comprehensive analysis. Your dedication to precision and clarity truly shines through this thoughtful addition. Great job!
Post Reply