reference lines

When you plot some data, it’s common to want to add a line to the chart to provide some context to the data. The line may represent a target value, a budget, a typical level, or some other reference. This tutorial will show how to create a chart with a horizontal line that appears behind the plotted data.

Easy Horizontal Line Across Chart

In Add a Horizontal Line to an Excel Chart, I showed how to add a horizontal line to an Excel chart. I started with this data and column chart…

… and I added this data as an XY Scatter series to the chart.

It’s not too complicated, takes only a couple minutes once you know how.

The problem with this easy approach is that the horizontal line cuts in front of the bars, and you may prefer to have the line behind the data.

Let’s Try an Area Chart

I originally had a more convoluted approach to the use of area chart series for this purpose. A long-time reader of my blog, Liu Wanxiang, had a simpler approach. His approach saves numerous steps and is easier to understand and explain, and there is no benefit to my original protocol. With Liu’s permission, I have modified the rest of this post.

We can use an area chart instead of an XY chart to add the line. First I’ll show why it works, and then I’ll show how to do it.

Let’s start with the same simple data and column chart.

Now let’s add this data to the chart. The new series is added as another column chart series.

When we change the second set of columns to an area type, the area is drawn behind the original columns.

We will need to put the column and area series on different axes in order to make the area stretch to the edges of the chart  while leaving the columns where they are. When the area series is plotted on the secondary axis, it still stays behind the bars. This is fortunate, because it keeps the protocol from becoming too complicated.

We’ll modify the area chart data and just use endpoints 0 and 1. We’ll use a transparent fill and colored border on the area series. We’ll remove the secondary vertical axis but add a secondary horizontal axis above the chart, and we’ll adjust the tick positions of this secondary horizontal axis. I know it’s a lot of steps, and I’ll do them individually later when I show how to construct the chart. Note that the area’s border lies behind the bars, just as the filled area was behind the bars.

But we see the other borders of this area chart, on the sides and bottom of the chart. How do we get rid of them?

If we strip away the original columns for clarity, we see that a single area chart series gives us the outlines on all four sides of the area.

Let’s instead use two area chart series: Blank, which will eventually be blank instead of semitransparent as in the chart below, and Target stacked on Blank.

Then we make the Target area transparent and give it the orange border we’ve been using for our target line.

Change the height of the Target area to zero, so the top and bottom edges of the resulting rectangle coincide.

This was the magical step of Liu’s approach: making the Target area series zero units tall. I didn’t even think of it, perhaps because old versions of Excel (2003 and earlier) treated the area chart shape differently. If it was zero height, either the line didn’t show up at all, or it showed up but was double the thickness, I don’t recall exactly which. Probably this was changed in Excel 2007, and I never noticed.

And finally, make the Blank series blank (transparent, i.e., no fill).

That’s exactly what we need: a single horizontal line at the desired Y position. Now let’s build the chart from scratch, step by step.

Build Chart with Horizontal Line Behind Bars

Once again, we’ll begin with this simple data and column chart.

Let’s add two series to the chart, “Blank” which will be a hidden area series and “Target” which will provide the horizontal line with its outline. These will be stacked areas. We want our horizontal line at Y=2.5; we use 2.5 for the Blank values, and 0 for Target. (I’m using a value of 1 for Target for now, just so you can see it as we proceed.)

Copy the data range, select the chart, and on the Home tab, click the Paste dropdown and select Paste Special. In the dialog, choose New Series, in Columns, Series Names in First Row and Categories in First Column.

Blank and Target are added as more clustered columns.

Right click on one of the added series, choose Change Series Chart Type from the pop up menu, change both to stacked areas, and place them on the secondary axis.

Format the Blank series to have no fill so it is transparent. Make Target also transparent, but give it a border.

Now you can change the height of Target to zero. When you get good at this, you can just start out with zero.

Excel added the secondary vertical axis but not the secondary horizontal axis. Click the floating plus icon next to the chart, click the arrow beside Axes, and check the secondary horizontal axis.

Delete the secondary vertical axis (right edge of chart). The area series are now plotted along the primary vertical axis, so they are in sync with the columns.

Adjust the secondary horizontal axis, choosing the On Tick Marks option for Axis Position.

Now hide the secondary horizontal axis: format it to use no line and to have no labels.

The final result, which is a bit more intricate to create than the chart with a horizontal line in front of the bars, is a chart with a horizontal line that lies behind the plotted data.

Example Workbook

You can download an Excel workbook with a few methods for creating a horizontal line across your chart here: Horizontal-Line-Worksheet-2.xlsx.

More Combination Chart Articles on the Peltier Tech Blog

• Pareto Charts in Excel
• Clustered Column and Line Combination Chart
• Precision Positioning of XY Data Points
• Add a Horizontal Line to an Excel Chart
• Bar-Line (XY) Combination Chart in Excel
• Salary Chart: Plot Markers on Floating Bars
• Fill Under or Between Series in an Excel XY Chart
• Fill Under a Plotted Line: The Standard Normal Curve
• Excel Chart With Colored Quadrant Background

A common task is to add a horizontal line to an Excel chart. The horizontal line may reference some target value or limit, and adding the horizontal line makes it easy to see where values are above and below this reference value. Seems easy enough, but often the result is less than ideal. This tutorial shows how to add horizontal lines to several common types of Excel chart.

We won’t even talk about trying to draw lines using the items on the Shapes menu. Since they are drawn freehand (or free-mouse), they aren’t positioned accurately. Since they are independent of the chart’s data, they may not move when the data changes. And sometimes they just seem to move whenever they feel like it.

The examples below show how to make combination charts, where an XY-Scatter-type series is added as a horizontal line to another type of chart.

Add a Horizontal Line to an XY Scatter Chart

An XY Scatter chart is the easiest case. Here is a simple XY chart.

Let’s say we want a horizontal line at Y = 2.5. It should span the chart, starting at X = 0 and ending at X = 6.

This is easy, a line simply connects two points, right?

We set up a dummy range with our initial and final X and Y values (below, to the left of the top chart), copy the range, select the chart, and use Paste Special to add the data to the chart (see below for details on Paste Special).

When the data is first added, the autoscaled X axis changes its maximum from 6 to 8, so the line doesn’t span the entire chart. We have to format the axis and type 6 into the box for Maximum. We probably also want to remove the markers from our horizontal line.

Paste Special

If you don’t use Paste Special often, it might be hard to find. If you copy a range and use the right click menu on a chart, the only option is a regular Paste, and Excel doesn’t always correctly guess how it should paste the data. So I always use Paste Special.

To find Paste Special, click on the down arrow on the Paste button on the Home tab of Excel’s ribbon. Paste Special is at the bottom of the pop-up menu.

You can also use the Excel 97-2003 menu-based shortcut, which is Alt + E + S (for Edit menu > Paste Special).

The tooltip below Paste Special in the menu indicates that you could also use Ctrl + Alt + V, but this shortcut doesn’t do anything for charts.

When the Paste Special dialog appears, make sure you select these options: Add Cells as a New Series, Y Values in Columns, Series Names in First Row, Categories (X Values) in First Column.

Click OK and the new series will appear in the chart.

Add a Horizontal Line to a Column or Line Chart

When you add a horizontal line to a chart that is not an XY Scatter chart type, it gets a bit more complicated. Partly it’s complicated because we will be making a combination chart, with columns, lines, or areas for our data along with an XY Scatter type series for the horizontal line. Partly it’s complicated because the category (X) axis of most Excel charts is not a value axis.

As with the XY Scatter chart in the first example, we need to figure out what to use for X and Y values for the line we’re going to add. The Y values are easy, but the X values require a little understanding of how Excel’s category axes work. Since the category axes of column and line charts work the same way, let’s do them together, starting with the following simple column and line charts.

Note in the charts above that the first and last category labels aren’t positioned at the corners of the plot area, but are moved inwards slightly. This is because column and line charts use a default setting of Between Tick Marks for the Axis Position property. We can change the Axis Position to On Tick Marks, below, and the first and last category labels line up with the ends of the category axis. The line chart looks okay, but we have cut off the outer halves of the first and last columns.

Let’s focus on a column chart (the line chart works identically), and use category labels of 1 through 5 instead of A through E. Excel doesn’t recognize these categories as numerical values, but we can think of them as labeling the categories with numbers.

Now let’s label the points between the categories. Not only do we have halfway points between the categories (1.5, 2.5, 3.5, 4.5), we also have a half category label below the first category (0.5) and another after the last category (5.5).

If the Axis Position property were set to On Tick Marks, our horizontal line starts at 1 (the first category number of 1) and ends at 5 (the last category number of 5). This would be wrong for a column chart, but might be acceptable for a line chart.

Here is our desired horizontal line, stretching from 0.5 to 5.5

So let’s use this data and the same approach that we used for the scatter chart, at the beginning of this tutorial.

Copy the range, and paste special as new series. We’ve added another set of columns or another line to the chart.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu.

In the Change Chart Type dialog, select the XY Scatter With Straight Lines And Markers chart type. We’re using markers to temporarily mark the ends of the line, and we’ll remove the markers later; in general we will change directly to XY Scatter With Straight Lines.

The new series don’t line up at all, though, because Excel decided we should plot the scatter series on the secondary axes. We could rescale the secondary axes, then hide them, but that makes a complicated situation even more complicated.

So we need to uncheck the Secondary Axis box next to the Scatter series in the Change Chart Type dialog.

And now everything lines up as expected: the markers on the horizontal lines are at the edges of the plot area.

We should remove those markers now, and in the future select the chart type without markers.

“Lazy” Horizontal Line

You may ask why not make a combination column-line chart, since column charts and line charts use the same axis type. And many charts with horizontal lines use exactly this approach. I call it the “lazy” approach, because it’s easier, but it provides a line that doesn’t extend beyond all the data to the sides of the chart.

Start with your chart data, and add a column of values for the horizontal line. You get a column chart with a second set of columns, or a line chart with a second line.

Change the chart type of the added series to a line chart without markers.  Doesn’t look very good for the column chart (left) since the horizontal line ends at the centerlines of the first and last column. You could probably get away with it for the line chart, even though the horizontal line doesn’t extend to the sides of the chart.

If we change the Axis Position so the vertical axis crosses On Tick Marks, the horizontal lines for both charts span the entire chart width. In the column chart, this comes at the expense of the outer halves of the first and last columns. The line chart looks okay, though.

Add a Horizontal Line to an Area Chart

As with the previous examples, we need to figure out what to use for X and Y values for the line we’re going to add. The category axis of an area chart works the same as the category axis of a column or line chart, but the default settings are different. Let’s start with the following simple area chart.

Notice that the first and last category labels are aligned with the corners of the plot area and the filled area series extends to the sides of the plot area. This is because the default setting of the Axis Position property is On Tick Marks. We can change it to Between Tick Marks, which makes the area chart look a bit strange.

Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series.

Right click on the added series, and change its chart type to XY Scatter With Straight Lines And Markers (again, the markers are temporary). The resulting line extends to the edges of the plotted area, but Excel changed the Axis Position to Between Tick Marks.

Change the Axis Position setting back to On Tick Marks, and remove the markers from the line.

“Lazy” Horizontal Line

In the column chart, and perhaps for the line chart, the “lazy” approach did not give a suitable horizontal line, since the line did not extend to the edges of the plot area. Let’s see how it works for an area chart.

Make a chart with the actual data and the horizontal line data.

Right click on the second series, and change its chart type to a line. Excel changed the Axis Position property to Between Tick Marks, like it did when we changed the added series above to XY Scatter.

Change the Axis Position back to On Tick Marks, and the chart is finished.

For the area chart, the appearance of the lazy horizontal line is identical to the more complicated line that uses an XY Scatter series. Since it’s easier and just as good, it’s probably better to use the lazy approach.

Follow-Up

An alert reader noted in the comments that the line produced by this method is placed in front of the bars, and it might be better to place such reference lines behind the data. I have written a new post describing an approach that does just this: Horizontal Line Behind Columns in an Excel Chart.

Historical

I showed similar approaches in an old post, Add a Target Line.

More Combination Chart Articles on the Peltier Tech Blog

• Pareto Charts in Excel
• Clustered Column and Line Combination Chart
• Precision Positioning of XY Data Points
• Horizontal Line Behind Columns in an Excel Chart
• Bar-Line (XY) Combination Chart in Excel
• Salary Chart: Plot Markers on Floating Bars
• Fill Under or Between Series in an Excel XY Chart
• Fill Under a Plotted Line: The Standard Normal Curve
• Excel Chart With Colored Quadrant Background