Data labels only for positive values

[ankita.sethi]

Hi,

I am dealing with a lot of charts where I want the data labels to be visible only for the points with a positive values attached to them (otherwise the chart looks very clobbered).
Can anyone help me with this? Any solution (VBA or otherwise) will be of great help!

Thanks in advance

 

[CWatts]

There are two ways:
1. Use a custom number format to display blank when the value is negative. by formatting your data labels with this custom number format: 0;"";0;@

2 Make a separate line for data labels. This works best for more complicated criteria as the custom number format has less options.

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:46px;"><col style="width:27px;"><col style="width:30px;"><col style="width:31px;"><col style="width:37px;"><col style="width:36px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td style="text-align:center; ">Jan</td><td style="text-align:center; ">Feb</td><td style="text-align:center; ">Mar</td><td style="text-align:center; ">Apr</td><td style="text-align:center; ">May
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Data</td><td style="text-align:center; ">1</td><td style="text-align:center; ">5</td><td style="text-align:center; ">-6</td><td style="text-align:center; ">4</td><td style="text-align:center; ">-2</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Labels</td><td style="text-align:center; ">1</td><td style="text-align:center; ">5</td><td>
</td><td style="text-align:center; ">4</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B3</td><td>=IF(B2>0,B2,"")</td></tr><tr><td>C3</td><td>=IF(C2>0,C2,"")</td></tr><tr><td>D3</td><td>=IF(D2>0,D2,"")</td></tr><tr><td>E3</td><td>=IF(E2>0,E2,"")</td></tr><tr><td>F3</td><td>=IF(F2>0,F2,"")</td></tr></tbody></table></td></tr></tbody></table>

 

[ankita.sethi]

Thanks for the quick reply

The data labels I have are category names as well as percentages. Can you suggest how to custom format these?