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 a couple of solutions for you!
Method A: by adding new data series
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
Solve 80% Problems in Excel
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:
Note: This method works well in Excel 2013 / 2016 / 365.
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 70% 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 60-day, no credit card required.