How to lock cells in excel, with shortcut, freeze certain cells, formula or cell format and unlock

If you want to lock cells(freeze cells), you can use both Excel content options and shortcut keys in Excel. You can lock one cell or multiple cells (including only lock the cells with formula); you can freeze one row or column, or multiple rows and columns; and you can freeze the entire table. The method is the same whether you freeze cells or freeze rows, as long as you select them.

If you only need to freeze the cell format(that is, the table structure), that is, you can no longer format the cells(including fixed row height and column width, cannot set font, color, bold, underline, etc.), only allow text to be entered into the cell, the same methods.

I, How to lock cells in excel(How to freeze cells in excel)

(I) How do you lock a cell in excel(The first locking method)

1. if you want to lock the cell with double slash header. Select one of the cells in the table, press Ctrl + A, select the entire table, the current tab is "Home", click "Format", and select "Lock Cell" in the pop-up menu to cancel the default lock for all cells in the entire table; select cell A1 to be locked, click "Format" again, select "Protect Sheet" from the pop-up options, open the "Protect Sheet" dialog box, keep the default settings, and click "OK", cell A1 is locked. When you double-click on it, you can no longer position the cursor inside it, but double-clicking on other cells, you can do it, indicating that only cell A1 is frozen; the procedure is shown in Figure 1:

Figure 1

2. If the lower version of Excel cannot freeze the cells with this method, after selecting A1, click "Format" first, then select "Lock Cell", lock A1, then click "Format", and select "Protect Sheet", then click "OK" in the window that opens.

3. Excel locks all cells by default, The reason why each cell can still be edited is because no protection is set. Therefore, you must cancel the lock on all cells before locking a cell, then protect the cell to be frozen, it will be locked, otherwise all cells will be locked.

(II) How to lock certain cells in excel(The second locking method)

1. Take the three cells of header B1:D1 are frozen as an example. Select any cell in the table, press Ctrl + A to select all tables, press Ctrl + 1, open the "Format Cells" dialog box, select the "Protection" tab, and click the "Locked" to unselect it, and then click "OK" to unlock all cells;

2. Select the three cells B1:D1, and press Ctrl + 1 again to open the "Format Cells" again, check the "Locked" to lock the three selected cells. Select the "Review" tab. Click "Protect Sheet" in the window that opens, enter "Password to unprotect sheet", press Enter to confirm, enter the same password again, and press Enter to confirm;

3. Return to the "Protect Sheet" dialog box, keep the other default settings, press Enter to confirm. Double-click any of the selected cells(such as B1), it prompts "The cell ro chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password." in the pop-up dialog box, double-click other cells can position the cursor inside, indicating that only the three selected cells are frozen; the operation steps, see screenshot in Figure 2:

Figure 2

4. There are multiple options under "Allow all users of this worksheet to", they refer to the operations that are allowed after the selected cell is locked. The checked ones indicate the operations that are allowed after the lock. What operations are required according to need to check. By default, "Select locked cells"(referring to a locked cell also allows it to be selected) and "Select unlocked cells"(referring to allowing selection of unlocked cells after the specified cell is locked) are checked.

(III) How to lock columns and rows in excel

The method is the same as locking one or more cells. If you want to freeze one or more rows, just select the row to be frozen and follow the above method; if you want to freeze one or more columns, the same method is used.

II, Shortcut to lock cells in excel(Freeze cell in excel shortcut)

(I) Lock cells with formula

1. Unlock all cells. Select one of the cells in the table, press Ctrl + A to select the entire table, hold down Alt, press H, O and L in turn to cancel the default lock of all cells in the table;

2. Select all cells with formula. Press the shortcut keys Ctrl + G to open the "Go To" dialog, click "Special", open the "Go To Special" dialog box, select "Formula", and click "OK" to select all cells with formulas;

3. Freeze all cells with formula. Hold Alt, press H, O and L, respectively, to lock the selected cells; press Alt again, and then press H, O, and P in turn to open the "Protect Sheet" dialog box, press Enter or click "OK", the cells with the formula was locked successfully, double-click one of the cells with the formula, the message "The cell ro chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password." pops up, and then double-click the other cell, you can still edit; the operation steps are shown in Figure 3:

Figure 3

4. Locking all cells with formula is the same as locking other cells. The only difference is that you must select all the cells with formula at one time, which requires the "Go To".

(II) Locking the entire table

Select one of the cells in the table, press Ctrl + A to select the entire table; Hold Alt, press H, O, and P in order to open the "Protection Sheet" dialog box, press Enter, double-click any cell in the table, and a message "The sheet has been protected" pops up, indicating that the table has been locked; operation process steps, see screenshot in Figure 4:

Figure 4

III, How to unlock cells in excel(How to unfreeze cells in excel)

(I) Unlock cells without a password

If you want to unlock cell A1. Select the "Review" tab, click "Unprotect Sheet", double-click cell A1, it can be edited, indicating that the worksheet protection is revoked; the operation steps are shown in Figure 5:

Figure 5

Tip: You can also press the shortcut keys Alt + A + P + S to unlock(unfreeze). The keystroke method is: hold down Alt and press A, P and S respectively.

 (II) Unlock cells with a password

Select one of the cells in the table, select the "Review" tab, click "Unprotect Sheet" to open the "Unprotect Sheet" dialog box, enter "Password", click "OK", double-click any cell in the table, it can be edited, indicating that the lock has been canceled; the operation steps are shown in Figure 6:

Figure 6

Tip: You don't need to select one or more locked cells, just select any cell in the table.

IV, The expanded application of locked cells in Excel

1. Lock row height or column width(fixed row height or column width)

The locking method has been introduced in the "IV, Excel fixed row height and column width" section of the article "How to adjust row height and change width of column in excel, with convert their units to inches,cm", click the article title in the parentheses to view.

2. How to lock cell format in excel(Ie freeze the table structure, that is, you can only enter text, you cannot set the format)

Select one of the cells in the table, press Ctrl + A to select all cells of the table, hold down Alt, press H, O, and L once to unlock all the cells in the table; hold down Alt, press H, O, and P in turn , Open the "Protect Sheet" dialog box, press Enter to select "OK", double-click any cell, you can also position the cursor inside, indicating that you can also enter text; the icon in the "Font" Group  have been grayed out, indicating that you can no longer set the cells format; the procedure is shown in Figure 7:

Figure 7