XL2000: Cells Made Blank with Quotation Marks Act As Text Cells (214103)

SUMMARY

In Microsoft Excel 2000, a cell that is blank because it contains a function or a formula that returns double quotation marks ("") may behave differently from a cell that contains nothing. This difference in behavior occurs because the quotation marks are treated as text.

If you copy a cell that contains a formula that returns empty double quotation marks, and you paste the value of that cell to another cell, the destination cell behaves the same as the cell that contains the formula, even though the destination cell appears empty. The destination cell behaves the same as the formula cell because the destination cell contains an empty text value (therefore, the cell is not really empty). To clear the cell (and make it completely empty), select it and click Clear on the Edit menu.

MORE INFORMATION

In Microsoft Excel, a common method to create a cell that appears to be blank is to use a formula that returns empty double quotation marks, as in the following example:

B1: =IF(A1>0,A1,"")

NOTE: In this formula, there is no space between the quotation marks.

In the preceding formula, if the value in cell A1 is zero or less than zero, cell B1 appears blank. If the value in cell A1 is greater than zero, cell B1 contains the same value as cell A1.

When you sort cells containing formulas that return empty double quotation marks, they are placed above blank cells (Excel treats these cells as if they contain text).

NOTE: These cells are placed below blank cells if you are sorting in descending order.

When you use cells containing formulas that return empty double quotation marks in the plot range for a chart, the result for the point values of those cells is zero. For example, a line chart shows a continuous line going from the previous point to zero and then to the next point. Blank cells, however, result in a break in the line rather than a zero value.

Note that when you plot a cell that contains a #N/A error value, the value is interpolated on a line chart. You can use the following example to return a #N/A error value in a cell:

B1: =IF(A1>0,A1,NA())

If you create an xy (scatter) chart and use the formula =NA() in a cell, it still plots the data points, and the line is still connected on the xy (scatter) chart, even though the Do Not Plot option is selected. (To select this option, on the Tools menu, click Options, and on the Chart tab, click Do Not Plot.) However, if you delete the contents of that same cell, it is not plotted on the xy (scatter) chart.