Knowledge Library

Excel Sheet Protection Tricks You Should Not Miss Out

To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password. Say you own the team status report worksheet, where you want team members to add data in specific cells only and not be able to modify anything else. With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.

Choose Cell Elements you Want to Lock

Here’s what you can lock in an unprotected sheet:

(1) Ranges: You can enable users to work in specific ranges within a protected sheet. 

(2) Formulas: If you don’t want other users to see your formulas, you can hide them from being seen in cells or the Formula bar. 

You will need to complete the below settings before proceeding to lock your worksheet.

(1) Unlock any cells that needs to be editable

1.  In your Excel file, select the worksheet tab that you want to protect.

2. Select the cells that others can edit.

3. [Right-click] and select [Format Cells] and then go to the [Protection] tab and uncheck [Locked].

(2) Prevent a Formula from displaying in the formula bar

1. Select the range of cells whose formulas you want to hide. You can also select nonadjacent ranges or the entire sheet.

2. [Right-click] and select [Format Cells] and then go to the [Protection] tab and select [Hidden].

(3) Protect the worksheet

1. On the [Review] tab, click [Protect Sheet]

2. Insert your desired password to protect your sheet, reenter to confirm, and then click [OK]

3. An error message will appear if you attempt to edit cells that are not unlocked previously.

4. To unprotect your sheet, click [Unprotect Sheet] and enter your password again.

為防止其他用戶意外或有意更改,移動或刪除工作表中的數據,可以鎖定Excel工作表上的單元格,然後使用密碼保護工作表。 假設您擁有團隊狀態報告工作表,您希望團隊成員僅在其中添加特定單元格中的數據,而不能修改其他任何內容,您可以使用工作表保護,使工作表的某些部分可編輯,並且用戶將無法修改工作表中任何其他區域的數據。

 

選擇要鎖定的單元格元素

以下是您可以在未受保護的工作表中鎖定的項目:

(1) 範圍:您可以讓使用者在受保護的工作表內特定範圍中工作。

(2) 公式:如果您不希望其他使用者查看您的公式,您可以隱藏它們,不讓公式顯示在儲存格或資料編輯列中。

在繼續鎖定工作表之前,您需要完成以下設置。

(1)  解除鎖定任何必須為可編輯的儲存格

1. 在您的 Excel 檔案中,選取您想要保護的工作表索引標籤。

2. 選取其他人可以編輯的儲存格。

3. 在工作表中的任何位置按一下滑鼠右鍵並選取 [儲存格格式] ,然後移至 [保護] 索引標籤並清除 [鎖定]

 

(2) 防止公式顯示在資料編輯列中

1. 選取您要隱藏公式的儲存格範圍。 您也可以選取不相鄰的範圍或整個工作表。

2. 在工作表中的任何位置按一下滑鼠右鍵並選取 [儲存格格式] ,然後移至 [保護] 索引標籤並選取 [隱藏]

 

(3)  保護工作表

1. 在 [校閱] 索引標籤上,按一下 [保護工作表]

 [校閱] 索引標籤上的 [變更] 群組

2. 輸入所需的密碼以保護您的工作表,再次輸入以確認,然後單擊[確定]

保護工作表對話方塊

3. 如果您嘗試編輯以前未解鎖的單元,將出現錯誤訊息。

4. 要取消保護工作表,請單擊[取消保護工作表],然後再次輸入密碼。