Dynamic Charts with Multiple Series - Data Label configuration question (no macros)

[ZL1]

Hello,

So I'm a pretty avid excel user and have all types of cool little tricks and tips i've done over the years with my data...

Today, i'm not able to figure this out.. and maybe there isn't a solution (as i've googled and googled and googled).

I have a chart that has 5 series tied to it. The Series are based off years, broken down by months (series 1 = Jan - Dec for Year selected). The Data selected is a user input range. so it can be a start point of 1/1/2018 and end point of 3/1/201/ and the chart will only show the 3 months. or the user can input 1/1/2013 and end point 1/1/18 and the chart will show the 5 years (month by month comparison for the span of the years).

So, it's easy enough to have offset named ranges to only select the data i need, and i hide the blanks on the chart by using na(), and all of that works great. But when i add data labels, the series that have no data (blanks or 1 designated na() cell), the labels can't be hidden for that cell. Is there a way to make the Series Labels dynamic so that if the series has no data, the labels do not show up? I've tried configuring the number format to $#,###"" but then the $ still shows up.

Only thing I've managed to do is exclude the labels all together, but it looks bare.

any help without a macro would be appreciated.

 

[ZL1]

So it looks like it all factors down to the Data Label Number Formats.

If i have the format as $#,### then the #n/a shows up and the 0's show up as just a $ sign
If i have the format as #,### then the #n/a's go away and the 0's $ sign also goes away.

it's not ideal, but i guess this will have to do.

I did try $#,###"" as the format also, and that didn't seem to make a difference

 

[thisoldman]

See if this works.

Add one semicolon after your custom format string to hide labels for negative numbers and the #N/A: $#,###;
Add two semicolons to the format string to hide all but positive values: $#,###;;

 

[ZL1]

This appears to have worked! awesome, thanks for the suggestion!

 

[thisoldman]

You're welcome. I'm glad it worked.