Excel: How to Hide Formulas from Other Users

To prevent other users from seeing or modifying your formulas, format the relevant cells as hidden, and then protect the worksheet or worksheets. Use the following tech-recipe to accomplish this.


1. Select the cell or range of cells that contain the formulas.
2. Choose FormatCells to display the Format Cells dialog box. (Alternatively, use Ctrl+1 on the keyboard.)
3. Select the Hidden and Locked check boxes on the Protection tab.
4. Click OK.
5. Choose ToolsProtectionProtect Sheet to display the Protect Sheet dialog box.
6. Make sure that the Protect Worksheet and Contents of Locked Cells check box is selected.
7. Type the password for protecting the worksheet in the Password to Unprotect Sheet text box.
8. Click OK. Excel will display the Confirm Password dialog box.
9. Type the password again, and then click OK.

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

  • Anil

    Hi,

    I tried this option and it is not working in Excel 2007.
    The formula is still visible even after using this option “Select the Hidden and Locked check boxes on the Protection tab.”.

  • Anonymous

    Hi It was working well.

    I have tested the same…

  • RIPON SARKAR

    i have followed the process to hide formula in excel but i want to carry on the work in sheet without protection by not viewing the formula in the cell in which i have given formula

    • Anonymous

      Hi Ripon Sarkar ,
      Read your problem, just a quick question did you sort it because i want to know how to do it.

      Mark

      • Jimit

        >firstly you need to select the entire work book and uncheck the locked cell check box. then you must select the cells where you wish to hide the formula and for those cell only go to Format Cells–> protection and check the hidden and locked check box. now go to review and select protect workbook.

        now only your formula cell is protected and hidden while all other cells are workable even if your work book is protected.

        this is because when you protect the work book all locked cells are protected and you cannot edit them. By default in excel all cells are locked. hence you must unlock all the cells you wish to edit before you protect the workbook.

        hope this helps.

        • Arniespec

          >this is awesome, I followed it step by step and I got what i wanted. thanks

        • Shamim

          > tHANS A LOT . i GOT MY POINT.

        • maneesh

          > great boss it worked and helped me. Thanks alot.

  • Anonymous

    formula is working. It is very useful for me for calculating formulas

  • Anonymous

    1. Select the cell or range of cells that contain the formulas.2. Choose Format – Cells (or use Ctrl+1 on the keyboard) to display the Format Cells dialog box.2A, Select the font tab & fomat all cell range as white then3. Select the Hidden and Locked check boxes on the Protection tab.4. Click the OK.5. Choose Tools – Protection – Protect Sheet to display the Protect Sheet dialog box.6. Make sure that the Protect Worksheet and Contents of Locked Cells check box is selected.7. Type the password for protecting the worksheet in the Password to Unprotect Sheet text box.8. Click the OK. Excel will display the Confirm Password dialog box.9. Type the password again and then click OK.

    • Kingsley

      Hi, the above one works good, But i need the formulas to be hidden without protecting the sheet and not using codings, Can u help me out>

  • Anil

    This is fine but want to know if at all there is any option where we can hide the formula and the sheet can be used without protecting it. Here in this case the sheet is protected and no one can use it unless it is unprotected.

    Please advise……!

    • Anil

      Anil,
      The premise of the attributes “locked” & “Hidden” are only effective if the worksheet is protected. You can have individual attributes or both checked on & off.

      With the “hidden” checked and “locked” unchecked, you can protect the worksheet which results in the formula being hidden but if you wish you can edit it, though you cant see what you’re editing.

      The “protect sheet” icon is located on the “Review” tab in Office 2007.

  • Rahul_dyalbagh

    hi its rahul i wanna know how i can hide my formula from othe user by without protecting sheet.

  • Anu

    Nice one … thanks very much…

    Anu

  • yubraj

    How to hide all sheet in excel and how to hide formula for other user? plz reply with good answer.. Thanks.

    • Chandra Mouli

      Open the work book and use shortcut ALT+F11, click one sheet and go to the properties, it will reflects in the top of the menu(Short cut F4), in the properties, the last colomn will be the Visible, beside the visible select the dropdown as 2(very protect) and save. the work sheet will be hidden from the work book.

  • Parcel_14

    it working well. Thanks

  • Chandra Mouli

    we can hide the formula from the work sheet, but after hiding the formula i am unable to edit the entire work sheet.

  • Hashim Bd

    Hi,
    Its working, but after hiding the formula i can not edit my data. Plz help me hiding only formula (data should be editable)

    Thanks

    Hashim

  • Karthikeyan

    You can do so by selecting the locked cells off and only leaving hidden option enabled. this way you will be able to edit ur cells and the formulas won’t be visible also..!!

  • Sanjeev

    Hello
    I want hide foumula in a cell show only calculated value. after doing procted worksheet nobody can do the work. I want anybody can do work but can’t see fourmula

  • YOGENDRA SHEKHAWAT

    HI,
    THANKS DEAR ITS WORKING WELL.

    THANKS A LOT.

  • Denny George Skaria

    Alright, the question is to hide the formula in a cell/row/column as well as work normally in rest of the sheet. I tried a few VBAs but it wont be of much help if the macros are disabled. I’d to face a similar situation and this is what I did. Hope it helps.

    SOLUTION:
    Before protecting the sheet with Password-1, use “Allow Users to Edit Ranges” with Password-2 on rest of the cells where formula needn’t be hidden.

    Password-2 can be shared with the end user, while Password-1 still hides the formulas in selected cells.

    You can also leave blank the Password-2, if needed.

    Did it help??

    Denny George Skaria

  • siva kumar

    sir I made a formula in cells in excel 2007 but no one cant change the formula how to do that. please tell me the solution

  • Saga

    thanks dear its work well in my excel document

  • Ashraff

    Hi,

    I need protect only a column not whole work sheed for an example

    MaterialNM Stock Issued Balnce
    ~~~~~~~~~~ ~~~~~ ~~~~~~ ~~~~~~
    Pen 200 45 =200-45

    I need to so only the blance 155 and not to display =200-45. I use the formula from d4:d200 and apply the formula. The user will feed only the issued figure.

    if I use this method to hide the formula and protect sheet the user cant update the “Issued” column.

    My target for thi hide is accurasy.

    Pl help me

    Thanks

  • Habib, Bangladesh

    Hi.
    It WORKS!!!! tHANK yOU

  • Cheers!

    I have the same issues and tried to follow all other options to hide the cell value of information without affecting the formula. I just don’t want to hide and have a clean worksheet when filling up a worksheet. Can u please advise. thanks.

  • Cheers!

    I need help on hiding the cell information/value but not the formula. I tried few options but it’s still shows the information. For example:

    01/1900 with formula as =MINA(E11:E1000), this is formatted as mm/yyyy. but I need to hide this so the worksheet is clear and we can enter new data on it without affecting the formula. Please help. Cheers!