Excel does not drop down list. Creating a Dropdown List

If you are filling out a table in Excel, and the data in a column can sometimes be repeated, for example, the name of a product, or the name of an employee, then in order not to enter the desired parameter every time, it is simpler and easier to create a drop-down list once and select a value from it.

In this article we will look at how to make drop-down lists various types in an Excel table.

Create a simple dropdown list

To do this, in cells A1:A7 we enter the data that will be displayed in the list. Now let's select the cell in which we will create a drop-down list - B2.

Go to the “Data” tab and click on the “Data Check” button.

On the “Parameters” tab, in the “Data type” field, select “List”. You can enter values ​​in the Source field in different ways:

1 – enter values ​​for the list manually, separated by semicolons;

2 – indicate the range of cells into which the data for the drop-down list is entered;

3 – select cells with names, click on them right click mouse and select “Assign a name” from the menu.

Select cell B2 and put “=” in the “Source” field, then write the created name.

So we created a simple dropdown list in Excel.

If you have a heading for a column, and you need to fill each row with values, then select not one cell, but a range of cells - B2:B9. Then you can select the desired value in each cell from the drop-down list.

Adding values ​​to a drop-down list - dynamic list

In this case, we will add values ​​to the required range, and they will be automatically added to the drop-down list.

Select the range of cells – D1:D8, then on the “Home” tab, click “Format as table” and select any style.

Confirm the location of the data and check the “Table with headers” box.

At the top we write the title of the table - “Employees”, and fill it with data.

Select the cell in which the drop-down list will be and click on the “Data Check” button. In the next window, in the “Source” field, write the following: =INDIRECT(“Table1”). I have one table on a sheet, so I write “Table1”, if there is a second one – “Table2”, and so on.

Now let's add a new employee name to our list: Ira. It appeared in the drop-down list. If we delete any name from the table, it will also be deleted from the list.

Dropdown list with values ​​from another sheet

If the table with drop-down lists is on one sheet, and the data for these lists is on another, then this function will help us a lot.

On Sheet 2, select one cell or range of cells, then click on the “Data Validation” button.

Go to Sheet 1, place the cursor in the “Source” field and select the desired range of cells.

Now you can add names on Sheet 1, they will be added to the drop-down lists on Sheet 2.

Creating dependent drop-down lists

Let's assume we have three ranges: first names, last names, and patronymics of employees. For each, you need to assign a name. We select the cells of this range, you can also empty ones - over time you can add data to them, which will appear in the drop-down list. Right-click on them and select “Assign a name” from the list.

We call the first “Name”, the second – “Last Name”, the third – “Father”.

Let's make another range in which the assigned names will be written. Let's call it "Employees".

We make the first drop-down list, which will consist of the names of the ranges. Select cell E1 and on the “Data” tab select “Data Validation”.

In the “Data Type” field, select “List”; in the Source field, either enter “=Employees” or select a range of cells that have been assigned a name.

The first dropdown list has been created. Now in cell F2 we will create a second list, which should depend on the first. If we select “Name” in the first one, a list of names will be displayed in the second one; if we select “Last Name”, a list of surnames will be displayed.

Select the cell and click on the “Data Check” button. In the “Data type” field, select “List”; in the source field, enter the following: =INDIRECT($E$1). Here E1 is the cell with the first drop-down list.

Using this principle, you can make dependent drop-down lists.

If in the future, you will need to enter the values ​​​​into a range that is given a name, for example, “Last Name”. Go to the Formulas tab and click Name Manager. Now select “Last Name” in the range name, and below, instead of the last cell C3, write C10. Click the check mark. After this, the range will increase, and you can add data to it, which will automatically appear in the drop-down list.

Now you know how to make a drop-down list in Excel.

How to create a drop-down list consisting of several cells at once (for example, so that the name has a cost)

Thanks, all worked well.

A drop-down list with values ​​from another sheet does not work, since the window when data verification is open does not allow working with other windows, especially with another sheet!

A dependent dropdown list allows you to do a trick that is very often praised by users Excel templates. A trick that makes work easier and faster. A trick that will make your curves comfortable and pleasing.

Example of creating a dependent drop-down list in an Excel cell

Example of using a dependent dropdown list to create convenient form filling out documents with which sellers ordered goods. From the entire assortment, they had to choose the products that they were going to sell.

Each seller first identified a product group, and then a specific product from this group. The form must include full name groups and a specific product index. Since typing this by hand would be too time-consuming (and annoying), I came up with a very quick and simple solution - 2 dependent dropdowns.

The first was a list of all product categories, the second was a list of all products in the selected category. So I created a dropdown list dependent on the selection made in the previous list (here you will find material on how to create two dependent dropdown lists).

The template user wants to get the same result home budget where the category and subcategory of expenses are needed. An example of the data is in the figure below:

So, for example, if we select the Entertainment category, then the list of subcategories should include: Cinema, Theater, Pool. Very fast decision, if you want to analyze more detailed information in your home budget.

List of categories and subcategories in Excel dependent dropdown list

I admit that in the version of my proposed home budget, I limit myself to only a category, since for me such a division of expenses is quite enough (the name of expenses / income is considered as a subcategory). However, if you need to separate them into subcategories, then the method I describe below is ideal. Feel free to use it!

A final result as follows:

Dependent dropdown list of subcategories

In order to achieve this, we need to make a slightly different data table than if we were creating a single drop-down list. The table should look like this (range G2:H15):

Working Excel Source Spreadsheet

In this table you must enter a category and its subcategories next to it. The category name must be repeated as many times as there are subcategories. It is very important that the data is sorted by the Category column. This will be extremely important when we write the formula later.

You could also use the tables from the first image. Of course, the formulas would be different. Once, even I found such a solution on the Internet, but I didn’t like it because it had a fixed list length: which means that sometimes the list contained empty fields, and sometimes it did not display all the elements. Of course, I can avoid this limitation, but I admit that I like my solution better, so I never returned to that solution.

OK then. Now, I will describe the steps of creating a dependent dropdown list one by one.

1. Cell Range Names

This is an optional step, without it we can handle this without any problems. However, I like to use names because they make the formula much easier to both write and read.

Let's assign names to the two ranges. List of all categories and working list of categories. These ranges would be A3:A5 (list of categories in the green worksheet in the first image) and G3:G15 (list of repeating categories in the purple worksheet).

To name a list of categories:

1. Select range A3:A5.
2. In the Name box (the box to the left of the formula bar), enter the name "Category."
3. Confirm with the Enter key.

Perform the same action for the category worklist range G3:G15, which you can call “Work_List”. We will use this range in the formula.

2. Create a drop-down list for a category

It will be simple:

1. Select the cell where you want to place the list. In my case it is A12.
2. From the DATA menu, select the Data Validation tool. The “Check Input Values” window appears.
3. Select List as the data type.
4. As the source, enter: =Category (image below).
5. Confirm with OK.

The result is as follows:

Dropdown list for category.

3. Create a dependent dropdown list for a subcategory

Now it will be fun. We know how to create lists - we just did it for a category. Just one question: “How do I tell Excel to select only those values ​​that are intended for a specific category?” As you can probably guess, I will be using a worksheet here and, of course, formulas.

Let's start with what we already know, that is, by creating a drop-down list in cell B12. So select that cell and click Data/Data Validation and the Data Type is List.

In the list source, enter the following formula:

View of the “Checking input values” window:

Validating input values ​​for a subcategory in a dependent dropdown list

As you can see, the whole trick dependent list consists of using the OFFSET function. Okay, almost all of it. The MATCH and COUNTIF functions help her. The OFFSET function allows you to dynamically define ranges. First, we define the cell from which the range shift should begin, and in subsequent arguments we define its size.

In our example, the range will move across the Subcategory column in the worksheet (G2:H15). We'll start moving from cell H2, which is also the first argument of our function. In the formula, cell H2 is written as an absolute reference because I assume that we will use the drop-down list in many cells.

Because the worksheet is sorted by Category, the range that should be the source for the drop-down list will start where the selected category first appears. For example, for the Food category we want to display the range H6:H11, for Transport - the range H12:H15, etc. Notice that we are moving along the H column all the time, and the only thing that changes is the beginning of the range and its height ( that is, the number of elements in the list).

The beginning of the range will be moved relative to cell H2 by as many cells down (in number) as the position number of the first occurring category in the Category column. It will be easier to understand with an example: the range for the Food category has been moved 4 cells down relative to cell H2 (starts from 4 cells from H2). In the 4th cell of the Subcategory column (not including the title, since we're talking about about a range named Work_List), there is the word Nutrition (its first appearance). We use this fact to actually determine the beginning of the range. The MATCH function (introduced as the second argument of the OFFSET function) will serve us for this purpose:

The height of the range is determined by the COUNTIF function. She counts all occurrences of repetitions in the category, that is, the word Nutrition. The number of times this word occurs, the number of positions there will be in our range. The number of positions in a range is its height. Here's the function:

Of course, both functions are already included in OFFSET function, which is described above. Also, notice that in both the MATCH and COUNTIF functions, there is a reference to a range called WorkList. As I mentioned earlier, you don't have to use range names, you can just enter $H3:$H15. However, using range names in the formula makes it simpler and easier to read.

That's all:

Download an example of a dependent drop-down list in Excel

One formula, well, not so simple, but it makes the work easier and protects against errors when entering data!

Very often, those who enter some data or simply fill out a table need to enter data that is repeated. To avoid repeating the same things, Excel has a tool for these cases – a drop-down list. Let's see how to make it

Let's do it step by step

How it works - you create a list in advance, and then in certain fields you select the desired value from the list with the mouse. Saves time and nerves.

This is easy to implement:

• First, create the list of data itself.

• Select the fields that you plan to fill with data.

• Then go to the Data tab, Data Validation – Data Type (List) command, and put a range in the Source field.

If you plan to add new data to the list, let the range also include a certain number of cells below the list.

An alternative option is to mark the range name rather than the range. In this case, you get rid of the possibility of increasing the list more than what you specified in the list. You just need to make sure that the new values ​​you enter into the range are actually included in it.

• Check the “Ignore empty cells” box.

Now, when you select a cell from those to which the command was applied, an arrow button will appear on the right.

A caveat is that with this method you can enter into a cell ONLY the values ​​contained in the list.

Nuances

A dropdown list is a cell property. And therefore, if you copy another cell and paste it into the one where there is a drop-down list, the cell properties will be replaced and the drop-down list will disappear. Accordingly, if you need the same drop-down list in another cell, you can simply copy the cell and paste it to the right address(or via special insert, you need to copy “conditions to values”).

After filling in the fields, if you don’t plan to do anything else, you can remove the drop-down list function (it happens that the arrow visually interferes when working with cells). This is done this way: select the range where the drop-down list is no longer needed, use the same command, but press the “Clear all” button. All data will be saved.

Second way

If you do not fill out repeated data, but want to do something like a questionnaire, because... table, where fields with lists are randomly arranged, it is better to use another method of creating a drop-down list.

To do this, go to the Developer tab (Excel 2007-2010). By default, this tab is not shown. To open it, you need to go to Options (Office/Excel Options/General/Show “Developer” tab on the ribbon).

• On the Developer tab there is an Insert button where there are form elements. We need a "Combo Box".

• Draw the mouse around the outline of the button, then right-click on the resulting field and select “Format Object”.

• In the first field, select a range.
• In the second field you can put a cell where you will put serial number element. This can be useful when you process the data later.
• You can also put a list of elements that will be shown. In the previous method, only 8 selection elements are automatically given, but here, although the setup is more complicated, there are more benefits.

Then click “Ok” and use it.

“The eyes are afraid, but the hands are doing”

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.

1. We make active the cell where we want to place the drop-down list.
2. 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.

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

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


3. 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.

1. 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.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E2:E9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
2. To make the selected values ​​appear below, we insert another handler code.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("H2:K2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
3. 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.

Option Explicit Option Compare Text Dim bu As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Row = 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub If Target.Column = 3 Then "number of the column in which we enter the values ​​bu = True With Me.TextBox1 .Top = Target.Top: .Text = Target.Value: .Activate End With With Me.ListBox1 .Top = Target .Top + 5 If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ (ActiveWindow.Application.Height + ActiveWindow.Application.Top) Then _ .Top = .Top - .Height + Target.Height "* ActiveWindow.Zoom / 100 .Clear End With bu = False Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Else Me.TextBox1.Visible = False: Me.ListBox1 .Visible = False End If End Sub Private Sub TextBox1_Change() If Len(TextBox1.Text) = 0 Or bu Then Exit Sub "if there are no characters to search - exit Dim x, i As Long, txt As String, lt As Long, s As String txt = TextBox1.Text: lt = Len(TextBox1.Text) "Where we look for the values ​​x = Sheets("nomenclature";).Columns(1).SpecialCells(2).Offset(1).Value "(! LANG: For i = 1 To UBound(x, 1)" поиск по первым буквам "If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" For i = 1 To UBound(x, 1) "поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(s, "~";) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then With Me.TextBox1 ActiveCell.Value = .Value .Visible = False: ListBox1.Visible = False End With ActiveCell(2, 1).Select End If End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub Application.EnableEvents = False bu = True With Me.ListBox1 ActiveCell.Value = .Value Me.TextBox1.Text = .Value Me.TextBox1.Visible = False: .Visible = False End With Application.EnableEvents = True bu = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Column = 2 Then Exit Sub If Not Intersect(Target, Range("C2:C100000";)) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("номенклатура";).Columns(1), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("номенклатура";).Range("номенклатура";).Cells(Worksheets("номенклатура";).Range("номенклатура";).Rows.Count + 1, 1) = Target End If End If End If Sheets("номенклатура";).Range("номенклатура";).Sort Key1:=Sheets("номенклатура";).Range("A1";), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal "этот код и поможет отсортировать в !} alphabetical order"End Sub

When filling cells with data, it is often necessary to limit the input to a specific list of values. For example, there is a cell where the user must enter the name of the department, indicating where he works. It is logical to first create a list of departments of the organization and allow the user to only select values ​​from this list. This approach will help speed up the input process and reduce the number of typos.

Drop-down list can be created using

In this article we will create Drop-down list using () with data type List.

Drop-down list can be formed in different ways.

A. The simplest drop-down list - entering list items directly into the Source field

Suppose in a cell B 1 need to create drop-down list to enter units of measurement. Select a cell B 1 and call Data verification.

If in the field Source indicate units of measurement separated by semicolons pcs;kg;sq.m;cub.m, then the choice will be limited to these four values.

Now let's see what happened. Select a cell B 1 . When you select a cell, a square arrow button appears to the right of the cell to select items from drop down list.

Flaws this approach: list items are easy to lose (for example, by deleting a row or column containing a cell B 1 ); It is not convenient to enter a large number of elements. The approach is suitable for small (3-5 values) immutable lists.
Advantage: Create a list quickly.

B. Entering list items into a range (on the same sheet as the drop-down list)

Items for a dropdown list can be placed in a range of EXCEL sheet and then into the field Source tool to specify a link to this range.

Let's assume that the elements of the list pcs;kg;sq.m;cub.m entered into range cells A 1: A 4 , then the field Source will contain =sheet1!$A$1:$A$4

Advantage: clarity of the list of elements and ease of modification. The approach is suitable for lists that change rarely.
Flaws: If new elements are added, you have to manually change the range reference. True, a wider range can be immediately identified as a source, for example, A 1: A 100 . But then the drop-down list may contain empty lines (if, for example, some of the elements were deleted or the list was just created). To make empty lines disappear, you need to save the file.

Second disadvantage: the source range must be located on the same sheet as drop-down list, because rules cannot use links to other sheets or workbooks (this is true for EXCEL 2007 and earlier).

Let's get rid of the second drawback first - we'll post a list of elements drop down list on another sheet.

B. Entering list items into a range (on any worksheet)

Entering list items into a range of cells that is in another workbook

If you need to move a range with drop-down list items to another workbook (for example, to a workbook Source.xlsx), then you need to do the following:

• in the book Source.xlsx create the necessary list of elements;
• in the book Source.xlsx assign to the range of cells containing the list of elements, for example ListExt;
• open the workbook in which you intend to place the cells with the drop-down list;
• select the desired range of cells, call the tool , in field Source indicate = INDIRECT("[Source.xlsx]sheet1!ListExt");

When working with a list of elements located in another workbook, the file Source.xlsx must be open and located in the same folder, otherwise you must specify the full path to the file. In general, it is better to avoid references to other sheets or use Personal macro book Personal.xlsx or Add-ons.

If you don't want to assign a name to the range in the file Source.xlsx, then the formula needs to be changed to = INDIRECT("[Source.xlsx]sheet1!$A$1:$A$4")

ADVICE:
If there are many cells with rules on the sheet Data checks, then you can use the tool ( Home/ Find and Select/ Selecting a group of cells). Option Data checking This tool allows you to select cells that are subject to data validation (specified using the command Data / Working with data / Validating data). When selecting a switch Everyone all such cells will be selected. When selecting the option These same Only those cells are highlighted for which the same data validation rules are set as for the active cell.

Note:
If drop-down list contains more than 25-30 values, it becomes inconvenient to work with it. Drop-down list displays only 8 elements at a time, and to see the rest, you need to use the scroll bar, which is not always convenient.

EXCEL does not provide font size adjustment Dropdown list. At large quantities elements, it makes sense to list elements and use additional classification of elements (i.e. split one drop-down list into 2 or more).

For example, to effectively work with an employee list of more than 300 employees, it should first be sorted alphabetically. Then create drop-down list containing letters of the alphabet. Second drop-down list should contain only those surnames that begin with the letter selected in the first list. To solve such a problem, the or structure can be used.