How to address the problem of showing invalid values on a bar chart?

[BNCOXUK]

Hello Sir,

I plotted a bar chart for a column, with values shown on each bar. The problem is that this column has some cells with invalid values #N/A, which make them also shown on the bar. I tried to make these invalid values as null strings "", but again the values shown are 0% on each bar.

Is there anyway that I can restrict the display of these invalid values.

Note: this bar chart is dynamic, and we don't know when some cells have invalid values as the cells can change. This means that we cannot manually do something on the chart

[jake.masters]

Try right clicking on format data labels > numbers and setting a custom format as ##, and then add it. It should get rid of anything less than 1. It depends what your data looks like though, as to the best way to do this.

[BNCOXUK]

Try right clicking on format data labels > numbers and setting a custom format as ##, and then add it. It should get rid of anything less than 1. It depends what your data looks like though, as to the best way to do this.

Hi Jake, I'm using Excel 2003 and tried to do it following your suggestion. Can you explain in more detail how to make this work?

[BNCOXUK]

Does anyone know how to deal with #N/A for a chart?

[FDibbins]

hide the rows with the "" or #NA in them

if there are a lot, use filters

[BNCOXUK]

Hide the rows? Use filters?

hide the rows with the "" or #NA in them

if there are a lot, use filters

[BNCOXUK]

Try right clicking on format data labels > numbers and setting a custom format as ##, and then add it. It should get rid of anything less than 1. It depends what your data looks like though, as to the best way to do this.

Hi Jake. I tried again, and this seemed to work. Thanks. But a problem is that the data values need to be shown as %, therefore any "" values will be shown as 0%.

[FDibbins]

yes, hide the rows, the chat will not show the hidden rows