Since I know I have a few charting guys that follow this, I figured that I’d ask this question here. Hopefully the response will help someoneâ€¦
I’m creating a food & beverage function evaluation form, and threw a chart on it. The point behind this chart is to let someone scroll through the number of customers, seeing how much profit the event will earn.
Using a trick that Mike Alexander covers in his latest dashboarding webcast, I added a second chart series. The scroll bar links to a cell and that controls a column of data that shows the value indicating the profit point for the selected customer, or #N/A for anything else. This allows for the single data point on the line chart shown below:
Okay, so this is fine, but it’s really hard to tell how many customers and how much profit (or loss) is evident at that point. So I thought I’d add a data label to it. So I selected the series from the legend (not shown on the chart here) and chose to add Data Labels. It came out like this:
Well that’s just nasty. And scrolling through the scroll bar didn’t change anything.
With a little playing though, I found that I could set the data points individually. So I tried a little VBA to set each and every data point individually:
Dim c As Long
For c = 1 To 100
Now this was much better, and yielded the following:
Okay, it’s still not perfect, but that’s not the point here. Why should I have to set each data point in the series manually to have it correctly recognize that it should not be plotted if the value is #N/A?
I just assumed that this would be a bug at first, but now I’m not so sure. If you have a legend on the chart and hit it with the following codeâ€¦
â€¦ then the chart turns back into the first one I showed, with all the clustered nasty elements. Soâ€¦ does this mean that telling Excel that you’d like Data Labels on your series is not the same as “turning on” a collection of data labels? I would have assumed that it was intended to be the same.
I’m curious as to people’s thoughts hereâ€¦ is this a bug, or a feature? I’m still leaning towards bug, since it seems to be attempting to display the #N/A values, which are not supposed to be charted.
What do you think?