Data label with text "N/A"

[exalan]

Hello,

I've a simple column chart plotting % of daily on time delivery. There are
days that I don't have any delivery so I insert "N/A" to the relevant cells.
However, when I inserted data labels to the chart, those values with "N/A"
were shown as 0%.

Without hiding the rows with "N/A", what other ways can I do to have data
labels not showing "N/A" as 0%?

Many thanks in advance...

 

[Jon Peltier]

A line or XY chart skips a point when it encounters #N/A (note the # prefix,
which makes it an official Excel error). No point, no data label. N/A is
treated as text, so it is plotted as a point at zero, and it will have a
legend.

In column or bar charts, #N/A is treated the same as text, so the best bet
is to leave the cell blank. However, you can use a little number format
trick. Show the labels, then give the labels a custom number format of

0%;;;

There are four settings, separated by semicolons. The first, for positive
numbers, uses 0%. The other three, for negatives, zero, and text, use no
format, so those values are not displayed. Note: if you use the official
Excel error #N/A, this error will appear regardless of number format.

- Jon

 

[exalan]

Hello Lon

Many thanks for the advice.