How to Hide Formula in Excel

Last Updated: March 24, 2024
puneet-gogia-excel-champs

- Written by Puneet

It happens sometimes you send Excel files to others and they made some changes to formulas. Or, sometimes you simply don’t want to show the 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 the formula in a cell in Excel?

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

Is there any way you can use to prevent both 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.

Step 1: 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 is unprotected.
  • For this, go to the review tab and protect the group.
  • If you find an “Unprotect Worksheet” button there, that means your worksheet is protected and you need to unprotect it.
  • Click on that button to unprotect it.

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

Step 2: Activate the Hide Formula Option

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

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

The locked option restricts the editing of a cell and the hidden option hides a formula from the formula bar. Now the final step is to protect the worksheet.

Step 3: Protect Worksheet

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

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

If you send this file to someone they will not be 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 the above steps even you can’t see the formula until you unprotect the worksheet. But if you just want to find them from a protected worksheet where they are, you can use the “Go To Special” option.

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

Hide a Formula without Protecting a Worksheet: I’m sure you have this thought in your mind 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 that runs at the opening of the workbook and hides the formula bar. But, again this is not a permanent solution. 

Conclusion

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

Until someone has the password, formulas are hidden and safe. I hope 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.

29 thoughts on “How to Hide Formula in Excel”

  1. Very Good Tutorial
    I want more Tutorials on Excel Macros And VBA
    through my e-mail address
    thank you

    Reply
  2. This is really helpful and although I’ve been asking myself this question for a number of years have never realised that such a straight-forward solution was available. Thanks again – you are brilliant!

    Reply
  3. Hi Puneet,

    Thanks a lot for this trick, i just learn this from you, i haven’t done before.

    Once again, thanks.

    Henry

    Reply
  4. This is wonderful!
    Please give the macro / VBA code which runs at the opening of the workbook and hide formula bar.

    Reply
  5. I send out many reports. I set it up to run a macro to copy > paste special > values and save as file name + date before it is attached to the email. That way I can always find the issue date when someone has a question and the formulas are no longer a problem.

    Reply
  6. Very useful tip.

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

    Reply
      • Michael,
        ;;; have 2 issues
        1. It will hide the text in the cell then what the person will read to whom report has been sent.
        2. If the person whom report is sent knows this trick he will be able to read your formula and can change it too

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

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

      Reply
  8. 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?

    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.

        Reply
          • Michael,
            ;;; have 2 issues
            1. It will hide the text in the cell then what the person will read to whom report has been sent.
            2. If the person whom report is sent knows this trick he will be able to read your formula and can change it too

        • This code doesn’t work.
          tried this also but didn’t work:
          Application.FormulaBarHeight = 0
          or
          Application.FormulaBarHeight = false

          Reply

Leave a Comment