Video: Manage drop-down lists - Microsoft Support (2024)

To prevent changes to your drop-down list data, you can hide the columns, rows, or the entire worksheet that contains the data. And unhide them if you need to make changes. You can also lock and password protect the cells on the worksheet or even the entire worksheet.

Lock cells to protect them

Your boss wanted you to protect a workbook, but she also wanted to be able to change a few cells after you were done. So, you unlocked some cells. Now your boss is done, so you can lock the cells. Here's how.

1. Select the cells you want to lock.

2. Click HOME, then click the Format Cell dialog box launcher (the arrow to the right of Alignment in the ribbon).

3. Click the Protection tab, check the Locked box, and click OK.

4. Click REVIEW > Protect Sheet or Protect Workbook, and reapply protection.

To prevent anyone from accidentally changing drop-down list data, you can hide the columns, rows, or the entire worksheet that contains the data.

You can then unhide them, if you need to make changes.

You can also lock and password-protect the cells on the worksheet, or even the entire worksheet.

By password protecting the data, only people with the password can make changes.

But it’ll also require a little more effort on your part, if you decide to make changes, because you have to unprotect the data first.

To hide columns or rows, select the columns or rows, right-click them, and click Hide.

To unhide them, select the column or row on one side of the hidden columns or rows, drag your mouse to the other side, right-click, and click Unhide.

To hide a worksheet, right-click the worksheet’s tab, and click Hide.

To unhide a worksheet, right-click any worksheet tab, click Unhide, and click OK.

By default, all cells on a worksheet are locked.

But, this has no effect until the worksheet is password-protected.

To password-protect a worksheet, right-click its tab and click Protect Sheet.

Enter the password and click OK.

Re-enter the password and click OK again.

Now, if I try to type in any cell on the worksheet, I get an error.

To unprotect a worksheet, right-click the worksheet’s tab, click Unprotect Sheet, enter the password, and click OK.

To lock specific cells, you first have to change the default for the worksheet.

Select the entire worksheet, right-click it, and click Format Cells.

In Format Cells, click the Protection tab, uncheck Locked, and click OK.

Select the specific cells you want to lock, right-click them, and click Format Cells.

In Format Cells, check Locked, and click OK.

Then password-protect the worksheet.

Only these cells on the worksheet are locked.

I can type in an unlocked cell, but if I try to type in a locked cell, I get an error.

To change the options for a drop-down list, you edit the data for the drop-down list, and possibly the Data Validation Source field.

For a drop-down list that uses a comma-delimited list in the source field, select the cells with the drop-down list, and in the Data Validation, in Source field, make the desired changes to the comma-delimited list.

And click OK.

And we can see the updated list options.

For a drop-down list that is based on a cell range, click a cell in the range and type the changes you want to make.

To insert a drop-down list option, right-click a cell in the range, click Insert, click OK, and type your new option in the cell.

Deleting a cell works similarly.

And again, we can see the updated list options.

You can also select the cells in the drop-down list, click Data Validation, and set Source to the new cell range.

For a drop-down list that is based on a named range, click a cell in the range and type the change you want to make.

To insert a drop-down list option, right-click a cell in the range, click Insert, click OK, and type your new option in the cell. Deleting a cell works similarly.

And again, we can see the updated list options.

To edit or delete a named range, click the FORMULAS tab, and click Name Manager.

In Name Manager, select the desired name, and click Edit or Delete.

To delete a drop-down list from cells, select the cells, click Data Validation, click Clear All, and click OK.

The drop-down list is removed from the cells, but not the values that have been selected.

You can remove those by pressing Delete.

Now you have a pretty good idea about how to apply and use drop-down lists in Excel.

Of course, there is always more to learn.

So, check out the course summary at the end, and best of all, explore Excel 2013 on your own.