Hide and protect formulas

If you don’t want others to see or change your formulas, you can hide and protect them from being viewed in worksheet cells and in the formula bar at the top of the worksheet.

Hide and protect formulas

  1. Select the entire worksheet by clicking the Select All button in the upper left-hand corner of the worksheet.

    Select All button in the upper-left corner of an Excel worksheet

  2. Right-click any cell in the worksheet, and then select Format Cells.

  3. Click the Protection tab, clear the Locked check box, and then click OK.

  4. Select the range of cells with formulas that you want to hide.
    Press Ctrl to select nonadjacent ranges.

  5. Right-click the selected cells, and then select Format Cells.

  6. Click the Protection tab, select the Locked and Hidden check boxes, and then click OK.

  7. On the Review tab, click Protect Sheet.

  8. Make sure the Protect worksheet and contents of locked cells check box is selected.

    At this point you have the option to enter a password. If you don’t, anyone can unprotect the worksheet by clicking Unprotect Sheet on the Review tab. If you create a password, you’ll be asked for that password when you want to edit a formula.

    Warning:  If you lose the password, you will not be able to edit protected formulas. Store the password in a secure location.

You can protect data used in formulas the same way; for example, if you don’t want anyone to change data that would then change your formula results. If it’s okay for formula results to change, don’t protect those data cells.

Add new formulas that you want to protect

  1. On the Review tab, click Unprotect Sheet.

  2. If you created a password, enter the password when prompted.

  3. Select the new range of cells with formulas you want to hide (and data if you want to protect that as well).

  4. Right-click the selected cells, and then select Format Cells.

  5. Click the Protection tab, check the Locked and Hidden check boxes, and then click OK.

  6. On the Review tab, click Protect Sheet, and enter your password if you have one.

Turn off protection and unhide formulas

After you do this, unprotected cells can be edited. You may want to make a backup copy of the workbook before you go on.

  1. On the Review tab, click Unprotect Sheet.

  2. If you created a password, enter the password when prompted.

  3. Select the range of cells with formulas you want to unhide (and cells used in the formulas if you hid those cells as well).

  4. Right-click the range of cells, and then click Format Cells.

  5. On the Protection tab, clear the Hidden check box, and then click OK.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×