You are on the Home/Excel/Charts/Straight lines page

Adding a horizontal line to a chart

There are many instances when one needs to add a visual indicator to a chart. This could be a line indicating a goal of some sort. Or, it could be a line showing the average of the graphed data. In TQM control charts, it could be a line so-many standard deviations from the mean.

One way draw such a line would be to calculate the values in cells in a worksheet, and plot these cells on the chart. Another way is to use a named formula, which means that the charted threshold values do not show up on the worksheet. This tutorial shows how to use a named formula.

The example uses the data set on the right. Of course, you will use your own data.

First, create the chart from the data set. The example uses a XY Scatter chart.
Next, define two names. The first, aRng, is just for convenience. The second, meanLine, is the one that generates as many numbers as in the original data, each equal to the average. How?	How does the meanLine named formula work? (a) OFFSET(aRng, 0,1) selects the 2nd column of data, i.e., the y-values. (b) AVERAGE(...) gives the average of y-values. (c) ROW(aRng)/ROW(aRng) creates an array of ones. The array has as many data points as in the original data. Multiplying (b) by (d) replaces the ones in the array by the average value.
Next, add a new series to the chart, using this newly defined name. How?
Format the new series to hide the markers and show the line. The job is done!