Add Labels to Points that are Off Scale.

Data labels are a handy way to annotate a chart. But if a point is off scale, the data label also disappears. This page shows how to display alternate labels that indicate out of range points.

In this chart, all points appear on scale, and all data labels are visible.

In this chart, the Y axis maximum has been manually set at 12, but two points exceed this limit. We know this only because the lines extend off scale.

We will add a new series to hold the necessary data labels. The data is shown in the grid below. Cell C1 holds the Y axis maximum. Columns A and B contain the data that is already in the chart. We've included some formulas in column C. The formula in C3 is:

=IF(B3>$C$1,$C$1,NA())

Select the cell, click on the small square at the bottom right corner of the cell, and drag it down to fill the range down to C10. This formula shows either the Y axis maximum, if the data value exceeds the maximum, or the #N/A error if it doesn't. In a line or XY series, #N/A values are not plotted, and no data labels appear for these points.

	A	B	C	D
1			12
2		Values	Outliers	Formula in Column C
3	1/28/2006	10	#N/A	=IF(B3>$C$1,$C$1,NA())
4	1/29/2006	13	12	=IF(B4>$C$1,$C$1,NA())
5	1/30/2006	7	#N/A	=IF(B5>$C$1,$C$1,NA())
6	1/31/2006	8	#N/A	=IF(B6>$C$1,$C$1,NA())
7	2/1/2006	11	#N/A	=IF(B7>$C$1,$C$1,NA())
8	2/2/2006	14	12	=IF(B8>$C$1,$C$1,NA())
9	2/3/2006	10	#N/A	=IF(B9>$C$1,$C$1,NA())
10	2/4/2006	7	#N/A	=IF(B10>$C$1,$C$1,NA())

First we need to add the new data to the chart. Select the chart, and choose Source Data from the Chart menu. Click on the Series tab, then click Add. Click in the Name input box, and select cell C2 ("Outliers"). The box is populated with the cell reference =Sheet1!$C$2. Clear the ={1} from the Values box, then select the data in C3:C10. The box is populated with the range reference =Sheet1!$C$3:$C$10. Click OK, and the new series appears in the chart.

Now we need to add labels to the new series. You can do this manually by adding data labels to this series, then changing the text of each label. If you have more than about three labels, this will quickly become tedious. To do this automatically, you need Rob Bovey's XY Chart Labeler, a free and absolutely must have add-in available from http://www.appspro.com. The Chart Labeler adds an item at the bottom of Excel's Tools menu.

Use the Chart Labeler to label the Outliers series, using the original data in column B as the source for the labels. Select the "Below" label position. The out-of-range labels have been colored red to differentiate them from the in-range labels.

When the Y axis maximum is set back to 12, the two original points disappear, but the added labels show where the points crossed above the top of the chart.

Time for clean-up. Double click the added series, and on the Patterns tab, choose None for Markers and for Lines, to hide the series. Select the legend, then select the legend entry (the "Outliers" label), and press Delete to remove this extraneous legend entry.

Instead of hiding the markers for the Outliers series, you can apply a custom marker to help indicate the out-of-range points. This is especially useful if the chart series has no lines connecting the points. For this chart I drew a red arrow in the worksheet, copied it, selected the Outliers series, and pasted, to assign the arrow as the series marker. For more information about this technique, refer to the Custom Chart Series Markers on this site.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile