Automatic data label when value exceeds axis?

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I have a number of bar charts which are all identical for the purposes
of comparison, but there are one or two data-values which exceed the X
axis. There is no reason to re-size all the charts [assume for the
moment there are 10000 values and that only fifteen of them exceed the
X-axis]. Is there any way to have excel automatically produce a
data-label with the value for any values which exceed the x-axis? (It
would seem that Excel 97 did this automatically upon printing)

Any help would be appreciated.

thanks
-br
 
Brad -

Suppose the values were in column B, starting in B2. You can use
formulas to put a helper series in Column C, using this as the formula
in C2:

=IF(B2>E$2,E$2,NA())

and this to hold labels in column D, starting in D2:

=IF(B2>E$2,E$3,"")

where the upper limit is in E2 and the desired label is in E3, if they
are all the same. Change E$3 to B2 to show the value which couldn't be
plotted. Fill these formulas down as far as you have data in column B.

Add a series which uses the same X values as column B but Y values from
column C. Format the series to be invisible (no line, no fill, no
markers). Use a third party labeling utility, such as Rob Bovey's Chart
Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com) to apply the labels in column D to the series based
on column C. Use the Below option for label alignment so they appear
below the top of the plot area.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,
Thanks. Your workaround also works, so I figured I'd post this
reply just as a thank-you. It would seem the issue I am experiencing
is a known bug [it took me hours of research, since not everyone uses
the exact same terminology] in the knowledge base.

http://support.microsoft.com/default.aspx?scid=kb;en-us;811161

The problem is that without this patch, the labels simply won't
appear.

[Before applying the patch, I tried it your way, and it works
marvellously. Thank you!]

-br


Jon Peltier said:
Brad -

Suppose the values were in column B, starting in B2. You can use
formulas to put a helper series in Column C, using this as the formula
in C2:

=IF(B2>E$2,E$2,NA())

and this to hold labels in column D, starting in D2:

=IF(B2>E$2,E$3,"")

where the upper limit is in E2 and the desired label is in E3, if they
are all the same. Change E$3 to B2 to show the value which couldn't be
plotted. Fill these formulas down as far as you have data in column B.

Add a series which uses the same X values as column B but Y values from
column C. Format the series to be invisible (no line, no fill, no
markers). Use a third party labeling utility, such as Rob Bovey's Chart
Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com) to apply the labels in column D to the series based
on column C. Use the Below option for label alignment so they appear
below the top of the plot area.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
I have a number of bar charts which are all identical for the purposes
of comparison, but there are one or two data-values which exceed the X
axis. There is no reason to re-size all the charts [assume for the
moment there are 10000 values and that only fifteen of them exceed the
X-axis]. Is there any way to have excel automatically produce a
data-label with the value for any values which exceed the x-axis? (It
would seem that Excel 97 did this automatically upon printing)

Any help would be appreciated.

thanks
-br
Click to expand...
Click to expand...
 
Hi Brad -

Coincidentally, I came across this article for the first time last
night. Several of my chart tricks have counted on data labels appearing
on points that lie outside the plot area, and Excel XP came along and
broke those tricks. I even had SP3 installed, but didn't know about the
registry hack.

The method I suggested, however, provides greater control over the
position of the labels, and allows for other kinds of labels, for
example, to indicate ranges of interest within the chart. In the second
chart on this page, all text elements besides the horizontal axis and
the legend are produced by data labels.

http://peltiertech.com/Excel/Charts/ComboCharts.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Jon,
Thanks. Your workaround also works, so I figured I'd post this
reply just as a thank-you. It would seem the issue I am experiencing
is a known bug [it took me hours of research, since not everyone uses
the exact same terminology] in the knowledge base.

http://support.microsoft.com/default.aspx?scid=kb;en-us;811161

The problem is that without this patch, the labels simply won't
appear.

[Before applying the patch, I tried it your way, and it works
marvellously. Thank you!]

-br


Jon Peltier said:
Brad -

Suppose the values were in column B, starting in B2. You can use
formulas to put a helper series in Column C, using this as the formula
in C2:

=IF(B2>E$2,E$2,NA())

and this to hold labels in column D, starting in D2:

=IF(B2>E$2,E$3,"")

where the upper limit is in E2 and the desired label is in E3, if they
are all the same. Change E$3 to B2 to show the value which couldn't be
plotted. Fill these formulas down as far as you have data in column B.

Add a series which uses the same X values as column B but Y values from
column C. Format the series to be invisible (no line, no fill, no
markers). Use a third party labeling utility, such as Rob Bovey's Chart
Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com) to apply the labels in column D to the series based
on column C. Use the Below option for label alignment so they appear
below the top of the plot area.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
I have a number of bar charts which are all identical for the purposes
of comparison, but there are one or two data-values which exceed the X
axis. There is no reason to re-size all the charts [assume for the
moment there are 10000 values and that only fifteen of them exceed the
X-axis]. Is there any way to have excel automatically produce a
data-label with the value for any values which exceed the x-axis? (It
would seem that Excel 97 did this automatically upon printing)

Any help would be appreciated.

thanks
-br
Click to expand...
Click to expand...
Click to expand...
 
Back
Top