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

Posted on June 5th, 2009 in Excel Howtos , Featured , Learn Excel - 41 comments

Here is a very quick tip if you ever want to hide a cell’s contents in excel

Just use the custom cell formatting (more here) code ;;;

How? (see the screencast aside too)
Hide / Make Invisible a Cell's contents in Microsoft Excel - Quick Tip
1. Select the cell you want to hide

2. Hit CTRL+1 (or open format cells dialog from right click)

3. Go to Number tab, select “custom” as the type

4. Type the formatting code as “;;;” without double quotes

5. Press OK and your cell contents are invisible now

What is the use of doing this?

1. This might be handy when you are using conditional formatting to highlight / change cell colors

2. This might be handy when you need to calculate intermediate values, but dont want to display them. (But try to use Hide rows / hide columns feature if you can)

Remember: This formatting code only blanks out the cell contents from being seen. They contents are still there and accessible for formulas, charts as such.

Also know how to display colors in chart data labels using custom cell formatting codes

What is your favorite cell formatting trick?

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

41 Responses to “How to Hide a Cell’s Contents in Excel? [Quick Tip]”

  1. moatasem says:

    Great tip PHD

  2. Radu says:

    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!

  3. Tony Rose says:

    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.

  4. hwsris says:

    thanks Chandoo.

  5. Any idea on how this may work in StarOffice?

    • Chandoo says:

      @Michael: Unfortunately I haven’t used staroffice in a really long time.
      @all.. anyone, any sun fans out there who are using star office?

  6. Michael Pierce says:

    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.

  7. Bob Gannon says:

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

  8. Chandoo says:

    @Bob.. in 2007 you can do this, but in earlier versions of excel this is not allowed in conditional formatting.

    @Michael Pierce: That is a good one.. keeping the alignment right..

    @Tony: that is a fun way to use this…

  9. Gerald Higgins says:

    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.

  10. Chandoo says:

    @Gerald.. you are right… the three sections of the custom format code can be used to define some minimal conditional formatting. For eg. a code like [<500][red]0;[<1000][blue]0;[green]0; would display red, blue and green text based on the value. I have also described an example of it here: http://chandoo.org/wp/2009/01/29/colors-in-excel-chart-labels-trick/

    • Gb4821 says:

      I am trying to build an invoice and would like to hide the pricing until I have a quantity in it so as to not show all pricing all the time. Is there a way to hide the price cells until you enter a value in the formula?

      Thanks for your help,

      Greg Brown

  11. derek says:

    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 :-)

  12. savithri.v says:

    “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

  13. San says:

    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???

  14. Chandoo says:

    @San, are you sure you have typed three ;;; as shown in the pic above? It should accept the format as is. Let me know if you have typed something else.

  15. Ashish Patel says:

    Hi,

    Thanks for the great tip.. I have one problem though.. you can still see the value if when you enter in the cell in the formula bar.
    This is also the case when you protect a worksheet and do not allow selection of locked cells. You can still navigate to the locked cell via the formula bar. Anyway around this ?

    Ashish

  16. Saeed says:

    Thanks for the tip Chandoo.
    Initially, I simply changed the font color to white on a white background to make the cell content quasi invisible; then I came across your tip.
    Somehow, without knowing why!, I think your tip is more elegant and technically better.
    Would you please comment on that? Thanks.
    Saeed

  17. Rose says:

    @ashish, if you lock the specific cells that you do not want to be seen and then protect the worksheet, it will not allow another user to select the cell whose value you want to not be seen. This still allows you to allow others to select the cells which are unlocked while keeping the sensitive values unseen in the cell and formula bar.

  18. Dear Sir,

    I was so keen to learn to hide the cell conmtents but when I use it on my Excel 2003 it does not work. Appreciate if you can help.

    Thank you.

  19. Saif says:

    Dear Sir,

    Is there anyway with which I can hide data from both cells and formula bar???

    Regards,

    Saif

  20. kumaran P says:

    pls help:

    if 47 days working for sales man 5000 salary ..if above 47 days working per day 750 rs every day how to calculate pls help

  21. vaishali says:

    I want to colour entire row based on the change of value of one cell. i.e suppose i want to apply condition that the value in the cell is greater than 0 the entire row should be green, if the value is greater than 5 the entire row should be yellow and so on…
    i have tried conditional formatting — new rule –Excel: Change entire row colors based on value of one of it’s cells? but it is not working… suggest me

    • Hui... says:

      You have to add a CF for each Color/Value you want
      Lets say it is Row 5 based on the value of Cell D1
      Select Row 5
      Goto CF
      Delete All CF for this area

      Goto CF
      New Rule
      Use a Formula
      =$D$1=1
      Set Format
      Apply

      Repeat from the — and add as many rules/formats as there are values / colors

  22. Carlos Magno says:

    Thanks for the tip Chandoo. It helps me in some unusual ways.
    Unfortunately I found that the contents are not accessible for filtering.
    The ideia is using, in a large table, a tiny column, to put hidden text, in order to tagging rows and using colors to distinguish the different tags. But all fails because when I try to grouping the rows by some tag, the text doesn’t appear in the filter options drop box. Any ideia? Thanks for all.

  23. Kate Grosmaire says:

    Thanks, Chandoo.  I like this, but what I am really trying to do is display a portion of the cell data because there are so many characters, e.g. “The Division has requested sampling of the data so that…” and then if you hover or click on the cell, you can see all the characters.  I can use yours with the instructions that cell data can be viewed in the formula bar, but I’d rather have a “hover” solution.

  24. Stephen says:

    Chandoo, This tool doesn’t seem to work if it involves a cell that has a VLOOKUP command in it.  I have CF for cells that are equal to 0, but when they are not equal to 0, the text still doesn’t show.  Is there another way to hide text with cells that have a background pattern?

  25. Stephen says:

    Update: It appears the cell isn’t refreshing.  If I go into the formula bar and hit enter, the text appears, and it updates automatically everytime after that, unless I delete the text in Column A.  I’ve checked the Excel Options to make sure it is calculating automatically.  Can you think of another issue that would cause this?

  26. Firoz says:

    Hello All,

    Can anyone help me to hide in between text…?  

    for example:
    Once again can you post a sample file with an example?
        here i want to hide just “sample file”

    so please help me out…

    Thanks   

  27. JL says:

    This is great but if I need to send to a client, they can still click on the cell with the missing info and find out what the value is. 

  28. micah says:

    What about PARTIAL text within a cell, i only want a small section of data hidden, can this do it?  When i select the text the “format cells” box only gives me the Font formatting, not the custom and one of the options under font formatting is NOT Hidden.
    Comprende? 

  29. Venkatesh Kolluru says:

    Hi I want to know how to hide the formulas in cell,but i want to see the result in the particular cell. if i edit the data relating to that formula in another cell it should be updated in the formula cell also.
    Thank you
    Venki.K

  30. David Jeffery says:

    I is a unayversity students. THANKING OF YOU! life solved, grate help x

  31. Candace says:

    Just curious if anyone knows.. when you put in a formula with the sum of multiple other cells how do you make the cell with the formula appear blank until there is a sum to fill it in (if its dollars rather than having $0.00 to just be blank)???
    Thanks!

  32. […] By default color scales show the values too. To hide them use ;;; custom cell formatting code (how to). […]

  33. Venkatesh says:

    Very Nice….Working

Leave a Reply