If a worksheet contains cells you do not want changed, you can lock them so that they cannot be edited or deleted. You can also add a password to a sheet or workbook to protect the entire sheet or workbook. If you have columns and rows that you do not want anyone to see or change, you can hide the columns and rows and then display them again whenever you wish. Locking CellsTo set cell protection, perform the steps in the To Do exercise. You want to lock all the cells in your worksheet except for the numbers for March in column D. To Do: Lock Cells
Protecting a WorksheetBy default, cells are locked, but the worksheet protection is not on. You can unlock all the cells you want to change and then turn on worksheet protection. To protect the worksheet, choose Tools, Protection, Protect Sheet. Excel displays the Protect Sheet dialog box, as shown in Figure 14.20. Figure 14.20. The Protect Sheet dialog box.
You can protect the sheet for selecting, formatting, inserting, deleting, sorting, AutoFilter, PivotTable reports , objects, and scenarios. By default, the worksheet and contents of locked cells is protected, along with the act of selecting locked and unlocked cells is protected. A password is optional. Choose OK to protect the worksheet. Now, to test whether the cells are locked, click cell C4, type 950 , and press Enter. Notice that when you try to edit locked cells, an alert box tells you that locked cells cannot be changed. Choose OK to clear the alert box. Excel prevents you from making the change to the cell because the cell is locked and the worksheet is protected. Now try to type a number in cell D4. Can you do it? The answer should be yes. Excel allows you to type in a cell that is not locked. To turn off worksheet protection, choose Tools, Protection, Unprotect sheet. Protecting the WorkbookYou might want to protect the structure of a workbook and the windows . Here are the structure elements you can protect in a workbook:
Here are the windows elements you can protect in a workbook:
You can assign a password (optional) so that users need a password to change the structure of, or windows in, the workbook. To protect a workbook, click Tools in the menu bar and choose Protection, Protect Workbook. Excel opens the Protect Workbook dialog box, as shown in Figure 14.21. Figure 14.21. The Protect Workbook dialog box.
To select the elements you want to protect, click in the Structure check box to protect the worksheets in the workbook. Click in the Windows check box so that the size and position of the windows cannot be changed. If you want, type a password in the Password box, click OK, and type the password again. Click OK. When you want to unprotect the workbook, choose Tools, Protection, Unprotect Workbook. The Unprotect Workbook dialog box opens. Enter the password and then click OK. Your workbook is now unprotected .
Hiding and Displaying Rows and ColumnsA worksheet might contain columns and rows that you don't want to appear on the worksheet. If so, you can easily hide columns and rows with the Hide command or with the mouse. Remember that hidden elements don't print when you print the worksheet. To use the Hide command to hide columns, select the column you want to hide by clicking the column header (the column header contains the column letter). You can hide additional columns by pressing the Ctrl key while clicking each column header. Then choose Format, Column, Hide. If you hide column C, for example, only columns A, B, D, E, and so on are visible (see Figure 14.22). A dark gray border between columns B and D replaces column C in the column header. Figure 14.22. Column C is hidden.
To display hidden columns, highlight the two columns on either side of the hidden column. For instance, if column C is hidden, highlight columns B and D. Choose Format, Column, Unhide. Excel displays the hidden column. Here's how you can use the mouse to hide and unhide columns:
To use the Hide command to hide rows, select the row you want to hide by clicking the row header (the row header contains the row number). After selecting one row, you can select other rows by pressing the Ctrl key while clicking each row header. Then choose Format, Row, Hide. If you hide row 3, for example, only rows 1, 2, 4, 5, and so on are visible. A dark gray border replaces the hidden row in the row header (see Figure 14.23). Figure 14.23. Row 3 is hidden.
To display hidden rows, highlight the rows above and below the hidden row. For instance, if row 3 is hidden, highlight rows 2 and 4. Choose Format, Row, Unhide. Excel displays the hidden row. Here's how to use the mouse to hide and unhide rows:
|