How to Hide a Cell’s Contents in Excel? [Quick Tip]

Great tip PHD

Hi, Chandoo. I’ve been reading your posts a fairly long time now, and they’re all fantastic. I see you’re making a habit of saving my (professional) life. ) But seriously, this is a great tip. Thanks for sharing it, and many thanks for all your hard work!

Chandoo – this is a neat little trick. I would typically use the color white so the text/numbers do not show. The downside is that someone can highlight the boxes and see the white text. With this trick that issue is solved.

One use that I can think of and do use is for the values in a drop down box (data validation – list). Now you can set the values, but hide them.

thanks Chandoo.

Any idea on how this may work in StarOffice?

Great tip, I always forget about this and it’s good to have the reminder.

One favorite formatting trick that I have is to right-align numbers when some of them are percentages. Typically, if you had the numbers 6.23% and 7.31 in a column, the decimals won’t align because of the percent sign. The percentages get the standard “0.00%” formatting, but for the other numbers, I use: “0.00_%” and everything aligns at the decimal point nicely.

Great tip. But there doesn’t seem to be any way to do this with conditional formatting. Too bad. You can do white text.

You can’t do it within normal conditional formating.

But you can use some of the conditional functions within custom formats.
For example
[<500]0;;
will display numbers smaller than 500. Anything more than 500 will not display. I worked this out from some custom format instructions on John Peltier’s site.

This method highlights the big weakness of Excel’s custom number formatting for me: its failure to properly handle logicals and errors. Try the following format on a range of types:

“positive”;”negative”;”zero”;”text”

You’ll find that numbers are shown using the “positive etc.” labels, (right aligned); text uses the “text” label, left aligned, naturally; errors do not respond to formatting; and logicals are treated with the “text” label, only center aligned. So I’m stuck with using conditional formatting to paint FALSE values white in my spreadsheets (conditional formatting does not allow you to format numbers, only some text and cell attributes)

However, at least I can create an expression that lets me switch FALSE values from invisible to bright red over the whole sheet, for diagnostic purposes, by typing “Y” in a sngle cell

“This formatting code only blanks out the cell contents from being seen. They contents are still there and accessible for formulas, charts as such”
Felt like sharing with you this…
Recently, I was witness to a presentation which just had lines all over in the graph, with no clue as to whether the numbers represented %s or values. It seems that the presenter had inadvertently missed to format the value axis in the graph to display the labels (which had source data formatted “;;;”)
savithri

Hi Chandoo… I tried this, but i am getting a error mesage saying “Microsoft Office Excel cannot use the number format you typed. Try using one of the built-in formats. For more information about custom number formats, click Help”.

Could you please tell me what is wrong that i have done???