Charting missing data: empty cells bug

**kazim** · 10-26-2009, 07:21 PM

Hi

I'm trying to chart data with some missing points. I read posts on this forum on this topic and checked with my settings but they seem to be OK. Still, my empty cells are plotted as zeros. Only one (!) cell out of 5 is not plotted(and I failed to figure out how that one was different from the rest).

Here are the current settings:

- Excel version is 2007 French.
- Cells are not hidden, but protected.
- They are customized at "empty cell" format (;;; ) (a tip I read on internet, but that might be wrong)
- Empty cells are set to be plotted as gaps.

Your help would be very much appreciated.

**shg** · 10-26-2009, 07:29 PM

The formatting doesn't change the underlying cell content, which is what gets plotted. It certainly does not make it empty.

**kazim** · 10-26-2009, 07:42 PM

Thanks shg.
What I had initially in those cells were two dots. Now, I deleted them and personalized the cell format to ;;; (I read that this action means that excel starts actually treating it as an empty cell). Do I have to change smth in the settings then?

**shg** · 10-26-2009, 07:45 PM

In A1 enter 1 and format as ;;;

In B1, enter =A1 and format as General.

Does Excel think A1 is empty?

**kazim** · 10-26-2009, 07:55 PM

Originally Posted by shg

In A1 enter 1 and format as ;;;

In B1, enter =A1 and format as General.

Does Excel think A1 is empty?

Oops. No, it doesn't. So, it's just good to hide the cell content.
But my cells are empty, though. Why does it treat them as zeros? Is there something I missed?

**shg** · 10-26-2009, 08:01 PM

But my cells are empty, though. Why does it treat them as zeros?

What cells are empty? Excel offers options of plotting as zero, not plotting (leaving a gap), or interpolating between the adjacent points.

**kazim** · 10-26-2009, 08:09 PM

Sorry if I was not clear enough. I actually put in my first post.

- Empty cells are set to be plotted as gaps.

So I did check those settings, and the current option is the "gap" option. Still, the empty cells are plotted as zeros. That's where I'm stuck.

**shg** · 10-26-2009, 08:11 PM

I think the cells are not really empty, as in contain NOTHING.

**kazim** · 10-26-2009, 08:18 PM

Originally Posted by shg

I think the cells are not really empty, as in contain NOTHING.

Wow! I feel really stupid.. You were right: they actually contained one tiny little space.
That's the power of discussing things. You really helped me out.Thanks a lot, shg!