How to create a drop-down list in Excel

How to make a simple drop-down list in Excel using the insert function

Elementary option: it does not need any configuration and is available thanks to the standard insert function. It allows you to select previously entered values from a list and quickly add them. However, this only works if the data are contained in the column without gaps and if the input is in order.

1. Place the cursor in the next empty cell of the range.
2. Right-click and press "Select from the drop-down list" or just press Alt + Down arrow on your keyboard.
3. After that, a list of all the values entered above will appear, and all you have to do is to select the one you want and click on it.

How to make a drop-down list in Excel from a range of data

The following method allows you to use any range of data for the dropdown list, which can be situated anywhere on the sheet or even in another file. The list will not update if you add information below that range, but if you insert it between the lines rather than at the end, it will work. If you delete items from the list, it will change in any case.

• Highlight the cell to place your drop-down list, switch to the "Data" tab and click "Check Data".
• In the window that opens, select the "List" data type, click on the "Source" field, and select the desired cells.
• Click OK, and a drop-down list with the desired values will appear in the specified cell.

How to make a drop-down list in Excel from a named range

Any range of cells can be named. Naming ranges will allow you to refer to it and make it easier to remember the ones you work with frequently. Otherwise, this variant of the drop-down list is the same as the previous one: it will only be updated if you delete one of the values or add rows to the range.

• First, create a named range. To do this, select the cells with the future items of the drop-down list and title them by typing the name in the cell name field, and then press Enter. The first character must be a letter, you cannot use spaces (if needed, use "_" instead).
• Now place the cursor in the cell where you want to make the list, and on the "Data" tab, click "Check Data".
• In the "Data Type" column, select "List", and specify the name of the range as the source, putting "=" in front of it.
• The drop-down list with elements from the named range is ready.

How to make a drop-down list in Excel with data from another sheet or file

Sometimes you need to use external data that is located on a different sheet or even in a separate file. In this case, the DVSSYL function will help. Since a range of cells is used as the source, the list will be updated only if you write new values into the range or delete them. In addition, if you refer to another file, that file must be open.

• Open both files, then select the cell with the future list, and on the "Data" tab click "Check Data".
• Select "List", and specify the following formula as the source: =DVSSSYL("[file_name]sheet_name!range") That is, inside the quotes, must be the file name with data for the list in square brackets, then without space the sheet name, and then the exclamation mark and range designation.
• The list is ready. It can use data from another file only when it’s opened.

How to make a drop-down list from a table

The next method is a bit more complicated but much more versatile. It is based on so-called dynamic smart tables. This means that any changes, whether you add new items or remove them, will instantly be reflected in the drop-down list.

• Select the range with the list items and their category, and then click Format as Table on the Home tab.
• Select any design option, check the "Table with Headers" option in the window that opens, and click OK.
• Place the cursor in one of the table cells and on the Table Builder tab that appears, enter the name of the list, confirming it by pressing Enter.
• Click the cell where you want to place your list and click "Check Data" on the Data tab.
• Specify "List" as the data type, and for the source, enter this formula: =DWSSL("table_name").
• The list will work immediately and will be updated when items are deleted and added.

How to make a drop-down list in Excel with a search

If the list is large enough, you can facilitate the entry of values with cues that will speed up the search for the desired items. This can be done through ActiveX developer tools.

• If the "Developer" tab is not displayed, you should first enable it. To do this, expand the menu and select "Other commands...".
• Click Configure Ribbon, then turn on Developer and click OK.
• On the "Developer" tab, click "Insert" and select "List field" from the ActiveX items.
• Use the cursor to draw a cell in the list, then right-click on it and select "Properties".
• Find the ListFillRange line, type the range of values for the list into it, and press Enter.
• Disable the Designer mode by clicking the button of the same name. Now the list is updated, and as you type, the matching items will be highlighted.

How to make a drop-down list with links in Excel

This is a very useful kind of list, which occurs quite often. Consider a table-based implementation of such a list, so that the data are automatically updated when items are added or removed.

• Select the first column with the header and on the Home tab click Format as Table.
• Select any of the styles, and then in the window that appears, turn on the option "Table with headings" and click OK.
• Click on any of the table cells, and then give it a name by typing it into the "Table Name" field and pressing Enter. In the same way, create tables for the remaining columns and rename them.
• Place the cursor in the cell where the first drop-down list will appear and click "Check Data" on the "Data" tab.
• Specify "List" as the type, and select the caps of the columns with categories, which will become the parent list items as the source.
• Next, place the cursor in the cell where the second list will be placed, click "Check Data", select the "List" option, and in the "Source" field, type =DBCRL and the address of the parent list cell in brackets.
• Now when you selected an item in the parent list, only the corresponding options will be displayed in the linked list.