how do I create a blank cell in excel 2003?

[Guest]

Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

 

[Guest]

Not sure I understand your question.

But what I use to do when Excel is putting zeros in cells that I want to
keep blank, is as follow:
(Keep in mind that I am using I Norwegian Excel program, so I have to
translate to English)

Right click the cell(s) you want to changes and select “Format cellsâ€. Go to
“Number†(it’s where you choose what format your data should have in the
cell) and choose “own definitionâ€. Wright “ “ (exclamation mark with space
between) if its text that appears as zero or just # if its numbers.

Have a nice day
ElFrodo

 

[Guest]

ElFrodo,


Thanks much for the very prompt reply. I obviously, wasn't clear enough. I
am using the INDEX function to pull data out of a dataset. Cells that are
blank get converted to zeros by the INDEX function. I would like to change
the zeros in the cells back to blank cells so that when graphed, the graph
will show a gap because there is no data rather than a zero. I don't know if
that's clear enough.

 

[Jon Peltier]

There is unfortunately no function that returns a blank. We would like to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A in the
chart, which isn't exactly a blank: no point is drawn in a line or XY chart
for this cell, and any line connecting data points connects the points on
either side.

- Jon

 

[Guest]

Jon,

Thank you for your prompt reply. I stumbled upon the NA() solution and
while it's misleading (if there is no data, there should be a gap in the
graph) it's better than the graph going to zero and then coming back up.
I'm assuming that the MVP group has pushed for such a function to be included?

Thanks again.

 

[Bernard Liengme]

If I understand correctly:
You have plotted a range of cells which include one or more empty (blank cells)
On the chart you want the line to break when at a blank.
Click chart to activate the chart; use Tools | Options and open the Chart tab
Select "Plot empty cells ..Not potted (leave gaps)"
best wishes

 

[Guest]

Bernard,


Oops...I'm obviously, not being clear enough. I am pulling data from a
dataset and in some instances doing mathematical manipulations. Excel
converts a blank cell to a zero when you use a lookup function (I'm using
INDEX). I can easily enough determine that there is a blank cell in the
dataset using the ISBLANK function. What I've not been able to do is create
a blank cell in the data that I'm using to create a graph. If I could create
a blank cell, I would be able to show a gap in the data which is accurate.
For now I'm stuck with plotting the zero that Excel returns or use the NA()
function to interpolate over the missing datum.

Don't know if this is any more clear that my original note.

 

[Bernard Liengme]

I agree Excel should handle this. But it does not so:
In B2:B20 I have a formula that either returns a number or it returns #N/A
I select B2:B20 and run this macro
Now I use the C column for my y-values in the chart
Hope this helps

Sub tryme()
For Each cell In Selection
If IsNumeric(cell.Value) Then
cell.Offset(rowOffset:=0, columnOffset:=1) = cell.Value
End If
Next
End Sub