How to lock and hide formulas in Excel

This tutorial explains how to conceal Excel formulas from the formula bar and protect them from unauthorized changes. Learn to lock individual or all formulas, ensuring data confidentiality and integrity.

Microsoft Excel simplifies formula interpretation. Selecting a formula cell displays it in the formula bar. For detailed analysis, use the Formulas tab > Formula Auditing > Evaluate Formula.

However, for security or confidentiality, you might need to hide formulas. This tutorial details how to hide and lock formulas, protecting them from deletion or alteration, particularly useful when sharing sensitive reports.

- Locking Excel Formulas

• Hiding Excel Formulas
• Removing Protection and Unhiding Formulas

Locking Formulas in Excel

Protecting your work from unintended modifications is crucial. While worksheet protection locks all cells, this tutorial shows how to selectively lock formulas while leaving other cells editable.

1. Unlock All Cells

First, unlock all worksheet cells. By default, the Locked option is enabled for all cells. This doesn't prevent editing until the worksheet is protected. To lock only formula cells, unlock all cells first. Skip this step if you want to lock all cells.

• Select the entire worksheet (Ctrl A or click the Select All button).
• Open Format Cells (Ctrl 1 or right-click > Format Cells).
• On the Protection tab, uncheck Locked, then click OK.

2. Select Formulas to Lock

Select the cells containing the formulas you wish to protect. Use Ctrl to select non-adjacent cells or ranges. To select all formula cells:

• Go to Home > Editing > Find & Select > Go To Special.
• In the Go To Special dialog, select Formulas and click OK.

3. Lock Selected Cells

Lock the selected formula cells. Open Format Cells (Ctrl 1), go to the Protection tab, and check Locked. This prevents modification.

4. Protect the Worksheet

The Locked attribute only takes effect after worksheet protection.

• Go to Review > Changes > Protect Sheet.
• Enter a password (remember it!). Select allowed actions (e.g., selecting locked/unlocked cells).
• Click OK, then re-enter the password for confirmation.

Your formulas are now locked and protected, though visible in the formula bar.

Tip: For easier updates, consider placing formulas on a hidden sheet and referencing them in your main sheet.

Hiding Formulas in Excel

Hiding formulas prevents their display in the formula bar.

1. Select the cells with the formulas to hide (use Ctrl for non-adjacent cells or Ctrl A for the entire sheet; use Go To Special > Formulas for all formula cells).
2. Open Format Cells (Ctrl 1, right-click > Format Cells, or Home > Cells > Format > Format Cells).
3. On the Protection tab, check Hidden. Locked is usually left checked.
4. Click OK.
5. Protect the worksheet.

Remember: Hiding and locking only take effect after worksheet protection.

Removing Protection and Unhiding Formulas

To unhide formulas:

• Home > Cells > Format > Unprotect Sheet, or Review > Changes > Unprotect Sheet.
• Enter the password.

If formulas were hidden before protection, uncheck Hidden in Format Cells if you want to keep them visible after future protection.

This concludes the guide on hiding and locking Excel formulas. Stay tuned for our next tutorial on copying formulas!

The above is the detailed content of How to lock and hide formulas in Excel. For more information, please follow other related articles on the PHP Chinese website!