Creating a drop-down list in Excel. Creating a Dropdown List

Twitter

A drop-down list refers to the content of several values in one cell. When the user clicks on the arrow on the right, a specific list appears. You can choose a specific one.

Very handy tool Excel to check the entered data. The capabilities of drop-down lists allow you to increase the comfort of working with data: data substitution, display of data from another sheet or file, the presence of a search function and dependencies.

Creating a Dropdown List

Path: Data menu - Data Validation tool - Options tab. Data type – “List”.

You can enter the values from which the drop-down list will be composed in different ways:

Any of the options will give the same result.

Dropdown list in Excel with data substitution

Need to make a dropdown list with values from dynamic range. If changes are made to the existing range (data is added or removed), they are automatically reflected in the drop-down list.

Let's test it. Here is our table with the list on one sheet:

Let’s add a new value “Christmas tree” to the table.

Now let’s remove the “birch” value.

The “smart table”, which easily “expands” and changes, helped us realize our plans.

Now let's make it possible to enter new values directly into the cell with this list. And the data was automatically added to the range.

When we enter a new name into an empty cell of the drop-down list, a message will appear: “Add the entered name baobab to the drop-down list?”

Click “Yes” and add another line with the value “baobab”.

Dropdown list in Excel with data from another sheet/file

When the values for the dropdown list are located on another sheet or in another workbook, standard way does not work. You can solve the problem using the INDIRECT function: it will generate correct link on external source information.

We make active the cell where we want to place the drop-down list.
Open data verification options. In the “Source” field, enter the formula: =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”).

The name of the file from which the information for the list is taken is enclosed in square brackets. This file must be open. If a book with the required values is in a different folder, you need to specify the full path.

How to make dependent dropdown lists

Let's take three named ranges:

This required condition. The above describes how to do regular list named range (using the Name Manager). Remember that the name cannot contain spaces or punctuation marks.

Let's create the first drop-down list, which will include the names of the ranges.

When you have placed the cursor in the “Source” field, go to the sheet and select the required cells one by one.

Now let's create a second dropdown list. It should reflect those words that correspond to the name selected in the first list. If “Trees”, then “hornbeam”, “oak”, etc. Enter in the “Source” field a function of the form =INDIRECT(E3). E3 – cell with the name of the first range.

Selecting multiple values from an Excel dropdown list

It happens when you need to select several items from a drop-down list at once. Let's consider ways to implement the task.

We create standard list using the Data Validation tool. Add to source sheet ready macro. How to do this is described above. With its help, the selected values will be added to the right of the drop-down list.
To make the selected values appear below, we insert another handler code.
To display the selected values in one cell, separated by any punctuation mark, use the following module.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval)<>0 And oldval<>newValThen
Target = Target & "," & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Don’t forget to change the ranges to “your own”. We create lists in the classic way. And macros will do the rest of the work.

Dropdown list with search

When you enter the first letters on the keyboard, matching elements are highlighted. And these are not all pleasant moments of this instrument. Here you can customize the visual presentation of information and specify two columns as a source at once.

The easiest way to complete this task is as follows. By pressing right button by cell under the data column call context menu. The field of interest here is Select from drop-down list. The same can be done by pressing the key combination Alt+Down Arrow.

However, this method will not work if you want to create a list in another cell that is not in the range and in more than one before or after. The following method will do this.

Standard method

Required select a range of cells, from which it will be created drop-down list, then Insert – Name – Assign(Excel 2003). In more new version(2007, 2010, 2013, 2016) go to the tab Formulas, where in the section Specific names find the button Name Manager.

Press the button Create, enter a name, you can use any name, after which OK.

Select cells(or several) where you want to insert a drop-down list of required fields. From the menu, select Data – Data type – List. In field Source enter the previously created name, or you can simply specify the range, which will be equivalent.

Now the resulting cell can be copy anywhere on the sheet, it will contain a list necessary elements tables. You can also stretch it to get a range with drop-down lists.

An interesting point is that when the data in the range changes, the list based on it will also change, that is, it will dynamic.

Using the controls

The method is based on insert control called " combo box", which will represent a range of data.

Select a tab Developer(for Excel 2007/2010), in other versions you will need to activate this tab on the ribbon in parameters – Customize your feed.

Go to this tab - click the button Insert. In the controls select Combo box(not ActiveX) and click on the icon. Draw rectangle.

Right click on it - Object Format.

By linking to a cell, select the field where you want to place it. serial number element in the list. Then click OK.

Using ActiveX Controls

Everything, as in the previous one, just select Combo box(ActiveX).

The differences are as follows: the ActiveX control can be in two variants - mode debugging, which allows you to change parameters, and - mode input, you can only sample data from it. Changing modes is done using the button Design Mode in the tab Developer.

Unlike other methods, this allows tune fonts, colors and perform a quick search.

If you have a list of data that you want to group and summarize, you can create an outline up to eight levels deep, each with one group defined. Each inner level, represented by a higher number in the structure symbols, displays details for the previous outer level, represented by fewer in structure symbols. Using the outline, you can quickly display total rows or columns, and also display detailed data for each group. You can create a row structure (as shown in the example below), a column structure, or a row and column structure.

A structured row of sales information grouped by geographic area and month, displaying multiple total and detail rows.

1. To display the level rows, click the corresponding structure symbols.

2. Level 1 contains the sum of sales for all detail lines.

3. Level 2 contains the sum of sales for each month in each region.

4. Level 3 contains rows with detailed data (in in this case lines 11 to 13).

5. To expand or collapse data in an outline, click the outline symbols and .

There are several ways to create a dropdown list. The choice of one depends on the structure of the data you have.

The first way to create a two-level list

The first method is based on creating a “smart” table, the header of which contains the values of the first drop-down list (group), and the table rows correspond to the values of the second drop-down list (subgroup). The values of the subgroup elements should be located in the corresponding group column, as in the figure below.

Now let's start creating the first drop-down list of the group (in my case, a list of countries):

Select the cell into which you will insert the drop-down list;
Go to the ribbon tab Data;
Choosing a team Data checking;
Select a value from the drop-down list List;
In field Source indicate the following formula =INDIRECT("Table1[#Headers]").

Formula INDIRECT returns a reference to the range of smart table headers. The advantage of using such a table is that as you add columns, the dropdown list will automatically expand.

It remains to create a second dependent drop-down list - a list of subgroups.

We boldly repeat the first 4 points described above. Source in the window Data checking for the second drop-down list the formula will be =INDIRECT("Table1["&F2&"]"). Cell F2 in this case, the value of the first drop-down list.

You can also use a regular dumb table, but in this case you will have to manually change the header and row ranges. In the example considered, this happens automatically.

The second way to create a two-level list

The second method is convenient to use when the drop-down list data is written in two columns. The first contains the name of the group, and the second contains the name of the subgroup.

IMPORTANT! Before creation dependent list By subgroups, you need to sort the source table by the first column (the column with the group); then it will be clear why this is being done.

To create a drop-down group we need extra column, containing the unique group values from the source table. To create this list, use the Remove Duplicates feature or use the Unique command from the VBA-Excel add-in.

Now let's create a drop-down list of groups. To do this, follow the first 4 points from the first method of creating a two-level list. As Source specify a range of unique group values. Everything is standard here.

Recommendation: It is convenient to specify a named range as a source. To create it, open Name Manager from tab Formulas and give a name to the range with unique values.

Now the hardest part is to specify in Source a dynamic link to a range with the values of the second drop-down list (list of subgroups). We will solve it using the function OFFSET(link, row_offset, column_offset, [height], [width]), which returns a reference to a range that is a specified number of rows and columns away from a cell or range of cells.

Link in our case - $A$1- upper left corner of the source table;
Offset_by_rows - MATCH(F3,$A$1:$A$67,0)-1- line number with the value of the desired group (in my case, country cell F3) minus one;
Offset_by_columns - 1 - since we need a column with subgroups (cities);
[Height] - COUNTIF($A$1:$A$67,F3)- number of subgroups in the desired group (number of cities in the country F3);
[Width] - 1 - since this is the width of our column with subgroups.

How to create a drop-down list in Excel? Everyone has long known how well Excel works with tables and various kinds of formulas, but few people know that you can make drop-down lists here. And today we will talk about them.

And so there are several options for how to make drop-down lists for working in Microsoft Office Excel.

Option one is very simple. If you enter similar data in the same column from top to bottom, then you just need to stand on the cell below the data and press the key combination “Alt + down arrow”. A drop-down list will appear in front of you, from which you can select the data you need with one click.

Disadvantage this method is that it is designed for a sequential method of data entry and if you click on any other cell in the column, the drop-down list will be empty.

Option two gives more opportunities, it is still considered standard. This can be done through a data check. First of all, we need to select the range of data that will go into our list and give it a name.

You can edit this range through the “Formulas” menu tab by selecting the “Name Manager” icon. In it you can create a new drop-down list, edit an existing one, or simply delete an unnecessary one.

The next step is to select the cell where our drop-down list will be placed and go to the “Data” menu tab, click on the “Data Check” icon. In the window that opens, we need to select the type of data that will be entered in our cell. In our case, we select “Lists” and below, through the equal sign, write the name of our range, and click OK. In order to apply the list to all cells, just select the entire column or the area you need before turning on data validation.

There are other more complex options for creating a dropdown list, such as: inserting through the Developer menu tab, where you can insert dropdown lists as part of a form element or as part of ActiveX element. Or write appropriate macros to create and operate drop-down lists.

Enter data in cells A1:A10, which will act as the source for the list. In our example, we entered numbers, they will appear in the drop-down list. Select the cell (For example, E5) that will contain the drop-down list. Select the Data menu -> Data Validation to open the Validate Input Values dialog box.

3. On the Options tab, select the List option from the drop-down menu. Make sure the correct boxes are checked.

4. Then, click on the button. The following dialog box will appear.

5. Select the items that will appear in the drop-down list on the sheet using the mouse, click on the button and return back to the “Validate input values” window, then click the “OK” button.

6. A drop-down list in Excel will be created.

If your list is short, you can enter items directly into Source in the Setup tab of the Validate Input dialog box. Separate each list item with the separators specified in the regional settings.
If the list needs to be on another sheet, you can use the "=List" option before specifying the data range.
How to create a drop-down list in Excel based on data from the list

Let's imagine that we have a list of fruits:
How to make a dropdown list in Excel

To create a dropdown list we will need to do the following steps:

Go to the “Data” tab => “Working with Data” section on the toolbar => select the “Data Validation” item.

In the “Source” field, enter the range of fruit names =$A$2:$A$6 or simply place the mouse cursor in the “Source” value entry field and then select the data range with the mouse:

If you want to create dropdown lists in multiple cells at a time, then select all the cells in which you want to create them and then follow the steps above. It is important to ensure that cell references are absolute (for example, $A$2) and not relative (for example, A2 or A$2 or $A2).

How to make a dropdown list in Excel using manual input data

In the example above, we entered a list of data for a drop-down list by selecting a range of cells. In addition to this method, you can enter data to create a drop-down list manually (it is not necessary to store it in any cells).
For example, imagine that we want to display two words “Yes” and “No” in a drop-down menu.

For this we need:
Select the cell in which we want to create a drop-down list;
Go to the “Data” tab => “Working with Data” section on the toolbar =>
Validating Data in Excel

In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type:
Validating input values in Excel

In the “Source” field enter the value “Yes; No".
Click “OK”
Not really

The system will then create a drop-down list in the selected cell. All elements listed in the “Source” field, separated by semicolons, will be reflected in different lines of the drop-down menu.

If you want to simultaneously create a drop-down list in several cells, select the required cells and follow the instructions above.
How to create a drop-down list in Excel using the OFFSET function

Along with the methods described above, you can also use the OFFSET formula to create drop-down lists.

For example, we have a list with a list of fruits:

In order to make a drop-down list using the OFFSET formula, you must do the following:
Select the cell in which we want to create a drop-down list;
Go to the “Data” tab => “Working with Data” section on the toolbar => select “Data Validation”:
Validating Data in Excel

In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type:
Validating input values in Excel

In the “Source” field enter the formula: = OFFSET(A$2$,0,0,5)
Click “OK”

The system will create a drop-down list with a list of fruits.
How does this formula work?

In the example above, we used the formula =OFFSET(link,offset_by_rows,offset_by_columns,[height],[width]).
This function contains five arguments. The “link” argument (in the example $A$2) indicates which cell to start the offset from. In the arguments “offset_by_rows” and “offset_by_columns” (in the example the value “0” is specified) – how many rows/columns need to be shifted to display data.

The “[height]” argument specifies the value “5”, which represents the height of the range of cells. We do not specify the “[width]” argument, since in our example the range consists of one column.
Using this formula, the system returns to you as data for the dropdown list a range of cells starting with cell $A$2, consisting of 5 cells.

How to make a drop-down list in Excel with data substitution (using the OFFSET function)

If you use the OFFSET formula in the example above to create a list, you are creating a list of data captured in certain range cells. If you want to add any value as a list item, you will have to adjust the formula manually.

Below you will learn how to make a dynamic drop-down list that will automatically load new data for display.
To create a list you will need:
Select the cell in which we want to create a drop-down list;

Go to the “Data” tab => “Working with Data” section on the toolbar => select “Data Validation”;
In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type;
In the “Source” field, enter the formula: = OFFSET(A$2$,0,0,COUNTIF($A$2:$A$100;”<>”))
Click “OK”

In this formula, in the “[height]” argument, we indicate as an argument denoting the height of the list with data – the COUNTIF formula, which calculates the number of non-empty cells in the given range A2:A100.

Note: for correct operation formulas, it is important that there are no empty lines in the list of data to be displayed in the drop-down menu.

How to create a drop-down list in Excel with automatic data substitution

In order for new data to be automatically loaded into the drop-down list you created, you need to do the following:
We create a list of data to display in the drop-down list. In our case, this is a list of colors. Select the list with the left mouse button:
drop-down list with automatic substitution in Excel

On the toolbar, click “Format as table”:

Select a table design style from the drop-down menu

By clicking the “OK” button in the pop-up window, we confirm the selected range of cells:

Assign a name to the table in the upper right cell above column “A”:

The table with the data is ready, now we can create a drop-down list. To do this you need:
Select the cell in which we want to create a list;

Go to the “Data” tab => “Working with Data” section on the toolbar => select “Data Validation”:

In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type:

In the source field we indicate = “the name of your table”. In our case, we called it “List”:
Source field automatic data substitution in Excel drop-down list

Ready! A drop-down list has been created, it displays all the data from the specified table:

In order to add a new value to the drop-down list, simply add information to the cell next after the table with the data:

The table will automatically expand its data range. The drop-down list will be replenished accordingly with a new value from the table:
Automatically inserting data into a drop-down list in Excel

How to copy a dropdown list in Excel

Excel has the ability to copy created drop-down lists. For example, in cell A1 we have a drop-down list that we want to copy to the range of cells A2:A6.

To copy a dropdown list with the current formatting:
left-click on the cell with the drop-down list that you want to copy;

select the cells in the range A2:A6 into which you want to insert the drop-down list;

Press the keyboard shortcut CTRL+V.
So, you will copy the drop-down list, saving original format list (color, font, etc.). If you want to copy/paste a dropdown list without saving the format, then:
left-click on the cell with the drop-down list that you want to copy;

press the keyboard shortcut CTRL+C;
select the cell where you want to insert the drop-down list;
click right button mouse => call the drop-down menu and click “Paste Special”;
dropdown list in excel

In the window that appears, in the “Insert” section, select “conditions on values”:

Click “OK”
After this, Excel will copy only the data from the drop-down list, without preserving the formatting of the original cell.
How to select all cells containing a drop-down list in Excel

Sometimes it is difficult to understand how many cells are in Excel file contain drop-down lists. There is an easy way to display them. For this:

Click on the “Home” tab on the Toolbar;
Click “Find and Select” and select “Select Group of Cells”:

In the dialog box, select “Data Validation”. In this field you can select the items “All” and “The Same”. “All” will allow you to select all drop-down lists on the sheet. The “same” item will show drop-down lists with similar data content in the drop-down menu. In our case, we select “all”:
Dropdown list in Excel. How to find all lists

Click “OK”
By clicking “OK”, Excel will select all cells with a drop-down list on the sheet. This way you can bring all the lists at once to general format, highlight boundaries, etc.

How to make dependent dropdown lists in Excel

Sometimes we need to create several drop-down lists, and in such a way that, by selecting values from the first list, Excel determines what data to display in the second drop-down list.
Let's assume that we have lists of cities in two countries, Russia and the USA:

To create a dependent dropdown list we need:
Create two named ranges for cells “A2:A5” with the name “Russia” and for cells “B2:B5” with the name “USA”. To do this, we need to select the entire data range for the drop-down lists:
dependent dropdown list in Excel

Go to the “Formulas” tab => click in the “Defined names” section on the “Create from selection” item:
Dependent Dropdown Lists in Excel

In the “Create names from a selected range” pop-up window, check the “in the line above” box. Having done this, Excel will create two named ranges “Russia” and “USA” with lists of cities:
dependent-drop-down-list-in-excel

Click “OK”
In cell “D2” create a drop-down list to select the countries “Russia” or “USA”. So, we will create the first drop-down list in which the user can select one of two countries.

Now, to create a dependent dropdown list:
Select cell E2 (or any other cell in which you want to make a dependent dropdown list);
Click on the “Data” tab => “Data Check”;
In the “Validate input values” pop-up window, on the “Parameters” tab, in the data type, select “List”:
Validating input values in Excel

Click “OK”

Now, if you select the country “Russia” in the first drop-down list, then only those cities that belong to this country will appear in the second drop-down list. This is also the case when you select “USA” from the first drop-down list.

Creating a drop-down list in Excel. Creating a Dropdown List