• 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.

Data labels on interactive charts [SOLVED]

E

exceljockey

Member
Hi


I am currently using Chandoos method of interactive graphs of having tables that populate to #NA for undesired values on the chart. So if i click january as the month, then all other months and values would go to #NA in the tables, so that the graph only shows Januarys values.


Now the problem is, if I want to show data labels then my graph will contain a bunch of #NA values, which is unpleasent to the eye. How do I make it so that any #NA values do not show on my data labels?


I hope I have explained the problem well enough, I can elaborate further if necessary.

Thanks
 
Hi ,


See if this link helps :


https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.excel.charting/tvUxaSledAI


Narayan
 
Narayan, I think that this is along the right lines of what I need to do but i can't quite make sence of it.


Here is the formula in one of the cells that shows as #NA: =IF($AC$27=$X$36,Y31,NA())


So when January is selected for example, then Y31 will appear, otherwise it will be #NA.

So on the graph, this value will be shown as #NA when data labels are enabled. What i then tried was rather than doing an NA if value is false, i changed the formula to =IF($AC$27=$X$36,Y31," ")) so that the cell will be shown as blank, but then the data label shows a 0 as that link you gave me rightly said.


So I click on the data label that says 0 or #NA (depending on which formula I use), and I go to custom, and type 0%;;; but nothing changes. Could you possibly elaborate on what that message meant exactly?


Thanks
 
Also, If I change the 0s to not show up on the data labels, then will this mean that when this value is not 0, then the data labels will still not show?
 
Hi ,


Since the format given ( 0;;; ) does not display text , your formulae will have to be rewritten ; if you have a formula as follows :


=IF($AC$27=$X$36,Y31,NA())


rewrite it so that the unwanted data points have text attached to them as follows :


=IF($AC$27=$X$36,Y31,"#N/A")


Is this possible in your case ?


Narayan
 
Hi Narayan


I have ammended the formula, the problem is that when I format the data label to go blank by using the ;;; in custom formatting, then this does get rid of the 0 values on my data labels BUT when these values aren't 0 but turn into other values when i select a different category on my interactive section, then these invisible numbers stay invisible, but i want to be able to see what values they turn into.


This is badly explained, let me elaborate:


I have 3 different sections, A,B,C. If i click on A, then my bar chart will only show all of the values under section A, and all of the other sections will be #NA and therefore not show on my graph, however data labels enabled shows the #NA values as 0 - i want my data labels enabled so that you can clearly see all of the values of my bar charts when A is selected.


Now when i select B, then all of A and C turn into #NA and therefore only Bs values are shown on the bar graph but again, if i enable data labels then all of the 0s and #NAs are visible.


So if i click on the data labels that say #NA or 0, using the custom format function ;;; i can make them invisible as you showed me. HOWEVER, once i go back to A, then the values that were invisible that are no longer 0s, still stay invisible.


What i want is data labels to NOT show any 0s or #NAs ever, regardless of what section is selected. Do you get my problem?


Thanks again for your help
 
Hi ,


The format 0;;; may not be the correct one ; the first section , which has 0 , is for positive numbers , the next section is for negative numbers , the third for zeros , and the last for text.


If you want positive and negative values to be displayed in any custom format , specify the formats , and then have two semi-colons at the end , to suppress zeros and text.


Narayan
 
Ok so what you are saying is that my answer definitely lies in this formatting, Ill have a play around with it and hopefully it solves my problem.


Thanks again for your help!
 
Id just like to confirm that this worked!

I used 0;;; and it worked exactly as required.

Thank you so much for your help, youve been great.
 
You must log in or register to reply here.
Back
Top