Cells Appear Blank but aren't

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

Why do the cells in Excel sometimes appear blank but in the function line it
shows that it has info in it?
 
Hi,

Maybe the cell has a formula that's evaluating as a null string. For example

=if(a1="","",A1)

this will show as empty until you enter a value in a1

Mike
 
Hi,

Maybe the cell has a formula that's evaluating as a null string. For example

=if(a1="","",A1)

this will show as empty until you enter a value in a1

Mike
 
Jennifer said:
Why do the cells in Excel sometimes appear blank but in the function line it
shows that it has info in it?
Click to expand...

Simplest way: type 1 or more spaces into a cell. If there doesn't
appear to be even spaces in a cell, but Excel treats the cell as
though it contains something, this can happen when cells with formulas
evaluating to "" are pasted as values into other cells.

You could use conditional formatting to visually identify cells
containing spaces or zero-length strings using the condition formula

=AND(ISTEXT(A1),LEN(TRIM(SUBSTITUTE(A1,CHAR(160),""))=0)

and setting the background color to something unusual.
 
Jennifer said:
Why do the cells in Excel sometimes appear blank but in the function line it
shows that it has info in it?
Click to expand...

Simplest way: type 1 or more spaces into a cell. If there doesn't
appear to be even spaces in a cell, but Excel treats the cell as
though it contains something, this can happen when cells with formulas
evaluating to "" are pasted as values into other cells.

You could use conditional formatting to visually identify cells
containing spaces or zero-length strings using the condition formula

=AND(ISTEXT(A1),LEN(TRIM(SUBSTITUTE(A1,CHAR(160),""))=0)

and setting the background color to something unusual.
 
What type of info is in the formula bar?

Maybe a formula that returns blank if a condition is not met?

=IF(A1<>12,"",A1) would look blank if A1 was not 12

Or possibly the cells have been custom formatted with 3 semi-colons ;;;
which will leave cells looking blank.


Gord Dibben MS Excel MVP
 
What type of info is in the formula bar?

Maybe a formula that returns blank if a condition is not met?

=IF(A1<>12,"",A1) would look blank if A1 was not 12

Or possibly the cells have been custom formatted with 3 semi-colons ;;;
which will leave cells looking blank.


Gord Dibben MS Excel MVP
 
Hi.

And it could be that the text color is simply the same color as the
background. In a new worksheet, type anything in cell A1. Then format
the text color to white. It will appear empty.

Regards,
Justin
 
Hi.

And it could be that the text color is simply the same color as the
background. In a new worksheet, type anything in cell A1. Then format
the text color to white. It will appear empty.

Regards,
Justin
 
Back
Top