This is a short article. We know how to hide rows, columns and sheets. Did you know you can hide cell contents as well in Excel?
When we want to hide cell contents, the obvious thought is to change the font color to white. It works, for some time. But the moment you select the cell, it will show the value as the background color changes. So here is the right way.
Go to Format Cells and Custom and type three semicolons ;;;
That’s it. Now the font color or background color does not affect it. The contents are just not visible.
Contents
Why would you want to hide cell contents?
Many reasons. Hide some interim calculations. Hide the corner cell in a data table. Hide some data purely for aesthetic reasons. Usually there is a connotation of Protecting the cell as well.
Will the formula bar show the value?
Yes. Formula bar will continue to show the value. If you want, you can hide the formula bar. But anyone can enable it. To prevent that from happening, you will have to protect the workbook. We will learn about the protection features in a separate article.
How does this work?
The answer is a little complex. But I am sure it will help you in more than one ways. In an Excel cell, you can have either a number or text. Numbers can be positive, negative or zero. So basically, four possibilities.
A cell does not know what is going to be typed. Nor do you. But you may want to format the content differently depending upon whether the value is positive, negative, zero or text. That is why, Excel allows you to specify formatting options for all the possibilities in the Custom textbox.
Now, you type the formatting options for positive number, and then want to type it for negative number, it needs a separator. That separator is the semicolon. Because we have four possible value types, we need three semicolons.
Here is a custom format which shows each type differently.
Now, if we do not want to show anything for all the four variations, we must type the three semicolons. That is the only way in which Excel understands that for EACH OF THEM you do not want to display anything. That is why three semicolons work and hide the cell contents.
Try it out. There is much more to formatting numbers, dates and text in Excel. We will cover that in another article.
2 Responses
Dr Paranjape, I would like to thank you for sharing all this knowledge with us. I am regular reader of your blog. Just would like to highlight that for the above to work, you would need to enter only three semi-colons and not four.
My intent is just to make sure your blog is helpful to others instead of struggling with 4 semi-colons.
Hi
Thank you for pointing out the mistake. Apologies for the inconvenience. I have already corrected the error in the blog.
Dr. Nitin