How to make a dropdown list in Excel

How to make a simple dropdown list in Excel through the insert function

Elementary option: does not need any preliminary setting 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 is contained in a non-gap column and the input is in order.

Place the cursor in the next empty cell in the range. In our example, this is B7.

Make a right mouse click and select "Select from drop-down list" or simply press Alt + "down arrow" on the keyboard.

After that, a list of all the values ​​\u200b\u200bentered above without repetitions will appear, and you just have to select the desired one 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 data range for the drop-down list, which is located anywhere in the sheet, as well as on another sheet or even in another file (more on that later).

List will not be updated if you add information below the specified range, but if you insert it not at the end, but between the lines, everything will work. When deleting the elements of the list, it will change in any case.

Select the cell that should have a dropdown list (we have it H3), switch to the "Data" tab and click "Data Validation".

In the window that opens, select the "List" data type, click in 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 by giving it a name. This will allow you to refer to it and make it easier to remember the ranges you work with frequently. Otherwise, this version of the drop-down list does not differ from the previous one: it will only update if you delete one of the values ​​or add rows inside the range.

First, create a named range. To do this, select the cells with future items in the drop-down list and title them by entering the name in the field with the names of the cells, and then press Enter. Mandatory condition: the first character must be a letter, spaces cannot be used (put "_" instead of them).

Now put the cursor in the cell where you want to make a list, and on the "Data" tab, click "Data Validation".

In the "Data type" column, select "List", and as the source, specify the name of the range, putting "=" in front of it. In our example, it came out "=brands".

The dropdown list with elements from the named range is ready.

How to make a dropdown list in Excel with data from another sheet or file

Sometimes you need to use external data, which are located on another sheet or even in a separate workbook. In this case, the INDIRECT function will help, which will allow you to refer to them. Because the source is a range of cells, the list will only update if you write or delete new values ​​inside the range. Also, when referencing another file, that file must be open.

Open both files, then select the cell with the future list and on the Data tab, click Data Validation.

Select "List", as the source, specify the following formula: =INDIRECT("[filename]sheetname!range") That is, inside the quotation marks there should be the name of the file with the data for the list in square brackets, then without a space the name of the sheet, and then also without a space an exclamation mark and a range designation. In our example it will be like this: =INDIRECT("[Book1]Sheet1!A3:A9")

The list is ready. To use data from another file, it must also be open at that time.

How to make a dropdown list in Excel from a table

The next method is a little more complicated, but much more versatile and correct. It is based on the so-called smart tables, they are dynamic. This means that any changes, whether it be adding new items or deleting them, will be instantly reflected in the drop-down list.

Select the range with the list items and their category, and then on the Home tab, click Format as Table.

Select any design option, check the option "Table with headers" in the opened window and click OK.

Place the cursor in one of the cells of the table and on the tab "Table Designer" that appears, enter the name of the list, confirming it by pressing Enter.

Click on the cell where you want the list to be and click Data Validation on the Data tab.

Specify "List" as the data type, and for the source, enter the following formula: =INDIRECT("table_name") In our example it will be like this: =INDIRECT("Doors")

The list will immediately start working and will be updated as you remove and add items.

How to make a drop down list in excel with search

If the list is large enough, you can make it easier to enter values ​​with hints that will speed up the search for the desired elements. You can do this through the ActiveX developer tools.

If you don't see the Developer tab, you need to enable it first. To do this, open the menu and select "More commands ...".

Click Customize Ribbon, then enable Developer and click OK.

On the Developer tab, click Insert and select Combo Box from ActiveX Controls.

“Draw” a list cell with the cursor, then right-click on it and select “Properties”.

Find the line ListFillRange, enter the range of values ​​for the list into it and press Enter. In our example, this is B6:B9.

Disable the design mode by clicking on the button of the same name. Now the list works, and matching items will be highlighted as you type.

How to make a linked drop down list in Excel

A very useful kind of list that comes up quite often. After selecting a row in one list, different sets of values ​​will be available in the second, depending on the previous one. For example, when specifying a product category in the first list in the second, the models change. Let's consider the implementation of such a table-based list so that the data is automatically updated when items are added or removed.

Prepare columns with data: in the headers are the items of the first, parent list, and below them are the items of the second one associated with it. Select the first column with a header and on the "Home" tab, click "Format as Table".

Select any of the styles, and then in the window that appears, enable the option "Table with headers" and click OK.

Click on any of the table cells, and then give it a name by typing it in the "Table name" field and pressing Enter. This will be the item of the first list, in our example - "Computers". Create tables for the rest of the columns in the same way and rename them.

Place the cursor in the cell where the first drop-down list will be, and on the "Data" tab, click "Data Validation".

Specify "List" as the type, and select the column headings with categories as the source, which will become the items of the parent list.

Next, place the cursor in the cell in which the second list will be, click "Data Validation", select the "List" option, and in the "Source" field, enter =INDIRECT and the address of the parent list cell in brackets. That is =INDIRECT(H3) for our example.

Now, when you select an item in the parent list, only the corresponding options will be displayed in the associated list. For example, if you click on the line "Laptops", then Excel will show only laptop models.

And if you specify “Headphones”, the corresponding names of accessories from the column of the same name will be displayed.