• 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

Greenbriars

Greenbriars

Member
I have a line chart in which I have a series consisting of labels. The label is either blank or populated.

I want to be able to show only the populated labels. My problem is that the blank labels appear on the chart as small rectangles. Of course I can get rid of these individually, but is there a way to eliminate these from the chart automatically?
 
OK. So I have anonymised the data and guess what? The blank labels have disappeared in my working file.

However, if I close the file and then reopen it the blank labels reappear.

Hope this makes sense.
 

Attachments

  • Chandoo1.xlsm
    104 KB · Views: 9
I Could not find any blank lables in you data. When I open the the file it was showing [Cell Range] as lable I selected it and delete it. Than inserted lable.


Regards,
 
Somendra

Very many thanks for looking at this, but I really don't understand your reply. I am sure that you realise that the label series should come from column 'G' in the 'Data Input' sheet. If I open my version of the workbook, I can see the labels which are in column 'G' but I also see small rectangles for the blank items in the series.

Regards.

Alan
 
Somendra

Are you using Excel 2010? I use 2013, but have just opened the file in 2010 and can see what you have told me, but I am not sure how to add the labels as a series in 2010.

Regards.

Alan
 
Hey Sorry, I hold the wrong column for lables, The way I can figure out this is to do manually, Selecting only those lables with custom labels and delete the rest.

May be somebody will come up with a better idea.

Regards,
 
Faseeh

Thanks for the tip. I have just tried it, but I don't seem to be able to format the labels as a group, or to set an arrow to point the label to the data point.

Am I missing something?

Regards.

Alan
 
Faseeh

Thanks for your help, but this is not quite what I want. If I simply want a label, then this is no problem. However I want to add more data to the label (see column 'G') and the text then becomes quite long and covers 2 lines.

In order to allow the reader to focus on the point to which the label refers, I would like the label to be contained in a box which points to the data.

However, when I enclose the label in a box, I also get boxes with blank data from the blank rows in column 'G'.

If there is no way to get round this using normal charting tools, I guess that I need a VBA script which would cycle through the individual labels on the chart and delete those which contain no data.

I hope that this is clearer.

Thanks for your help.

Alan
 
Now this is clear, but using the aforesaid software, you can refer to Col G to add labels to your chart, what is present int he cell will be displayed in the chart as label, if they are blank there will be nothing in the chart, if there is something, it will be displayed as Label. and if even then it does not work, you will definitely need vba.
 
Faseeh

Very many thanks for your input. I agree with and understand all that you say. XY Chart Labels is a very interesting add-in.

However, because my labels contain a lot of text and there are weekly data points, I feel that I need to have a label which points to the actual data, not hovers somewhere close to it.

I agree that VBA is the answer. I just hope that somebody can supply it. It is completely beyond me!

Best regards.

Alan
 
You are welcome, since i am also unfamiliar with VBA, i will not be able to add anything further here. Thank you.

(Hui is an expert in charting, might he provide you with a solution.)
 
Do you mean like this ?
Capture.PNG


See the attached file
 

Attachments

  • Chandoo1.xlsm
    94.9 KB · Views: 4
Hui

I apologise for my earlier breach of etiquette!

Thanks for your interest.

You have the file, but I have added a couple of screen dumps to show you what I have and what I would like to have.

The idea is that there will be weekly data points over a period of (say) 3 years.
On a small minority of occasions there will be something noteworthy about the data.
On such occasions a comment has additional data added to get to a label in column 'G'.
I need the label to point to the data point to which it refers.

I can do this as you have shown, but by the time the additional data is added to the comment, the label becomes quite large and it is not possible to identify easily the data point to which it relates.

My idea was to enclose the label in a box with a pointer which is helpfully provided in Excel 2013. This makes it easy to see where the relevant data point is in the series.

Having done this I find that all the blank rows in column 'G' end up with their own small, but empty comment box.

At the moment I can go through the superfluous label boxes and delete them. My idea is to have this process performed automatically so that as the data increase, the task of producing the chart does not become more cumbersome. or that someone else could produce the chart easily.

My thought was that this is a problem that VBA would be able to deal with. Cycle through the labels in a chart and delete those with no text. I don't know where to start.

Many thanks for your interest.

Regards.

Alan
 

Attachments

  • What I would like.jpg
    What I would like.jpg
    88.9 KB · Views: 10
  • What I have.jpg
    What I have.jpg
    107.6 KB · Views: 10
Alan
In 2013 you can do this easily

Add a column to your Input sheet that has the text you want to display
Each cell that shouldn't have a value must be blank
You have done this in Column G

Now Select the Chart Sheet,
Then Select the Series
Right Click on the Series, Add Data Labels, Add Data Callouts

Now click a Data Label
Right Click , Format Data Labels
Tick Value from Cells and select the Range ='Data Input'!$G$4:$G$62
Untick Category Value and Value Boxes
Apply

Capture.PNG
Enjoy
 
I would actually add another series See Column y2
And use that to highlight the points (No line, Include Markers)

Capture.PNG

See Attached file:
 

Attachments

  • Copy of Chandoo1.xlsm
    104.8 KB · Views: 6
SM

The callouts are new in 2013, Which he said he was using.
You can do the Circles in all Excel versions
 
Not that I can see or know about
You can import the 2013 files into 2010, but it looses the callouts and reverts to a rectangle
 
You must log in or register to reply here.
Back
Top