• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Suppress unused labels in chart

Hui

Thanks for your help.

I have used the marker technique before, and it is a good idea to use it here.

You might not believe this, but I don't think that my problem has been solved.

This is what I did:

I followed your instructions and created call-outs rather than labels.
The call-outs worked great, except that the last 2 blank data points also had call-outs.
Easy enough to get rid of them.
I thought that this had fixed things and tried to see if I could get the call-outs to be in an optimum position relative to the chart.

I thought that I would have a look at exactly how you had created your chart.
I opened the file you uploaded.
There was a call-out for every single data point.

It looks to me as though I am back to square one. I wonder whether there really is a difference between a call-out and a label surrounded by a shape. I know that some label shapes will only display a certain amount of text.

It seems to me that you can achieve pretty much what you want, but when the data are saved, Excel assumes that you want call-outs even for blank rows within the data.

Am I missing something, or is this indeed a task for VBA?

I am really grateful for your help.

Regards.

Alan
 
Alan

Simply select the Markers,
Press Delete
Then Press Undo

Sounds ridiculous except it works
 
Hui

Wow!

I would not have thought of doing that, but then again I am not an Excel Ninja.

Thanks for your help. That is certainly something I can cope with, but if Narayan can come up with a VBA solution, then I can give the worksheet to someone with no Excel knowledge.

Chandoo's community is excellent!

Very many thanks.

Regards.

Alan
 
Hi Alan ,

I have the same problem as Misra ; all the labels show CELL RANGE. I use Excel 2007.

You can see what this code does at your end.
Code: 
Private Sub Chart_Format()
            Dim r As Integer
            Sheets("Weight Chart").Activate
            With ActiveChart
                For r = 1 To Range("Table1[Label]").Rows.Count
                    lbltext = Range("Table1[Label]").Cells(r).Value
                    If lbltext = vbNullString Then
                        On Error Resume Next
                        .SeriesCollection(1).Points(r).DataLabel.Delete
                        On Error GoTo 0
                    Else
                        .SeriesCollection(1).Points(r).ApplyDataLabels
                        .SeriesCollection(1).Points(r).DataLabel.Text = Range("Table1[Label]").Cells(r).Value
                    End If
                Next r
            End With
End Sub
This will not insert callouts ; all it does is delete the unwanted labels.

It inserts the text in column G as labels for the Actual Weight series.

Narayan
 
Hui and Naryan

Thank you both for your help.

To Hui

I thought that I had cracked it after your last post, but I saved the file, reopened it, deleted the labels and then pressed 'undo'. All the labels returned.

Then I did what I should have done in the first place. I used markers as you suggested to highlight the data points which should have comments. I then added labels to that series. I have saved and reopened the file a number of times and the labels only attach to the marked data points.

I have spent hours trying various things, and now it just seems to be so obvious.

To Naryan

Thanks for your help. I added your script to my file, but couldn't get it to work. If I understand what you have done, the script will add labels to the chart, rather than remove superfluous labels.

To you both

Very many thanks for your help and indulgence.

Best regards.

Alan
 
Don't ask me why Deleting and Undoing works ?

Don't ask me how I even knew to try that, as I have never used the Callout Markers !
 
You must log in or register to reply here.
Back
Top