Data labels out of reach

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a chart with data labels "outside end" but one of the bars is too high
so It doesn't fit my scale and that label is not displayed. So I increase the
scale to find the label, and manually drag it below my chart's previous max
value. Then I change the scale back but then the label is not displayed. It
is only displayed as long as the whole bar fits in the chart. Any ideas
(except writing the label manually)?

/Marcus
 
If the maximum Y axis is 500,000 (for example) and the column with the label
out of sight is 499,000, I can see that. Can you not increase the maximum to
600,000 (for example)? Will that not do it? Connie
 
Yes, but I don't want the whole bar to be displayed. I want it to go
"through the roof" and still display its value.

/Marcus
 
Hi,

You
You could use a 2nd dummy series to hold the data labels of those points
that go 'through the roof' of your chart. I have assumed that you can
place the scales maximum value in a cell for use in formula.
Here is some example data and formula,

C1:= 10
B2:= S1 C2:= DummyS2
A3:= a B3:= 1 C3:=IF(B3>=$C$1,$C$1,NA())
A4:= b B4:= 2 C4:=IF(B4>=$C$1,$C$1,NA())
A5:= c B5:= 13 C5:=IF(B5>=$C$1,$C$1,NA())
A6:= d B6:= 4 C6:=IF(B6>=$C$1,$C$1,NA())
A7:= e B7:= 5 C7:=IF(B7>=$C$1,$C$1,NA())

Create a clustered column chart on the range A2:C7.
Apply data labels to the S1 series.
Set the Y axis maximum to 10, this should cause the data label for value
13 to disappear.
Now format the DummyS2 series such that it is on the secondary axis, is
of chart type line.
Once it is on the secondary axis you can then use the source data dialog
to set the secondary axis labels to be thoses of B3:B7.
Apply data labels to the DummyS2 series displaying Category instead of
value. Set the alignment to above. Format the line and marker style to
none. Final thing to do is clear the secondary Y axis so the DummyS2
series uses the same scale as S1

And now because the line point is within the plot area the data label
should display just above the bar.

Cheers
Andy
 
Back
Top