If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data label with text "N/A"
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... -- exalan |
#2
|
|||
|
|||
Data label with text "N/A"
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "exalan" wrote in message ... 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... -- exalan |
#3
|
|||
|
|||
Data label with text "N/A"
Hello Lon
Many thanks for the advice. -- exalan "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "exalan" wrote in message ... 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... -- exalan |
Thread Tools | |
Display Modes | |
|
|