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

ID: Q93681

The information in this article applies to:

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,"")

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

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 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 =NA() in a cell, it still will plot the data points, the line is still connected on the xy (scatter) chart, even though the Do Not Plot option button in the Chart tab on the Tools Options menu is selected. However, if you delete the contents of that same cell, it does not plot it on the xy (scatter) chart.

Additional query words: 3.00 2.10 5.00c 97 98 XL98 XL97 XL7 XL5 XL4

Keywords : xlformula
Version : MACINTOSH:4.0,5.0,5.0a,98; WINDOWS:4.0,4.0a,5.0,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type :