Tame your computer - fail-safe your formulas

By default all cells in a worksheet can be modified by anyone who has access to that file.

If you want to prevent other users from changing, moving, or deleting formulas you can make those cells read-only. Other data in the sheet can still be changed.

Here’s how:

1. Select all cells in the worksheet by clicking on the top left corner of the sheet. (Or click in any empty area and press CTRL + A.)
2. Right-click anywhere in the sheet and click on Format Cells (or use CTRL+1).
3. On the Protection tab untick Locked and Hidden.
4. Click OK or press ENTER.
5. On the Home tab, in the Editing group, click on the Find & Select arrow and click on Formulas.
6. Right-click any of the selected cells and click on Format Cells (or use CTRL+1).
7. On the Protection tab tick Locked and Hidden.
8. Click OK or press ENTER.
9.  On the Review tab, click Protect Sheet.
10. Enter a password in the Password to unprotect sheet box and click OK. Re-enter the password in the Confirm Password dialog box.
11. Click OK or press ENTER.

Step 10 is optional, but if you do not set a password, anybody can remove the protection by simply clicking on the Unprotect Sheet button on the Review tab.  The warning message that pops up even tells you the way to unprotect the sheet.

* Unless stated otherwise, these tips were written for Microsoft Office 2010.

31 July 2017

Karen Roem offers software training and support through her company Roem Ltd.  Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk