How to Add a Horizontal Line in a Chart in Excel?
Excel bar graphs or charts are a great way to graphically represent mathematical data. On top of that, sometimes, the values included in the charts are required to be compared with a target or a base value. Have you ever wondered if there is a way to graphically represent this target value? Well, in this article we will discuss how we can use a horizontal target/benchmark or baseline in an excel chart but first, let us look at the problem statement.
Need for a horizontal benchmark in an Excel chart
Let us first create a table with items and sales as attributes. This is shown below:
Now follow the below steps to convert this table into a bar graph.
Step 1: Select the cells from A1 to B5. Then click on the Insert tab at the top of the ribbon and then select the column in the Illustration group.
Insert tab – column option
Step 2: From the column drop-down, just click on any chart option you want, and that chart will be automatically displayed. Here, we have taken the stacked column chart option.
Sales bar graph
Now, let us say that we had the target to reach 30,000 sales for each of the items. How can we compare this visually with the current sales in this graph? Well, to do that, we can make a line at 30, 000, horizontal to the x-axis. Here is how it will look:
horizontal benchmark
But how to add this horizontal benchmark to the graph?
Read further to know the different ways of doing the same.
Method 1 – Use the paste special method to add a horizontal benchmark in the Excel chart
Note that we will use the same table and the corresponding chart as above to demonstrate this example.
Step 1: First (after creating the sales table and its bar chart), create a benchmark table on the same spreadsheet as shown below:
Benchmark table
The values 1 and 4 refer to the number of records in the sales table and 30000 represents the benchmark value.
Step 2: Now, select the benchmark table and press Ctrl + C alongside. After this, click on the sales chart to activate it and go to the paste special option under the paste dropdown on the home tab. A dialog box will appear like this:
Paste special dialog box
Step 3: In this dialog box, check the following options if not checked already: new series, columns, series names in the first row, and categories (x labels) in the first column, and click on ok.
Checkboxes in paste special
The spreadsheet will look like this once you click ok.
updated chart
Here, the benchmark data is also added to the activated chart.
Step 4: In this chart, right-click on the benchmark chart section (in red color) and select the change series chart type option. You will see a dialog box like this:
Change chart type option
Step 5: In this dialog box, go to the X Y (Scatter) option and choose the scatter with straight lines option.
X Y Scatter
Step 6: Click ok. You will see a horizontal benchmark like this:
horizontal benchmark
Step 7: Right-click this horizontal benchmark and go to the format data series option. Then select the primary axis option as shown below:
Series option
Step 8: Close this dialog box, you can see how the horizontal benchmark appears now:
Horizontal benchmark
Note that you can use this method for other graphs like line graphs and area graphs as well. That was one method, now let us discuss the other method to achieve this.
Method 2 – Add new data to add a horizontal benchmark in Excel chart
In the above method, we made a separate table for the benchmark. But this time, we will make another column for the benchmark values.
Step 1: Add a new column to the sales table with benchmark value 30000 as shown below:
Benchmark column
Step 2: Right-click the sales chart and choose the select data option. A dialog box will appear.
Step 3: In this dialog box, click the add button under the legend entries section.
select data source
Step 4: You will see another pop-up. In this pop-up, write Benchmark under the series name and give the benchmark column values in the series values. Remember to exclude the column header while you do that. Then, click ok.
edit series
Step 5: You can see that the benchmark chart is added to the sales chart.
benchmark chart
Now repeat steps 4 and 5 of Method 1. You will see the benchmark line as follows:
Horizontal benchmark