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.

Use Dropdown List 1

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.

Use Dropdown List 2

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.

Use Dropdown List 3

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

Use Dropdown List 4

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

Use Dropdown List 5

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.

Use Dropdown List 6

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.

Use Dropdown List 7


Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...