Drop-down list settings

You can use a comma-delimited list, a cell range, or a named range to define the options in a drop-down list.

We used a comma-delimited list in the previous video.

You might use such a list if there are just a few values, and they are unlikely to change.

If you need to change the list entries, such as adding and deleting entries, this type of drop-down list is more time consuming to manage.

We'll cover managing drop-down lists in video 4.

A comma-delimited list is also case sensitive.

This can be a problem when someone types an entry instead of picking it from the list.

For example, typing YES in all capital letters returns an error, if error messages are enabled, which is the default.

To avoid the problem, let's use a cell range for the entries in the drop-down list.

Select a cell where you want a drop-down list.

Click the DATA tab, and click Data Validation.

In the Data Validation dialog, set Allow to List; this enables a list in the cell.

Leave In-cell drop-down selected; this enables a drop-down list in the cell.

Leave Ignore blank selected; we'll cover this in the next video.

To provide the options in your drop-down list, click in Source and select the cell range that contains the options.

It can be on a different worksheet, as in this example, giving you greater flexibility in configuring and protecting the worksheets. We'll cover this in video 4.

The range must be a single row or column, and click OK.

Verify the cell contains a drop-down list with the options provided by the cell range.

To use this drop-down list in other locations, copy it to other cells.

Select the cell.

If it shows a text or number entry, press Delete to clear it.

This way, text and numbers won't appear in the destination cells, so it doesn't seem like an entry was already selected.

You can use the keyboard shortcut Ctrl+C to copy the cell.

Then select the destination cells and press Ctrl+V to paste it.

These cells now have the drop-down list.

A named range, such as Fruits, is easier to remember than a cell range, such as A2:A37.

To use a named range for the options in your drop-down list, you start by creating one.

Select the cell range you want to name.

In the name box, type the name you want for the range. For example, "_Veggies".

The first character of a name must be a letter or an underscore.

The rest of the name can be letters, numbers, periods, and underscores.

A name can't have spaces.

And you can't use predefined statements, such as true or false, or cell references, such as A1.

When you select the cells of a named range, you'll see the name in the name box.

Now, you are ready to create a drop-down list that uses the named range.

Select the cell where you want a drop-down list. Click Data Validation, select List, click in Source, press F3, select the name, click OK, and click OK again.

Verify the cell contains the drop-down list with the entries provided by the named range, and copy the list to the other cells.

Up next, Input and error messages.