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.

add vertical average line to chart1

#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.

add vertical average line to chart2

add vertical average line to chart3

#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)

add vertical average line to chart4

#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.

add vertical average line to chart5

#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.

add vertical average line to chart6

#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.

add vertical average line to chart7

#7 click Ok button. Then the new average series has been added into the existing bar chart.

add vertical average line to chart8

#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.

add vertical average line to chart9

#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.

add vertical average line to chart10

#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.

add vertical average line to chart11

#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.

add vertical average line to chart12

#12 click Ok button. And you would notice that the vertical line has been added into your horizontal bar chart.

add vertical average line to chart13

#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.

add vertical average line to chart14

#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:

add vertical average line to chart15

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
Average per Week 1

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
Average with Multiple Criteria 1

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 of Top N Values 1

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 the Last N Numeric Values 7

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
Average Last N Values in Multiple Columns 14

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
How to Average Ignore Zero Number 5

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
How to Average the Last N Values 10

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
How to Calculate Average If Criteria Not Blank 11

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
How to Calculate Average Ignore Non-Numeric Values and Errors 8

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