XL: Cells Made Blank with Quotation Marks Act As Text Cells

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SUMMARY

In Microsoft Excel, a cell that is blank because it contains a function or 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 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 (thus, 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 way 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,"")

In the above formula, if the value in cell A1 is zero or less than zero, cell B1 will appear blank. If the value in cell A1 is greater than zero, cell B1 will contain the same value as cell A1.

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

NOTE: These cells will be 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 an error value, such as #N/A, the value is interpolated on a line chart. You can use the following example to return an error value in a cell:

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