This post will introduce you to the steps you can follow to use a dropdown list normally in a protected worksheet in Excel. While a protected worksheet restricts users from making changes to certain cells, you can still click a dropdown list to allow them to select from a set of predefined options.
We can lock worksheet and add password to protect worksheet. And after protecting worksheet, we cannot do any operation on worksheet including clicking dropdown list to select value without changing values in source. See the screenshot below.
Table of Contents
1. Use Dropdown List Normally in a Protected Worksheet
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. Just follow a few stesp:
Step1: Select the cell or cells where you want to add the dropdown list.
Step2: Go to the “Data” tab on the ribbon and click on “Data Validation“.
Step3: In the “Data Validation” dialog box, select “List” from the “Allow” dropdown menu.
Step4: In the “Source” field, enter the range of cells that contains the values you want to appear in the dropdown list. For example, if your values are in cells A2:A6, you would enter “=$A$2:$A$6”.
Step5: Click on the “OK” button to close the dialog box and apply the data validation to the selected cells.
Step5: 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.
Step6: On selected dropdown list, right click, select Format Cells from loaded menu.
Step7: In Format Cells window, click Protection tab. Uncheck Locked. Then click OK.
Step8: 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.
Step9: After protecting worksheet, click data except dropdown list field.
Verify that operator cannot edit data on worksheet.
Step10: 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.
Once the worksheet is protected, users can select the dropdown list and choose from the available options, but they won’t be able to edit the dropdown list or the values in the protected cells.
2. Video: How to Use Dropdown List Normally in a Protected Worksheet
This video will guide you through the process of using a dropdown list normally in a protected worksheet in Excel.