Is it possible to insert a TRUE blank into a cell?

**EricLindros** · 03-12-2010, 01:26 PM

Hello,

After searching I couldn't find a solution to this problem.

"" or " " is not treated as a true blank by excel. It is in terms of formulas like Average(), but not when graphing. "" or " " is interpreted as a 0 data point on a graph.

#N/A is ignored by graphs, but will break formulas.

The only way to have a cell interpreted correctly in terms of both graphing and formulas, is to have it as a true blank, ie an unmodified cell. Is there any way to set a cell to this state?

**Paul** · 03-12-2010, 01:42 PM

Hi Eric, welcome to the forum.

You can't enter a true blank using a formula, so your options are limited to selecting the cell and pressing Delete, or using VBA to clear the cell's contents, e.g.

Code:

Range("A1").ClearContents

**EricLindros** · 03-12-2010, 02:42 PM

This is a big oversight by Microsoft.

This means there is no way to graph and run simple formula calculations on the same data set without misrepresenting empty fields. For my application there is a big difference between no data, and zero.

The only way I can see to do what I need to is very inefficient, Duplicating the whole data set and modifying each one differently. One for graphing with #N/A replacements and one for statistics with "" replacements.

**shg** · 03-12-2010, 02:47 PM

You could autofilter to hide the rows you don't want plotted.

**EricLindros** · 03-12-2010, 03:35 PM

Originally Posted by shg

You could autofilter to hide the rows you don't want plotted.

This doesn't work. If you graph a set where you autofilter out the unwanted values that whole category of points is omitted from all the data sets you are comparing in the graph.

Example:

____________set1_____ set2
first field ______10_____ X
second field ___20 _____5
third field ___ ___X _____13
fourth field _____34_____ 7
fifth field ___ ___X ___ ___X
average ______21.3 ____8.3

If I autofilter out all the X's, then on a graph I only get the second field, fourth field and average plotted because every row with an X is hidden.

I want the output given by graphing

______________set1___ set2
first field ________10
second field _____20 ___5
third field ____________ 13
fourth field ______34 ____7
fifth field
Average ______21.3 ____8.3

Ie. I want the graph to show that for the first field, set1 has a value of 10 while there was no datapoint for set2. With autofilter, it just wipes the whole first, third, and fifth fields off of the graph.

Edit: I tried to make that more legible with underscores.