How to Hide Formula in Excel

It happens sometimes you send Excel files to others and they made some changes into formulas

Or, sometimes you simply don’t want to show a formula to others.

Hiding a formula is a simple way to do this so that others can’t able to see which formula you have used and how they get calculated.

Now the question is, how can we hide a formula in a cell in Excel.

But before we do that let’s be clear on this point that there two way to see a formula from a cell, first, by editing a cell, and second, from the formula bar.

Is there any way which you can use for preventing both of the things? Yes, it’s there.

In Excel, there is a simple way for this and today, I’d like to share with you this simple trick.

Steps to follow to Hide a Formula

To hide formulas follow these simple steps:

Step1: Un-Protect Worksheet

At this point when you click on a cell, it shows you the entire formula in the formula bar.

  • First of all, you need to make sure that your worksheet in unprotected.
  • For this, go to review tab and in protect group.
  • If you find a “Unprotect Worksheet” button there, that means your worksheet is protected and you need to unprotect it.
  • Click on that button to un protect it.

If you have “Protect Sheet” button there that means your worksheet is unprotected. In that case, skip above steps and start from here.

Step2: Activate Hide Formula Option

  • Now, select the cell or range of cell, or select all the non-contiguous cells where you want to hide formulas.
  • After that, right-click and select format cells option.
  • From format cells option, go to protection tab.
  • In protection tab, you have two check boxes to mark, first locked and second is hidden.
  • Mark on both of them and click OK.

These both options which you have checked solve your both of the problems which we have discussed above.

Locked option restricts editing of a cell and hidden option hides a formula from the formula bar.

Now the final step is to protect the worksheet.

Step3: Protect Worksheet

  • Right click on worksheet tab and then click on “Protect Sheet”.
  • Here you need to enter a password. So enter a password (Twice).
  • And in the end, click OK.

Now all the cell which you had selected got formula hidden from the formula bar.

If you send this file to someone they will not able to see the formula or edit it unless this worksheet is unprotected.

How do I find hidden formulas?

Once you protect a file using above steps even you can’t see formula until you unprotect worksheet.

But if you just want to find them from a protected worksheet where they are, you can use “Go To Special” option.

  • Go to Home Tab -> Editing -> Find & Select -> Go to Special.
  • Select “Formulas” option and it’s all four sub option (Numbers, Text, Logicals, Errors) and click OK.
  • Once you click OK, it Excel will select all the cell from worksheet where you have formulas.

Hide a Formula without Protecting a Worksheet

I'm sure you have this thought in your mind that how can I hide a formula without protecting a worksheet. I'm afraid there is no other method for this. You need to go this way. The only possible thing which I know is to create a macro code which runs at the opening of the workbook and hide formula bar. BUt, again this is not a permanent solution. 

Conclusion

This option not only helps you to hide formula but also helps if you have any crucial calculation which you want to hide from others.

Until someone has the password, formulas are hidden and safe. I hope you this tip will help you to manage your worksheets.

Have you ever tried to hide a formula before? Please share your views with me in the comment section, I’d love to hear from you.

And, make sure to share this tip with your friends.

Content Protection by DMCA.com
2018-11-16T06:13:27+00:00

14 Comments

  1. TILOTTAMA 4 Dec, 18 at 5:06 am - Reply

    Hey, Nice Clue..

  2. Michel 21 Aug, 18 at 7:35 pm - Reply

    Format cell with ;;;

  3. ARIF CHIMANWALA 20 Aug, 18 at 4:44 pm - Reply

    Very useful article.
    Thanks.

  4. Nitai Chandra Mahato 13 Aug, 18 at 4:30 pm - Reply

    Very useful

  5. Krupesh Daiya 17 Jan, 18 at 1:53 pm - Reply

    Very useful tip.

    I had wanted to hide formulas in excel but didn’t know how. I had given up on this.

    • Puneet Gogia 17 Jan, 18 at 7:32 pm - Reply

      I’m glad you liked it.

  6. Sarfraz Ali 11 Jan, 18 at 6:36 am - Reply

    Even if you hide, Ctrl+Tilde will show the formulas 🙂

    • Puneet Gogia 17 Jan, 18 at 7:32 pm - Reply

      Yup. 🙂

  7. Raman Girdhar 6 Sep, 17 at 4:50 am - Reply

    That’s nice. However, what if we want to hide formula in a particular column of an Excel Table whose range may increase with new rows being added in the table. Is there a way to do so ?

    • Puneet Gogia 6 Sep, 17 at 5:03 am - Reply

      The problem comes up when we protect our worksheet. In that case, we can’t able to expand our table automatically.

  8. SANDEEP BHARGAVA 31 Aug, 17 at 4:26 pm - Reply

    Yes I normally use this formula hiding option in almost all of my sheets from last 5-6 years. It is quite useful feature the only issue is to remember so many passwords.
    If their is any way to decode the password if forgotten?

    • Puneet Gogia 1 Sep, 17 at 8:07 am - Reply

      There is a way but bit messy, try to write about it.

      • Shoban Babu Medam 2 Sep, 17 at 4:34 pm - Reply

        we can use workshert selection change code….
        if target afdress $A$65535 then
        application.formulabar.display = false
        note: means if user selects a cell rather than
        a65535 the formula bar will be hidden.
        i think this will help…..
        waiting for update.

        • Puneet Gogia 6 Sep, 17 at 5:04 am - Reply

          Thanks for the update.

Leave A Comment