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.

28 Responses to “Excel: How to Hide Formulas from Other Users”

  1. January 14, 2010 at 8:11 pm, Anil said:

    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.”.

    Reply

  2. February 15, 2010 at 6:58 am, Anonymous said:

    Hi It was working well.

    I have tested the same…

    Reply

  3. February 18, 2010 at 8:09 am, RIPON SARKAR said:

    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

    Reply

    • March 04, 2010 at 7:07 pm, Anonymous said:

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

      Mark

      Reply

      • November 07, 2013 at 12:31 am, Jimit said:

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

        Reply

        • December 10, 2013 at 5:52 am, Arniespec said:

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

          Reply

        • January 20, 2014 at 12:54 pm, Shamim said:

          > tHANS A LOT . i GOT MY POINT.

          Reply

        • January 21, 2014 at 7:08 am, maneesh said:

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

          Reply

  4. February 24, 2010 at 9:58 am, Anonymous said:

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

    Reply

  5. April 17, 2010 at 8:58 am, Anonymous said:

    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.

    Reply

    • February 23, 2014 at 1:23 am, Kingsley said:

      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>

      Reply

  6. April 18, 2010 at 3:37 pm, Anil said:

    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……!

    Reply

    • April 21, 2010 at 7:32 am, Anil said:

      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.

      Reply

  7. June 26, 2010 at 10:50 am, Rahul_dyalbagh said:

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

    Reply

  8. December 08, 2010 at 10:51 am, Anu said:

    Nice one … thanks very much…

    Anu

    Reply

  9. December 21, 2010 at 6:20 am, yubraj said:

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

    Reply

    • March 10, 2011 at 5:00 am, Chandra Mouli said:

      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.

      Reply

  10. January 26, 2011 at 9:34 am, Parcel_14 said:

    it working well. Thanks

    Reply

  11. March 10, 2011 at 5:02 am, Chandra Mouli said:

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

    Reply

  12. March 25, 2011 at 3:52 pm, Hashim Bd said:

    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

    Reply

  13. April 05, 2011 at 9:47 am, Karthikeyan said:

    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..!!

    Reply

  14. May 11, 2012 at 3:55 pm, Sanjeev said:

    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

    Reply

  15. June 09, 2012 at 10:48 am, YOGENDRA SHEKHAWAT said:

    HI,
    THANKS DEAR ITS WORKING WELL.

    THANKS A LOT.

    Reply

  16. September 22, 2012 at 8:14 am, Denny George Skaria said:

    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

    Reply

  17. November 14, 2012 at 8:49 am, siva kumar said:

    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

    Reply

  18. December 04, 2012 at 1:22 am, Saga said:

    thanks dear its work well in my excel document

    Reply

  19. February 12, 2013 at 1:52 am, Ashraff said:

    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

    Reply

  20. March 25, 2014 at 12:23 am, Habib, Bangladesh said:

    Hi.
    It WORKS!!!! tHANK yOU

    Reply

Leave a Reply