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
- Select the target area.
- Click the “Insert” in the tab.
- Then click the button “Column”.
- In the drop-down menu, click the “All-Chart Types”.
- And then in the new window, find the horizontal bar chart and then click.
- Now click “OK”. Therefore, you have created a horizontal bar chart.
- 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.
- Right click the chart.
- Then in the new menu, choose the “Select Data”.
- In the “Select Data Source” window, click the button “Add”.
- In the “Edit Series” window, input a new series name into the “Series name” text box. And here we input “Average”.
- 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.
- And then click “OK”.
- In the “Select Data Source” window, click “OK”.
- Now you can see the series of “Average” in the chart. Then right click any of the red bars.
- In the menu, choose “Change Series Chart Type”.
- In the “Change Chary Type” window, choose the “Scatter with Straight Lines” in the X Y (Scatter).
- Then click “OK”.
- Right click the chart and then choose “Select Data”.
- Next click the “Average”.
- And then click “Edit”.
- In the “Edit Series” window, fill the text boxes of X values and Y 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.
- Then click “OK”.
- And click “OK” in the “Select Data Source” window. And now you will find the average line in the chart.
- Right click the second Y axis in the chart. If you find it a little difficult, you can click the numbers in the axis.
- Then in the new menu, choose the option “Format Axis”.
- 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.
- 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.
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