How to add horizontal line to chart
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types of charts, in this case I am going to combine the column and the x y scatter chart.
What's on this page
Why would you want to use a horizontal line?
It could be a break-even line for revenue or a threshold making it easy to spot specific dates/months/years containing interesting data points.
There are, of course, many more applications.
I am also going to demonstrate how to insert a vertical line to a bar chart.
To the right are links pointing to different sections on this page.
Check out the Charts category for more interesting articles.
Insert a chart
The first step is to create a chart with only one series. You can see my data in cell range A1:B7, see picture below.
- Select A1:B7
- Go to tab "Insert" on the ribbon
- Click "column chart" button
The excel column chart is created, shown in the picture below.
Add a second series
Now it is time to build the line. I will now add more data, see cell range A9:B11 in picture below.
This series will be the horizontal line on y-axis value 140.
- Right click on chart
- Click on "Select Data..."
- Click "Add" button
- Select cell B9 for "Series name:"
- Select cell range B10:B11 for series values
Excel colored the second series differently so you can distinguish them easily. There are only two columns in the second series because of the two cells we selected as series values.
Related article:
Improve your X Y Scatter Chart with custom data labels
The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on […]
Improve your X Y Scatter Chart with custom data labels
Change series chart type
I am now going to change the chart type for the second series.
- Right click on the second series on the chart.
- Click on "Change Series Chart Type"
- Select chart type "Scatter with smooth lines"
- Click OK button
The second series is now a red line between Jan and Feb.
You can change the width and angle of the line by editing the values in cell range A10:B11.
Related post:
Highlight a column in a stacked column chart
This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows […]
Highlight a column in a stacked column chart
Build a horizontal line
- Type 0.5 in cell A10
- Type 6.5 in cell A11
- Right click on chart
- Click on "Select Data..."
- Select series "y"
- Click on "Edit" button
- Select cell range A10:A11 for "Series X values:"
- Click OK
- Click OK
Related post:
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
Final chart customizations
These are the steps I made to remove gridlines, smaller line and change the second series name value.
- Click on a grid line
- Press Delete button on keyboard
- Double click on horizontal line
- Decrease line width
- Change line color
- Click Close button
- Type "Break even" in cell B9
If you want to know in greater detail how to customize a chart element, read the following post:
What's on this page Chart area Chart title Legend Axis title Axis lines/values Tick marks Plot area Chart series […]
Download excel *.xlsx file
Combine a column chart and a xy scatter chart.xlsx
How to add a vertical line to a chart
A vertical line on a bar chart would be just as useful as a horizontal line on a column chart.
The picture above shows a black line on value 7.5 with transparency ca 50%.
Here is how I built this chart.
Insert a bar chart
- Select the data.
- Click on "Insert" on the ribbon.
- Click on "Column" chart button.
- Click "2D Clustered Bar"
The bar chart is now visible next to the data.
Add a second series
The x value is where on the chart I want the line to appear, the y value is a value you probably have to guess and adjust later.
- Right-click on the chart and click on "Select Data..."
- Click on "Add" button shown above.
- Select the x values.
- Click OK button.
- Click OK button.
The chart now contains two series, the first series is blue and the second series is red.
Change the chart type of the second series
- Right-click on the chart and then click on "Change Chart Type...".
- Click on tab "Combo"
- Select the clustered bar chart for series1.
- Select the Scatter with smooth lines chart for series2.
- Click OK button.
The chart above has two x axis, one at the top and one at the bottom. The y axis regions disappeared and new y axis values appeared.
We certainly have some cleaning up to do.
Adjust scatter chart values
- Right-click somewhere on the chart.
- Left-click on "Select Data...".
- Select Series2.
- Click on "Edit" button.
- Select the x values.
- Select the y values.
- Click OK button.
- Click OK button.
The chart now looks like this.
Chart formatting
- Delete all axis values
- Select the chart.
- Go to tab "Design" on the ribbon.
- Click "Add Chart Element" button.
- Click "Axis".
- Click "Secondary Horizontal ".
- Repeat steps 4-5, then add a "Vertical Horizontal" axis.
To move the axis region values left follow these steps.
- Double click the x axis values above plot area.
- Go to tab "Axis Options"
- Select "Vertical Axis crosses" axis value 0 (zero).
To move the axis values follow these steps.
- Double click the y axis values to the left of the plot area.
- Go to tab "Axis Options"
- Select "Horizontal axis crosses" at category number: 0 (zero).
- Exit Settings
Double click on the line to open the settings. Change the line color, width and transparency.
The line is not covering the entire plot area, here is how to fix that.
- Select the chart.
- Go to tab "Design"
- Add the "Primary Vertical" axis
- Double click on axis values to open the settings.
- Go to tab "Axis Options"
- Change the maximum value so that the line covers the entire plot area. 8 worked for me.
- Delete the "Primary Vertical" axis.
To add a Legend follow these steps.
- Select chart
- Go to tab "Design"
- Click "Add Chart Element" button.
- Click "Legend"
- Pick a position for the "Legend"
Download Excel *.xlsx file
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
Highlight a column in a stacked column chart
This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows […]
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]
Improve your X Y Scatter Chart with custom data labels
The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on […]
How to build an interactive map in Excel
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This picture below shows you a column chart with pictures (flags) below each column. Watch this video to learn how […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.