Protect Excel Worksheets and Lock Cells in Office 2011 for Mac - dummies

Protect Excel Worksheets and Lock Cells in Office 2011 for Mac

By Geetesh Bajaj, James Gordon

When you make forms in Excel 2011 for Mac, you’ll be turning worksheet protection on and off frequently. You must turn off worksheet protection when you want to build a form. Turning off sheet protection enables you to use the form controls on the Developer tab of the Ribbon and allows you to edit worksheet content. When you’re done building the form, you must turn on protection so that people can tab through the input fields.

By default, all cells on a worksheet are locked. You unlock cells that you want to designate as input fields, which are the cells that users fill in. You also unlock the cells that will have the result of a choice made by a user in a form control.

Protecting and unprotecting a worksheet in Excel 2011 for Mac

The default state of a worksheet is that it is unprotected. When you want to create or edit a form, you need to make sure the worksheet is unprotected; if it’s protected, you can’t edit the form. Conversely, when you’re ready to test or use your form, you must turn on protection.

To turn on protection on a worksheet, follow these steps:

  1. From the menu, choose Tools→Protection→Protect Sheet.


  2. (Optional) Type a password to be required to unprotect the form.

  3. In the Allow Users of This Sheet To section, select the first two check boxes, Select Locked Cells and Select Unlocked Cells.

  4. Click OK to close the dialog.

    The worksheet is now protected, and the form is enabled for use. The Protect Sheet button has become an Unprotect Sheet button.

While protection is turned on, the only cells that can be changed are the cells you unlocked. While protection is turned on, pressing the Tab key takes the cursor from one input field (unlocked cell) to the next. This is a very convenient feature for filling in forms.

To turn off protection on a worksheet, from the menu choose Tools→Protect→Unprotect Sheet, or click the Unprotect Sheet button on the MVP Toolbar. Your worksheet is enabled for editing, and the Unprotect button reverts to the Protect Sheet button.

Locking and unlocking a cell in Excel 2011 for Mac

Unlock a cell by selecting it and then press Command-1 to display the Format Cells dialog. Click the Protection tab in the Format Cells dialog and then deselect the Locked option. If you installed the MVP Toolbar, you can simply click the Lock/Unlock Cell toggle button on the toolbar. On the MVP Toolbar, the Lock Cell button acts as status indicator. When it appears as a depressed button, the selected cell(s) is (are) locked. It always indicates the lock state of the current selection.

Alternatively, you can right-click on a form control and choose Format Control from the contextual menu. In the Format Control dialog, click the Protection tab and deselect the Locked option.