Excel: select from a list. Drop-down list in a sheet cell

Many users do not even realize that the well-known Excel spreadsheet editor has such functions and has tools that go far beyond the main purpose of using the program - editing tables. This article will talk about the option to select from a list in Excel. In other words, we’ll show you how to create drop-down lists in table cells.

Method 1: create an additional list

If you want to make a selection list in an Excel cell, the easiest way is to use this method, which involves simply creating a drop-down list. By the way, it will be told about its two variations, so read it to the end to figure it out.

Step 1: preparing the data

First you need to create a table with data in a separate range of cells, which will be in the drop-down list in the future. Let's look at an example of a product. So, we have a list of seven products, to be more precise, products. We will create this label to the right of the main table, within which drop-down lists will be created.

If you do not want the data table to be on the same sheet as the main one, you can create it on a separate sheet. It will not play roles.

Step 2: enter a range name

To use the option to select from a list in Excel, you must first enter a range name with data for the future list. This is done quite simply:

1. Select the cells in which the product names are in this case.
2. Right-click (RMB) on the selection.
3. Select the "Assign Name" option from the menu.
4. In the window that appears, in the "Name" field, enter the name of the range. It can be absolutely anything.
5. Click OK.

The second step is completed. The range of cells we just created will make it easier to create a list in the future.

Step 3: make a drop down list

Now you can go directly to using the list option in Excel. This is done as follows:

1. Select the desired range of cells in which the drop-down lists will be located.
2. Click on the "Data" tab.
3. In the “Work with Data” tool group, click on the “Data Check” button.
4. In the window that appears, on the "Parameters" tab, select "List" from the "Data Type" drop-down list.
5. Enter the name of the previously created range of cells in the "Source" field, after putting an equal sign. In our case - “= Products”.
6. Click OK.

Immediately after that, drop-down lists will appear in the selected cells. This was the first way to create it, let's move on to the second.

Method 2: create a drop-down list through the "Developer" menu

It is possible that the previous instruction seemed incomprehensible to you, and you encountered difficulties when creating an item in a table cell to select a value from a list in Excel. The second implementation method can be a worthy alternative.

How to create a drop-down list in a sheet cell using the "Developer" menu? As in the previous time, for a better understanding, all actions will be divided into stages.

Step 1: turn on the "Developer" menu

So, first of all, you need to activate the "Developer" menu, since by default it is not among other tabs.

1. Click on the “File” button.
2. Click on the “Options” button.
3. In the pop-up window of the same name, go to the "Tape Settings" section.
4. In the "Main Tabs" area, check the box next to "Developer".
5. Click OK.

The necessary toolbar is activated, now you can start creating the list.

Step 2: insert the drop down list

You need to create the Dropdown list element itself. For this:

1. Click on the added Developer tab.
2. On the sheet, create a list of products that will be used to create a drop-down list.
3. Click on the “Insert” button and select “Combo Box” in the additional menu.
4. Click on the cell where the list itself will be located.

Already at this stage, the desired item will appear, but if you click on it, an empty list will open. Accordingly, you need to add products to it.

Step 3: set the necessary parameters

To add items to the drop-down list, you must:

1. On the toolbar, click on the button "Design Mode".
2. Then press the button “Properties of the control” located next to it.
3. In the window with the properties in the ListFillRange column, enter the range of cells in which the items of the future drop-down list are located.
4. Now click RMB on the drop-down list and select “ComboBox Object” in the menu, and in the Edit submenu.

Immediately after that, the indicated items will be added to the drop-down list. Just like that, you can select from a list in Excel using the second method.

Method 3: create a linked list

To select multiple values, a drop-down list in Excel works best, but sometimes there is a need for the interconnection of several such lists. Fortunately, the program allows you to do this, and then a detailed step-by-step instruction with a detailed description of all actions will be provided.

Step 1: create an additional list

It is imperative to create a basic drop down list. We will not dwell on this for a long time, since the design is completely similar to the one that was described in the first method. We will only say that we will associate the name of the product with its weight. It is recommended to create the name of the ranges with measures of goods (g, kg, ml, l).

Step 2: Link the first list to the second

Well, now we’ll go directly to the main one - to create the second “Select from List” element in Excel, which will be associated with the first.

1. Place the cursor in the cell where the second list will be located.
2. Open the “Validation of Entered Values” window by clicking on the “Data Validation” button on the “Data” tab.
3. In the window that appears, on the "Parameters" tab, select "List" from the "Data Type" drop-down list.
4. In the input field "Source", enter the formula "INDIRECT", which refers to the first list. In this case, it will look like this: "= INDIRECT ($ B3)."
5. Click OK.

The second list is created. It is tied to the first, which means that, having chosen the value in this case of the product, you will need to choose its measure as well. In order not to create the same lists in other cells, select the already added ones and drag the selection to the bottom right corner of the selection down, thereby filling in all the necessary cells.

Conclusion

The option to select from a list in Excel is quite useful, you could understand this from the foregoing. But much more important is that to create it you do not need to have in-depth knowledge of using a table processor. Moreover, there are even three ways to implement this function, and with the help of the described instructions you should not have any special problems when executing them.