How to Hide Excel Formulas and Protect Your Spreadsheet

Excel makes it easy to decipher why a formula produces its result. When you click on the cell, the formula is displayed in the formula bar. If that’s not enough, you can select the Formulas ribbon and click Evaluate Formulas for a step-by-step walkthrough.

But what happens when you don’t want the formulas displayed? If you are working with a lot of complexity, it can get cluttered or confusing fast. Perhaps you need to hide Excel formulas from recipients and users for proprietary, security, or confidentiality reasons. On occasion I need to send the same spreadsheet to a list of competing vendors. I don’t want them to know how other producers’ rates are calculated.

Luckily, Excel makes it fairly simple to hide formulas. Just follow the steps below.

Unprotect the Sheet

  1. On the Review ribbon, find the button labeled either Protect Sheet or Unprotect Sheet.
  2. If the button is labeled Protect Sheet, then do nothing. The sheet is currently unprotected.
    Fred Pryor Seminars_Excel Hide Formula figure 1
  3. If the button is labeled Unprotect Sheet, then click it. The sheet is now unprotected.

Hide the Formulas

By default, when you click on a cell, its formula appears in the formula bar.
Fred Pryor Seminars_Excel Hide Formula figure 2

To hide formulas:

  1. Select the cells for which you to want to hide the formulas.
  2. Right-click the cell (or cells) and choose Format Cells.
  3. In the Format Cells dialog box, click the Protection tab.
  4. Check the Hidden box.
    Note: Hidden is what prevents the user from seeing the formula. Locked prevents the user from changing the contents of the cell. Locked is set by default.
    Fred Pryor Seminars_Excel Hide Formula figure 3
  5. Click OK.

 

Protect the Sheet

Important Step: Setting the cell format to hidden has no effect until you protect the sheet!

  1. On the Review ribbon, click Protect Sheet.
  2. In the Protect Sheet dialog box, type a password. No one (including yourself) will be able to unprotect the sheet to make changes without typing the password, so do not forget the password!
    Fred Pryor Seminars_Excel Hide Formula figure 4
    Notice that by default the Select locked cells and Select unlocked cells checkboxes are selected. If you select OK now, the user without the password can do nothing but click on cells, whether those cells are locked or unlocked. S/he cannot format, delete, insert, or edit. If you want your users to do any of these operations for unlocked cells, then check them here.
  3. Click OK.
  4. In the Confirm Password dialog box, Excel asks you to retype the password you selected. This prevents a typographical error in the password from locking up your spreadsheet forever. Retype the password and click OK.
    Fred Pryor Seminars_Excel Hide Formula figure 5

That’s it! The formulas in your worksheet are now protected, and your users cannot see or edit the formulas. To verify it, click on a formula:

Fred Pryor Seminars_Excel Hide Formula figure 6

The formula no longer appears in the formula bar.

Next Steps

Try some of the other options to customize the protection properties, such as unlocking only specific input cells. This allows you to send a spreadsheet out to multiple users with an input cell—B1 in this example, where the user would enter his producer code—yet prevent the user from accessing other cells. This is a convenient tool for distributing corporate results to company agents, employees, or others while filtering the values so that each user can see only those for which he is authorized. For step by step instructions visit our blog post: How to Unlock Specific Cells in Excel.

 

Local Seminars Related to this Topic:

Related Formulas Articles

Categories

Monthly Archives

4 Comments

  • asad khan says:

    DEAR SIR,
    HOW TO LOCK SOME CELLS OF THE EXCEL,BY KEEPING OTHER CELLS UNLOCK.

  • Tammy Gibbs says:

    If I choose to protect the sheet with formula, will this prevent the cell from calculating when I enter new values for formula to calculate? I would like to protect the formula from being accidently eased or altered.

    • Excel Tips and Tricks from Pryor.com says:

      When you choose to Protect or Hide a formula, this has no effect on its calculation or performance.
      It behaves just as it would if the sheet were Unprotected except:
      If the cell is Hidden, the user can no longer view the formula, even if the cell is selected.
      (What cannot be seen, cannot be altered either)
      If the cell is Protected, the user must Unprotect the sheet in order to alter the formula.
      (This requires the password you Protected the sheet with)

      If you enter new values in other cells that the formula makes use of, the formula will recalculate its result the moment you hit Enter in the other cells.

Leave a Reply

Your email address will not be published. Required fields are marked *