Note: The other languages of the website are Google-translated. Back to English

How to add horizontal benchmark/target/base line in an Excel chart?

Let's say you have created a column chart to show four teams' sales amount in Excel. But now, you want to add a horizontal benchmark line in the chart, how could you handle it? This article will introduce three solutions for you!


Add horizontal benchmark/base/target line by adding a new data series in an Excel chart

This method will take the benchmark line for example to guide you to add a benchmark line, baseline, or target line in an existing chart in Excel.

1. Beside the source data, add a Benchmark line column, and fill with your benchmark values. See screenshot:

2. Right-click the existing chart, and click Select Data from the context menu. See screenshot:

3. In the Select Data Source dialog box, please click the Add button in the Legend Entries (Series) section. See screenshot:

4. In the popping Edit Series dialog box, please (1) type Benchmark line in the Series name box, (2) specify the Benchmark line column excluding the column header as Series value, and (3) click the OK buttons successively to close both dialog boxes. See screenshot:

Kutools for Excel

Stand out from the Crowd

300 Handy Tools
Solve 80% Problems in Excel
Free Trial Now

Say goodbye to terrible VBA and formulas!

5. Now the benchmark line series is added to the chart. Right-click the benchmark line series, and select Change Series Chart Type from the context menu. See screenshot:

6. In the Change Chart Type dialog box, please specify the chart type of the new data series as Scatter with Straight Line, uncheck the Secondary Axis option, and click the OK button. See screenshot:

Now you will see the benchmark line is added to the column chart already. Go ahead to decorate the chart.

7. Right click the horizontal X axis, and select Format Axis from the context menu.

8. In the Format Axis pane, please check the On tick marks in the Axis position section on the Axis Options tab.

Now the horizontal benchmark line is added as following screenshot shown:

Add horizontal benchmark/base/target line by adding a new data series in an Excel chart


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Only several clicks to add a horizontal average/benchmark/target/base line to an Excel chart

Comparing to generally adding data series and changing chart type to add a normal horizontal line in a chart in Excel, Kutools for Excel's Add Line to Chart feature provides a fantastically simple way to add an average line, a target, benchmark, or base line to a chart quickly with only several click.



Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Add horizontal benchmark/target/base line by Paste Special in Excel chart

This method will guide you to copy the benchmark/target/baseline data to the destination chart as a new data series, and then change the chart type of the new series to Scatter with Straight Line in Excel.

1. Enter your benchmark data in the worksheet as below screenshot shown.

Note: In the screenshot, 12 indicates there are 12 records in our source data; 85 means the benchmark sales, and you can change them as your need.

2. Select the benchmark data (the Range D2:E4 in my example), and press Ctrl + C keys simultaneously to copy it.

Kutools for Excel

Stand out from the Crowd

300 Handy Tools
Solve 80% Problems in Excel
Free Trial Now

Say goodbye to terrible VBA and formulas!

3. Click to activate the original chart, and then click Home > Paste > Paste Special. See screenshot:

4. In the opening Paste Special dialog, please check the New Series option, the Columns option, the Series Names in First Row checkbox, the Categories (X Labels) in First Column checkbox, and then click the OK button. See screenshot:

Now the benchmark data has been added as a new data series in the activated chart.

5. In the chart, right click the new Benchmark series, and select Change Series Chart Type from the context menu. See screenshot:

6. Now the Change Chart Type dialog comes out. Please specify the Chart Type of Benchmark series as Scatter with Straight Lines, uncheck the Secondary Axis option behind it, and then click the OK button. See screenshot:

So far, the benchmark line has been added in the chart. However, the benchmark line neither begins at the outside border of the first column, nor reaches the outside border of the last column.

7. Right click the horizontal X axis in the chart, and select Format Axis from the context menu. See screenshot:

8. In the Format Axis pane, under the Axis Options tab, please check the On tick marks option in the Axis Position section. See screenshot:

Now you will see the benchmark line comes across all column data in the chart. See screenshot:

Notes:

(1) This method can also add horizontal benchmark line in an area chart.

(2) This method can also add horizontal benchmark line in a line chart.

Add horizontal benchmark/target/base line by Paste Special in Excel chart


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Add horizontal benchmark/target/base line in an Excel chart with an amazing tool

If you have Kutools for Excel installed, you can apply its Add Line to Chart feature to quickly add average line, benchmark line, target line, based line, etc. to the currently selected chart in Excel.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the chart you will add benchmark line for.

2. Click Kutools > Charts > Add Line to Chart to enable this feature.

3. In the Add line to chart dialog, please check the Other values option, refer the cell containing the specified value or enter the specified value directly, and click the Ok button. See screenshot:

Now the specified benchmark line is added in the selected chart.

Note: You can easily format the benchmark line as you need:
(1) Double click the benchmark line to enable the Format Error Bars pane.
(2) Enable the Fill & Line tab, and you can change the parameters to format the benchmark line freely, says line color, dash type, line width, etc.

300 tools help you stand out from the crowd now

with Kutools for Excel - never worry about job cut

Kutools for Excel brings 300 handy tools for 1500 work scenarios, helps you work faster and better than your colleagues, and easily win trust of your boss. You will be the last one in the list of layoffs, and easily maintain a stable and better life for your family!

  • To be a master of Excel in 3 minutes, and easily win appreciation of others.
  • Improve 80% work efficiency, solve your 80% problems in Excel, not work overtime.
  • Speed up your work, save 2 hours every day to improve yourself and accompany family.
  • Say goodbye to complicated formulas and VBA code, free up your memory.
  • Reduce thousands of keyboard operations and mouse clicks, far away from mouse hand.
  • Spend $39.0, worth more than $4000.0 training of others.
  • Choice of
    110,000+
    highly effective people and 300+ famous companies, work stable in Excel.
  • Full feature free trial 30-day, no credit card required.
Read More ...
Free Trial Now
 
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
All was good until Step 8. My vertical bars are truncated on both ends of the data field (as are yours). Thoughts on how to fix this appearance?
This comment was minimized by the moderator on the site
It's best to use  Line only for the Target data then your bars should be same!
There are no comments posted here yet

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL