Create chart with average / target line in Excel
In Excel, it may be a common task to insert a horizontal or vertical line to a chart, the horizontal or vertical line may reference some target or average value. This type of line makes it easy to compare the actual values versus the target or average value, and with the help of this line, you can easily see where values are above and below this reference value as below screenshots shown. This article, I will talk about how to create the column or bar chart with average or target line in Excel.
| Column chart with horizontal line | Bar chart with vertical line |
 |
 |
- Create column chart with average or target line horizontally
- Add a target line with different values in a column chart
- Create column chart with average line dynamically by using radio buttons
- Create bar chart with average or target line vertically
- Add a target line with different values in a bar chart
- Add a target / average line to a column or bar chart with a handy feature
- Download Chart With Average / Target Line sample file
Create column chart with average or target line horizontally
To create a column chart with average or target line horizontally, please do with the following steps:
1. If you want to create a column chart with an average line, first, you should calculate the average value of the list of data, please enter the below formula into a blank cell, and then drag auto fill handle to copy the formula to other the cells, see screenshot:
2. Then, select the data range including the average values, and then click Insert > Insert Column or Bar Chart > Clustered Column, see screenshot:
3. And a column chart is inserted in the worksheet, you can delete the unneeded elements, such as chart title, gridlines as you need, see screenshots:
 |
 |
 |
4. Then, please right click the average data series (orange bars), and choose Change Series Chart Type from the context menu, see screenshot:
5. In the Change Chart Type dialog box, under the Choose the chart type and axis for your data series list box, select Line chart from the Chart Type drop down list for the Average data series, see screenshot:
6. And then, click OK button, you will get the chart as below screenshot shown:
7. Next, you can add the data label for this average line, double click to select the right most data point of the line, see screenshot:
8. And then, click Chart Elements, check Data Labels box, and the data label has been inserted for the average line, see screenshot:
Add a target line with different values in a column chart
Sometimes, you may want to compare the actual values with the target values that are different for each row, in this case, to visualize the target values more clearly, the below type of chart may do you a favor.
 |
|
 |
To create this type of chart, please do as this:
1. First, select the data range, and then click Insert > Insert Column or Bar Chart > Clustered Column to insert the chart.
2. Then, right click the target data series (orange bars), and choose Change Series Chart Type from the context menu, in the Change Chart Type dialog box, choose Line chart for the target data series from the Chart Type drop down list, see screenshot:
3. Then, click OK button, and then double click the target line, and choose Format Data Series, see screenshot:
4. In the opened Format Data Series pane, under the Fill & Line tab, select No line from the Line section, see screenshot:
5. Still in the Fill & Line tab, switch to the Marker section, select Built-in from the Marker Options section, and then select the horizontal bar in the Type drop down, at last, set a size corresponding to the width of your bars in the Size box, see screenshot:
6. And then, set a color for the target lines as you like from the Fill section, see screenshot:
7. At last, you can delete the unwanted elements, such as chart title, gridlines as you need.
Create column chart with average line dynamically by using radio buttons
If you have a large table with the sales of the product in multiple years, now, you want to show the average line for each year in a chart. For dealing with this task, you can use a chart with average line by clicking the radio button as below demo shown. When selecting the corresponding year, the chart with average line will be displayed dynamically.
To create this dynamic chart, first, you should insert several radio buttons, and then create a helper data table which the chart based on, at last insert a chart link to the radio button. Please do with the following steps:
First, insert several radio buttons
1. Click Developer > Insert > Option Button (Form Control), and then draw four radio buttons and edit the text to your need, see screenshots:
 |
|
 |
2. Then, right click the first radio button, and choose Format Control from the context menu, see screenshot:
3. In the opened Format Object dialog box, under the Control tab, select a cell to link the radio button from the Cell link text box, and click OK to close it, see screenshot:
Second, insert a helper data table for creating the chart based on
4. Copy the product names from the original table to a new range, and then apply the below formula into a blank cell beside your new copied data, and then drag the fill handle to get the result, see screenshot:
5. Now, when you click one radio button, the corresponding year of the data will be displayed automatically as below demo shown:
6. And then, you can calculate the average values by using the below formula, see screenshot:
Third, Create the chart based on the new helper data table
Then, select the new created data, and then please apply the above step 2- step 8 of the method 1 for creating a column chart with an average line, and you will get a chart with an average line dynamically. See below demo:
Create bar chart with average or target line vertically
This section will introduce how to create a bar chart with average or target vertically, please do as this:
1. First, create a helper data table, enter X and Y into two cells, and then please calculate the average values into cell D2 and D3, see screenshot:
2. Then, enter the number 0 and 1 into cell E2 and E3 for the Y values, see screenshot:
3. Then, select the original data range, and then click Insert > Insert Column or Bar Chart > Clustered Bar, see screenshot:
4. And a bar chart has been inserted into the sheet, then, right click the bar, and choose Select Data from the context menu, see screenshot:
5. In the popped out Select Data Source dialog, click the Add button, see screenshot:
6. And then, in the Edit Series dialog, enter a series name into the Series name text box, and then select the cells with the X values (D2:D3 in this example) in the Series values text box, see screenshot:
7. Click OK > OK to close the dialogs, and the new data series is now added to your bar chart (two orange bars), and then right click the orange bar and choose Change Series Chart Type, see screenshot:
8. In the popped out Change Chart Type dialog box, select Scatter with Smooth Lines from the Chart Type drop down for the Average data series, see screenshot:
9. Then, click OK to close the dialog box, and the new data series is converted to a data point along the primary Y axis, then, right click the bar, and then choose Select Data from the context menu, see screenshot:
10. In the Select Data Source dialog box, in the Legend Entries (Series) list box, click the Average option, and then click Edit button, see screenshot:
11. Then, an Edit Series prompt box is popped out, in the Series X values box, select the two X cells (D2:D3), and in the Series Y values box, select two Y cells (E2:E3), see screenshot:
12. Click OK > OK to close the dialog boxes, and you can see a vertical average line has been added to the chart, see screenshot:
13. Then, right click the secondary vertical axis (the right axis), and choose Format Axis from the context menu, see screenshot:
14. In the opened Format Axis pane, under the Axis Options tab, in the Axis Options section, type 1.0 in the Maximum bound box, and you will get the chart as below screenshot shown:
15. At last, you can delete the secondary vertical axis and the unwanted gridlines as you need, now, a bar chart with a vertical average line is finished. See screenshot:
Add a target line with different values in a bar chart
To add the target line with different values in a bar chart as below screenshots shown, please do with the following step by step:
 |
|
 |
1. Select the data range, and then click Insert > Insert Column or Bar Chart > Clustered Bar to insert a bar chart, see screenshot:
2. Then, right click the target data series (orange bars), and choose Format Data Series from the context menu, see screenshot:
3. In the opened Format Data Series pane, under the Series Options tab, change the series overlap to 100%, see screenshot:
4. Still in the Format Data Series pane, switch to the Fill & Line tab, select No fill from the Fill section, see screenshot:
5. Next, go to the Effects tab, under the Shadow section, please set the following operations:
- Specify a color that you need from the Color drop down;
- Set the blur to 0 pt;
- Then, adjust the angle to 0°;
- At last, change the distance to 2pt as you need.
6. Now, switch to the Series Options tab again, select Secondary Axis from the Series Options section, and change the gap width to 90% as you need. See screenshot:
7. Finally, delete the unwanted elements, such as chart tile, top X axis, gridlines as you need, you will get the bar chart with different target lines as below screenshot shown:
Add a target / average line to a column or bar chart with a handy feature
Kutools for Excel provides 50+ special types of charts that Excel does not have, such as Bullet Chart, Target and Actual Chart, Difference Arrow Chart and so on. With its handy chart tool- Add line to chart, you can add a target or average line to a column or bar chart with only a click in Excel. Click to download Kutools for Excel for free trial!
Download Chart With Average / Target Line sample file
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
