3 Ways to Add an Average Line to Your Charts in Excel (Part II)

In the former article, we have discussed about adding horizontal line into the chart. Thus, in this article, the method to add vertical average line to horizontal chart will be displayed.

Sometimes, you will need to create a horizontal bar chart. So the average line should be vertical.

Add Vertical Average Line to Horizontal Chart

  1. Select the target area.
  2. Click the “Insert” in the tab.
  3. Then click the button “Column”.Click All Chart Types
  4. In the drop-down menu, click the “All-Chart Types”.
  5. And then in the new window, find the horizontal bar chart and then click.Choose Horizontal Bar
  6. Now click “OK”. Therefore, you have created a horizontal bar chart.Create Horizontal Bar Chart
  7. Input the number into cells. You may refer to the image below. The number 0 and 1 will be used in the later steps. And the numbers in the second column are the average sales volume.Input into Cells
  8. Right click the chart.
  9. Then in the new menu, choose the “Select Data”.Select Data
  10. In the “Select Data Source” window, click the button “Add”.Click Add
  11. In the “Edit Series” window, input a new series name into the “Series name” text box. And here we input “Average”.
  12. In the “Series values”, input =’sales volume’!$B$8:$B$9. You can also use your mouse to select the two cells with the average value that you have created in step7.Edit Series
  13. And then click “OK”.
  14. In the “Select Data Source” window, click “OK”.
  15. Now you can see the series of “Average” in the chart. Then right click any of the red bars.Change Series Chart Type of Average Series
  16. In the menu, choose “Change Series Chart Type”.
  17. In the “Change Chary Type” window, choose the “Scatter with Straight Lines” in the X Y (Scatter).Choose Scatter with Straight Lines
  18. Then click “OK”.
  19. Right click the chart and then choose “Select Data”.
  20. Next click the “Average”.
  21. And then click “Edit”.Click Average and Click Edit
  22. In the “Edit Series” window, fill the text boxes of X values and Y values.Fill the Values

In the horizontal chart, the second column is the X axis and the first column is the Y axis. So here you need to be careful and don’t mistake this two columns.

  1. Then click “OK”.
  2. And click “OK” in the “Select Data Source” window. And now you will find the average line in the chart.The Average in Chart
  3. Right click the second Y axis in the chart. If you find it a little difficult, you can click the numbers in the axis.
  4. Then in the new menu, choose the option “Format Axis”.Choose Format Axis
  5. In the “Format Axis” window, alter the minimum and the maximum options and the numbers in the “Axis Options”. The 0 and 1 are the numbers that we have set in step 7.Set Axis Option
  6. Then close the window. Thus, you can see that the vertical average line in the horizontal chart.

Besides, you can also imitate the second method in the Part I of our article and draw the vertical average line.

Protect Your Chart in Excel Files

Excel will sometimes corrupt due to many different known or unknown reasons. And as a result, the chart and other information will also be damaged. To protect your chart from a data disaster, you can use our potent Excel recovery software. It is capable of retrieving almost all the lost data in a damaged Excel file.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.