As Andy said, however, I hope you are really asking about a line chart, not a
bar or column. If column or bar, what did you want - how do you want to plot
nothing and 0?
It's a good question, and I felt inhibited from having a go at it
because I worried that the OP wanted some non-zero value attached to
either the zeroes or the nulls. Like the disappointing Excel 2007
in-cell bars that render zero as approximately 0.1, because
absence-of-bar unsettled the test audience.
If zero always means zero, then you should definitely trust your
audience to be smart enough to interpret a blank as 0.00. But it seems
to me that if zero could be either 0.00 or "not available", then that's
a *second piece of data*, and should be displayed as such.
Some ideas:
Display as ghost column
***********************
Where the data has not been returned, show a bar of a different
colour, maybe paler than the data, or no fill colour and a plain or
dashed line. Make the column of some stereotypical height like 100%
when the data was not returned, and of course zero if it was.
How to do it in Excel
*********************
Create a second column data series, with the values of N or 0 as
above, then format the series with the pale fill, or no fill. Use
Format Options "Overlap = 100%" to make the bars lie on top of one
another without being stacked. Naturally one column will always be
zero when the other is non-zero. When they are both zero, leaving
a blank, it means data came back and it was actually $0.00.
Display as text
***************
Where the apparent zero is due to non-return of data, place a text
label saying so. The absence of such a label should be interpreted as
data having been returned, in which case the actual value is clearly
zero.
How to do it in Excel
*********************
Create a dummy data series, similar to the ghost column above, and
use one of the labelling add-ins
http://www.appspro.com/Utilities/ChartLabeler.htm
http://j-walk.com/ss/excel/files/charttools.htm
to add data labels, then format the series with no line and no
fill so it's invisible, and only the text label shows. Use Format
Options "Overlap = 100%", as before, to make the bars lie on top
of one another without being stacked.
Display as symbol
*****************
Where the data has been returned, place a symbol saying so. The
absence of such a symbol should be interpreted as data having *not*
been returned, in which case the blank is explained. Alternatively,
use a symbol for non-return, but I feel that's just iconising the
text label method above, and I would prefer to assume my chart's
viewers can read plain English
How to do it in Excel
*********************
Create a data series, and make the chart type = Line. Format the
line as "Symbol and No Line", and give the symbols all the same
y-value so they line up nicely somewhere convenient, and have the
Legend explain what the symbol means. When the data is zero, let
the symbol be present, but when the data was never returned, let
the value be blank, and the symbol absent.
Display as Background Colour
****************************
Where the data has been returned, place a column behind the data
column with a colour value to indicate the fact. The absence of such
a colour should be interpreted as data having *not* been returned, in
which case the blank is explained.
How to do it in Excel
*********************
Create a data series, and make the chart type = Column. Format the
columns as "Secondary Axis", and give the columns all a y-value
that covers the plot area from top to bottom. Format the columns
as Format Options "Gap Width = 0%" to make the background
completely cover the width of the x-value. Any white left in the
plot area means "they never provided the data".
