How to Use Dropdown List Normally in a Protected Worksheet?

We can lock worksheet and add password to protect worksheet. And after protecting worksheet, we cannot do any operation on worksheet including click dropdown list to select value without changing values in source. See the screenshot below.

Sometimes in questionary, we can provide a source table with different values, this part cannot be changed, so we can protect worksheet to prevent editing data by others; but on the other side, we want to others can still use dropdown list to filter their answer on the same worksheet, so we can collect data to do investigation. So, in this case we need to make dropdown list still available in worksheet protected mode. This article will help you to resolve this problem.

Step 1: Before protecting your worksheet. Select the dropdown list field.

If there are multiple dropdown lists or you are not sure where is dropdown list located, you can click Home->Find & Select->Data Validation to auto check on all dropdown lists.

Step 2: On selected dropdown list, right click, select Format Cells from loaded menu.

Step 3: In Format Cells window, click Protection tab. Uncheck Locked. Then click OK.

Step 4: Click Review->Protect Sheet, enter password to unprotect sheet, then click OK. This step is used for protecting worksheet. You can ignore this step if you already know how to protect worksheet in excel.

Step 5: After protecting worksheet, click data except dropdown list field.

Verify that operator cannot edit data on worksheet.

Step 6: Click dropdown list to answer the question.

Verify that operator can still use dropdown list in worksheet normally. That means dropdown list is unlocked even worksheet is protected.