# How to Add Vertical Average Line to Bar Chart in Excel

This post will guide you how to add vertical average line to a bar chart in Excel. How do I add an average line to your bar chart in Excel 2013/2016.

## Add Vertical Average Line to Bar Chart

Assuming that you have created a horizontal bar chart based on the original data in Range A1:B5 in current worksheet, and you want to add a vertical average line across the horizontal bar chart at a specific value. How to do it. And you can do the following steps to add a vertical line to the horizontal bar chart type in Excel.

#1 select the original data that you want to build a horizontal bar chart. #2 go to INSERT tab, click Bar command under charts group. And select Clustered Bar chart type.  A horizontal bar chart is created in your worksheet.  #3 we need to build a new series into the existing bar chart, so need to add two numbers 0 and 1 into two blank cells A6 and A7, then to the right of cells A6:A7, apply the following formula to calculate the average value for all of the sales data. Like this:

=AVERAGE(\$B\$2:\$B\$5) #4 select the chart, and go to DESIGN tab, click Select Data command under Chart Tools group.  And the Select Data Source dialog box will open. #5 click the Add button under legend Entries section to add the Average Value series in the Select Data Source dialog box. And the Edit Series dialog will open. #6 type “Average” as the name of the series. And specify the two cells (B6:B7) that contain the average values calculation. Click OK button. #7 click Ok button. Then the new average series has been added into the existing bar chart. #8 right click on the average series in the bar chart, and select Change Series Chart Type from the popup menu list to turning the selected series into a vertical line.  And the Change Chart Type dialog box will appear. #9 select Combo option, select Scatter with Straight Lines chart type in the Chart Type list for the Average Series name in the Change Chart Type dialog, and select. Click Ok button to exit the dialog. #10 right click on the bar chart again, choose Select Data from the popup menu list. And the Select Data Source dialog will appear. And then choose the Average series and click Edit button. The Edit Series dialog will appear. #11 you need to specify Series X and Series Y values for the vertical average line. And the Series X values should be the place on the x-axis for your vertical line, so you should choose the cells that contain average calculation.  And the Series Y values should be the place on the y-axis for your vertical line, so you can choose the cells that contain 0 and 1 numbers.  Click Ok button. #12 click Ok button. And you would notice that the vertical line has been added into your horizontal bar chart. #13 you need to adjust y-axis of the average series, double-click the second y-axis on the right side of the chart. And the Format Axis pane should be shown. #14 type number 1 into the text box in the Format Axis dialog.

#15 right click on the vertical line, and select Format Data Series from the popup menu list. You can change the colors and design of the chart as you need. Like this: ### Related Functions

• Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

Related Posts

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

Average per Week by Formula in Excel

We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation ...

Average with Multiple Criteria in Excel

AVERAGEIFS function is one of the most popular functions in Excel. Apply AVERAGEIFS function, we can calculate average simply if there are multiple conditions. In this article, we will introduce you to apply AVERAGEIFS function to calculate average of a ...

Average of Top N Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average properly based on different situations. In this article, we will introduce you the way to calculate average of ...

Average the Last N Numeric Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How to Average Last N Values in Multiple Columns

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. In daily work, we may apply AVERAGE function together with some other functions to get average based on some conditions. In ...

How to Average Ignore Zero Number

AVERAGE function is a frequently used function in our office work. Except this basic function, Excel also provides some other Average related functions like AVERAGEIF, AVERAGEIFS. In this article, we will show you applying AVERAGEIF function to get average with ...

How to Average the Last N Values in Excel

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. We can apply it to calculate the average of numbers from a given range reference. In daily work, we may apply ...

How to Calculate Average If Criteria Not Blank/Ignore Blank Cell

In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of ...

How to Calculate Average Ignore Non-Numeric Values and Errors

In daily work we often need to calculate the average of some numbers in a range for further analysis. Thus, we need to know some basic functions of calculate average in Excel. From now on, we will introduce you some ...

Sidebar