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

How to conditionally format a data label?

D

DashboardNovice

Member
In the attached picture, there are 3 bar charts. In each chart one bar is a different color than the rest. Is this due to conditional formatting?

Also, notice the associated data label. That is also a different color from the other data labels. How do you conditionally format a data label?
 

Attachments

  • Conditionally Formated Data Label.JPG
    Conditionally Formated Data Label.JPG
    31.5 KB · Views: 8
I have a file uploaded. On the tab called 'Dashboard' notice how the combo box over cell B4 has IBM selected. This can be changed. But for now I will use the current selection, IBM, as an example. With this in mind, notice the two bar charts at the very bottom. As of now, all data bars are the same color, i.e. gray. Notice how the associated data labels are also gray.

Here is what I had in mind. For whichever stock is selected, IBM in our case, I want the data bar associated with that stock, IBM to light up in a different color, let's say dark red. Not only that, I want the associated data label for IBM, to also turn dark red.

Therefore at any given time, only one stock's data bar and its data label will be colored in dark red. This will show the user at a glance which stock is currently selected.

Note: I'm fairly sure I can conditionally format the data bars based on the stock selected. However, I am reluctant to move forward with that, until I can be sure that it is in fact possible to conditionally format the associated data label.
 

Attachments

  • Stock Dashboard II.xlsm
    148.6 KB · Views: 7
NARAYANK991 said:
Hi ,

See if this is acceptable.

Narayan
Click to expand...


Great. This is just want I was looking for. To get the data label to color for the second data series I had to show all labels and color them. This worked, but the problem is that the data labels for the other bars (with a zero value) are also showing. I can't find an option where you can enable only one particular data label.
 
Last edited:
Hi ,

The data labels can be formatted with a custom number format to ensure that the zero values are not displayed.

Narayan
 
NARAYANK991 said:
Hi ,

The data labels can be formatted with a custom number format to ensure that the zero values are not displayed.

Narayan
Click to expand...

I see. I got it to work now. I just have two questions:

1. I would have thought you could use NA() in the 2nd data series in cases where you have a non-match (instead of zero), because graphs ignore an #N/A error. Line graphs do ignore the #N/A error. But it seems like column charts and bar charts do not ignore the #N/A error. Did I do something wrong? Or if column and bar charts really do ignore the #N/A, is this why you had to use zeros instead of the NA() function?

2. I'm having trouble understanding the characters in the custom number format. I thought the # and the 0 both represent any number, but I noticed that you are using both. Also, I noticed 3 semi-colons and a negative sign. It seems like you are saying subtract one quantity from another, or something to that effect.

#,##0;-#,##0;;
 
Hi ,

It is easy to hide the zeroes using a Custom Number Format ; I do not know of any way to hide the error values.

Narayan
 
You must log in or register to reply here.
Back
Top