Hide and Seek – Cell Content

Don’t be shy!  Let silly things continue!  Let’s Hide and Seek with #Excel again! 🙂

Case 1

Excel Tips - Hide and Seek Cell Content

Case 2

Excel Tips - Hide and Seek Cell Content1

Case 3

Excel Tips - Hide and Seek Cell Content3

You may download a Sample File – Hide and Seek Cell Content for the above cases.

If you know the answers for all the three cases above, I believe you are good in using Excel.  If you don’t know all of the above, you may want to continue.

Case 1 – The most common scenario

The text is always there… just that the font are formatted in the same color as cell fill color. Well, it’s true that a normal person does not see white on white.  Excel Humor #9 – You do not see me

Excel Tips - Hide and Seek Cell Content

Tip: The “disappeared” middle gridlines from D3 to I3 is a good indicator for this scenario.

Solution

To make the cell contents visible to normal person, simply change the font color:

Excel Tips - Hide and Seek Cell Content1.gif

 

Case 2 – A very special custom format to hide cell content

Excel Tips - Hide and Seek Cell Content1

Similar to the “hidden” content in D3, but in this case the font color is Black.  Also, the middle gridlines from D5 to I5 are there.  Does it mean that cell D5 is really empty? Should not be… as we can see the content in the formula bar.

Strange enough?  It is due to Custom Format.

Solution

Simply change the cell format to “General

Excel Tips - Hide and Seek Cell Content2.gif

Curious about the Custom Format that hides cell content?

Here you go:

Format Cells –> Number Tab –> Custom (under Category:) –> Type: Three semi colons as followed:

;;;

Excel Tips - Hide and Seek Cell Content5.PNG

Note: This custom format does not hide #ERROR!.

p.s. I love this trick! 🙂

Case 3 – A reversed case

Excel Tips - Hide and Seek Cell Content3

In this case, we see the content in a cell but not in the formula bar… how come?

Try to click into the formula bar, and you will see the following error message:

Excel Tips - Hide and Seek Cell Content6

The cell or chart you’re trying to change is on a protected sheet.  To make a change, unprotect the sheet.  You might be requested to enter a password.

It is absolutely normal if you do not know you could protect a formula (or cell content) from displaying in formula bar.  To do so, we simply need to

Format Cells –> Protection tab –> Check the “Hidden” box

Excel Tips - Hide and Seek Cell Content7

Note: It takes no effect until you protect the worksheet.

Solution

Obviously, the solution is simple: Reverse the above steps by unprotecting the worksheet (provided that you know the password, or there was no password set.)

Excel Tips - Hide and Seek Cell Content3.gif

 

What if…… we do not know the password?

Easy… Add a new worksheet, or go to a worksheet that is not protected, input the following formula in an empty cell.

=FORMULATEXT(SheetName!D3)
where D3 resides the formula you want to look into.

If #N/A is returned, it means the origin content in the cell is a text string or number as you see it.  It is not a result from formula.

Note: FORMULATEXT function is available in Excel 2013 or later versions.

 

What?  Still using Excel 2003??????

Why not copying D3 and paste on an unprotected sheet?  Isn’t it the simplest way? 🙂

What? You cannot copy D3 as the worksheet is strictly protected in a way that you cannot even select D3????

No worry.  Check this out: Copy data from strictly-protected sheet

Advertisements
This entry was posted in Format and tagged , , , , . Bookmark the permalink.

One Response to Hide and Seek – Cell Content

  1. Wong Ernest says:

    I am thinking to add your post in my website blog, but how to keep it updated on regular basis?

    Best regards, Ernest ________________________________

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s