Related dropdown lists. Excel: Select from a list

In a programme Microsoft Excel It is possible to create a drop-down list - that is, make one cell contain several values ​​that the user can switch between. This is a very convenient tool for working with tables, in particular when it comes to checking entered data. Therefore, if you do not know how to make a drop-down list in Excel, then the instructions given in the article will help you.

First, let's look at the simplest way that will help you make a drop-down list. To implement it, do everything as in the instructions below:

Step 1. Go to the tab "Data", which is located on the top panel, then in the block "Working with data" select the data verification tool (the screenshot shows what icon it is represented by).

Step 2. Now open the very first tab "Options", and install "List" in the data type list.

Step 3. Now in the data entry field "Source" you can specify the values ​​that will make up the dropdown list. You can do this in the following ways:


On a note! In order not to enter a range of cells manually, just left-click to select the first cell with a value, put a colon and select the last cell with a value with the mouse.

After you use any of the methods and specify the value range name, you will get the result as in the picture below.

On a note! There is another way to specify a value in the source - write the name of the range in the input field. This method is the fastest, but before you use it, you need to create a named range. We'll talk about how to do this later.

Video - Creating Dropdown Lists in Excel

Dropdown list with data substitution

If your table is subject to constant changes and you don't want it to affect the contents of your dropdown list, then the following method will help you. Use it, and then editing cell data will be automatically displayed in the drop-down list. To do this, do the following:

  1. Select the range for the list with the left mouse button (in in this example this will be a list of trees), then open the tab "Home" and select menu "Format as table".

  2. After this, the style selection menu will open. Style doesn’t play any role other than visual, so choose any one to suit your taste.

  3. Next, a confirmation window will appear, the purpose of which is to verify that the entered range is correct. It is important to check the box next to "Table with headers", since the presence of a title in this case plays a key role.

  4. After completing the procedures, you will receive the following type of range.

  5. Now left-click the cell in which the drop-down list will be located and go to the tab "Data"(the previous method tells you how to do this).

  6. In the input field "Source" you need to enter a function with the syntax “=INDIRECT(“TableName[Header]”)”. The screenshot shows a more specific example.

So, the list is ready. It will look like this.

On a note! This method has a fundamental difference from the first - a ready-made one is used as a list, not a range of values. This means that any changes in the table will be reflected in the drop-down list.

Let's test this. First, let's add a new "Christmas tree" cell to our new formatted table. As you can see, the same value has been added to the list.

If you delete a cell (in this case we deleted “birch”), this will also be reflected in the contents of the drop-down list.

On a note! In this method we dealt with the so-called “smart table”. It is easily extensible, and this property is useful for many manipulations with Excel tables, including creating a drop-down list.

Dependent Dropdown Lists

To create dependent dropdown lists, follow these step-by-step instructions:

  1. First, you need to create a named range. Go to the tab "Formulas", then select "Name Manager" And "Create".

  2. Enter a name and range, then click "OK". If you previously selected the required cells with the mouse, the range will be specified automatically. Also note that the range name must be unique and cannot contain punctuation with spaces.

  3. Using the same technique, make as many named ranges as there are logical dependencies you want to create. In this example, these are two more ranges: "Shrubs" And "Herbs".

  4. Open the tab "Data"(in the first method the path to it is indicated) and indicate the names of the named ranges in the source, as shown in the screenshot.

  5. Now you need to create an additional dropdown using the same design. This list will reflect those words that match the title. For example, if you selected “Tree”, then it will be “birch”, “linden”, “maple” and so on. To do this, repeat the above steps, but in the input field "Source" enter function "=INDIRECT(E1)" . In this case "E1"– this is the address of the cell with the name of the first range. Using the same method, you can create as many interconnected lists as you need.

Now you know several ways to create a drop-down menu in . This may be useful to you in a number of cases, and especially in data verification. Choosing the right method should depend on what type of table you are working with. If this is a “one-time” table, then the first method is suitable - it is fast and easy. If the table requires constant changes, then it would be best to use a method that includes creating “smart tables” and named ranges.

Video - Related Dropdown Lists: Easy and Fast

The list element is familiar to us from forms on websites. It is convenient to select ready-made values. For example, no one enters the month manually; it is taken from such a list. You can fill out a drop-down list in Excel using various tools. In this article we will look at each of them.

How to make a dropdown list in Excel

How to make a drop-down list in Excel 2010 or 2016 using one command on the toolbar? On the “Data” tab, in the “Working with Data” section, find the “Data Validation” button. Click on it and select the first item.

A window will open. In the “Options” tab, in the “Data type” drop-down section, select “List”.


A line will appear at the bottom to indicate sources.


You can provide information in different ways.

First let's assign a name. To do this, create such a table on any sheet.

Select it and right-click. Click on the “Assign a name” command.

Enter your name in the line above.

Call the “Data Check” window and in the “Source” field, specify the name by placing the “=” sign in front of it.


In any of the three cases you will see the desired element. Selecting a value from an Excel drop-down list is done using the mouse. Click on it and a list of specified data will appear.

You learned how to create a dropdown list in an Excel cell. But more can be done.

Dynamic Excel Data Substitution

If you add some value to the range of data that is inserted into the list, then no changes will occur in it until new addresses are manually specified. To link a range and an active element, you need to format the first one as a table. Create an array like this.

Select it and on the “Home” tab, select any table style.


Be sure to check the box below.

You will receive this design.

Create an active element as described above. For the source, enter the formula

INDIRECT("Table1[Cities]")

To find out the table name, go to the Design tab and look at it. You can change the name to any other.


The INDIRECT function creates a reference to a cell or range. Now your element in the cell is bound to the data array.

Let's try to increase the number of cities.


The reverse procedure - substituting data from a drop-down list into an Excel table - works very simply. In the cell where you want to insert the selected value from the table, enter the formula:

Cell_address

For example, if the list of data is in cell D1, then in the cell where the selected results will be displayed, enter the formula

How to remove (delete) a drop-down list in Excel

Open the drop-down list settings window and select "Any value" in the "Data type" section.



The unnecessary element will disappear.

Dependent Items

Sometimes in Excel there is a need to create several lists when one depends on the other. For example, each city has several addresses. When selecting the first one, we should receive only the addresses of the selected locality.


In this case, give each column a name. Select without the first cell (title) and right-click. Select "Name".

This will be the name of the city.


When naming St. Petersburg and Nizhny Novgorod, you will receive an error, since the name cannot contain spaces, underscores, special characters etc.


Therefore, we will rename these cities with an underscore.


We create the first element in cell A9 in the usual way.


And in the second we write the formula:

INDIRECT(A9)


You will first see an error message. Agree.

The problem is that there is no selected value. As soon as a city is selected in the first list, the second one will work.

How to Set Up Dependent Dropdown Lists in Excel with Search

You can use a dynamic data range for the second element. This is more convenient if the number of addresses grows.
Let's create a drop-down list of cities. The named range is highlighted in orange.


For the second list you need to enter the formula:

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

MATCH returns the number of the cell with the city selected in the first list (E6) in the specified area SA:$A.
COUNTIF counts the number of matches in a range with the value in the specified cell (E6).


We got linked dropdown lists in Excel with a match condition and a range search for it.

Multi-select

Often we need to get multiple values ​​from a data set. You can display them in different cells, or you can combine them into one. In any case, a macro is needed.
Right-click on the sheet label at the bottom and select View Code.


The developer window will open. You need to insert the following algorithm into it.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) 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


Please note that in the line

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

You should enter the address of the cell with the list. For us it will be E7.

Return to your Excel worksheet and create a list in cell E7.

When selected, the values ​​will appear below it.

The following code will allow you to accumulate values ​​in a cell.

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

As soon as you move the pointer to another cell, you will see a list of selected cities. To read this article.


We showed you how to add and change a drop-down list in an Excel cell. We hope this information helps you.

Have a great day!

When creating tables, it is sometimes very convenient to use drop-down (in other words, drop-down) lists. Excel 2010 allows you to do this in several ways. Let's look at them.

Method 1: Create a Dropdown List in Excel 2010 Using the Data Validation Tool

This method is considered standard because it is simple and convenient.

1. On free space sheet, write down all the elements of the drop-down list in a column, each element in its own cell.

2. Give the cell range a name. For this:

  • Click the top cell of the list and, holding down the left mouse button, drag the cursor down until the entire list is selected.
  • place the cursor in the “Name” field, to the left of the formula bar;
  • enter a name for the list and press Enter.

Please note that the list name must always begin with a letter and not contain spaces.

3. Select the cell in the table where the drop-down list will be placed.

4. Open the “Data” tab and click the “Data Check” button. In the window that opens, go to the “Options” tab. In the "Data type" drop-down list, select "List".

5. In the “Source” line you need to indicate the address where the elements will be taken from the list being created. The address will be the name you give to the range of cells. You can set the address in several ways.

  • Enter it manually, placing an equal sign in front of it, for example, “=month”. Letter case is not important.
  • By clicking the mouse in the “Source” line (to activate), select with the cursor all the list elements in the table.

6. If you need to create a message for input, open the tab of the same name. Write text that will appear next to the dropdown cell when it is selected. On the next tab - “Error message”, in the same way you can write text notifying about errors.

7. Confirm your entry by clicking “OK” and the drop-down list is ready. To open it, click on the down arrow button that appears next to the cell containing the list.

Method 2. Quickly create a drop-down list

A drop-down list in Excel 2010 can be created with a single keyboard shortcut, but it can only be located in one place - in the cell below the list items.

1. List in a column all the elements of the future drop-down list.

2. Select the cell located under the last element and press the key combination “Alt” + “down arrow” - the list will be created. This method will allow you to set the cell to the value of one of the elements.

Method 3: Create a dropdown list as a control

To use this method, enable the display of the “Developer” tab: open the menu “File” - “Options” - “Customize Ribbon”. In the “Main Tabs” column, check the “Developer” box. Confirm the action by clicking “OK” - the tab will be created.

1. List the elements of the future list in a column.

2. From the “Insert” menu of the “Developer” tab, select “Form Controls” - “Combo Box”.

3. Draw your future drop-down list on the sheet with the cursor. Right-click on it and select “Format Object” from the menu.

4. The value of the “Generate list by range” field should be a list of elements - select it with the cursor, and the field will be filled in automatically. In the “Link to cell” field, indicate the address of the cell where the serial number of the selected element will be shown. Select a cell and click on it. The Number of List Lines field allows you to configure how many items will be displayed when the list is expanded.

5. Confirm your entry and click OK. The list will be created.

Method 4: Create a Dropdown List as an ActiveX Control

The most complex method, but with the most flexible settings.

1. Create a list using the method described above.

2. From the “Insert” menu of the “Developer” tab, select “ ActiveX controls" - "Combo box".

3. Draw the future drop-down list on the sheet.

4. The option that allows you to edit the drop-down list is called “Design Mode”. If this mode active—the button of the same name will be highlighted in the “Controls” section, next to the “Insert” button. If the button is not highlighted, the editing mode is disabled.

5. To set the list parameters, click the “Properties” button in the same section. The “Properties” settings window will open. Both tabs of this window contain the same settings, sorted in the first case alphabetically, in the second - by categories.

6. Most settings can be left as default, but the most necessary ones are listed below.

  • ListRows - analogous to the “Number of rows in the list” value, will show how many rows will be displayed.
  • Font—font settings. Allows you to select the font and its style.
  • ForeColor - selection of font color from the table.
  • BackColor - background color.
  • ListFillRange - location of the list of elements in the format: sheet ("!" - separator) and a range of cells. For example: Sheet2!D2:D6. Registered manually.
  • LinkedCell - link to a cell. Manually specify the address of the cell where the serial number of the selected list item will be displayed.

7. Save the settings and deactivate the design mode by clicking on the button of the same name. The drop-down list will be created and you can check how it works.

Many users do not even realize that the well-known Excel spreadsheet editor has such functions and tools that go far beyond the main purpose of using the program - editing tables. This article will talk about the select from option. In other words, we will tell 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, then the easiest way is to use this method, which involves simply creating a drop-down list. By the way, we will talk about two of its variations, so read to the end to understand everything.

Step 1: prepare the data

You must first create a table in a separate range of cells with data that will be in the drop-down list in the future. Let's look at everything using products as an example. So, we have a list of seven products, or more precisely, products. We will create this table a little 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 separate sheet. It won't matter.

Step 2: enter the range name

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

  1. Select the cells that contain the product names in this case.
  2. Right-click (RMB) on the selection.
  3. Select the "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 Dropdown List

Now you can move directly to using the list selection 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. Go to the Data tab.
  3. In the “Working with Data” tool group, click the “Data Validation” button.
  4. In the window that appears, on the “Parameters” tab, select the “List” value from the “Data type” drop-down list.
  5. Enter the name of the previously created range of cells in the “Source” field, after placing an equal sign. In our case - “=Products”.
  6. Click OK.

Immediately after this, 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: Creating a drop-down list through the "Developer" menu

It is quite possible that the previous instructions seemed incomprehensible to you, and you encountered difficulties when creating an element for selecting a value from a list in a table cell in Excel. The second implementation method may be a worthy alternative.

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

Step 1: enable the “Developer” menu

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

  1. Click on the "File" button.
  2. Click the “Options” button.
  3. In the window of the same name that appears, go to the “Customize Ribbon” section.
  4. In the “Main Tabs” area, check the box next to “Developer”.
  5. Click OK.

The desired toolbar is activated, now you can start creating a list.

Step 2: insert the dropdown list

You need to create the “Dropdown List” element itself. For this:

  1. Go to the added "Developer" tab.
  2. On the worksheet, create a list of products that will be used to create the drop-down list.
  3. Click 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 element 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 “Design Mode” button.
  2. Then click the “Control Properties” button located next to it.
  3. In the properties window that appears, in the ListFillRange column, enter the range of cells in which the items of the future drop-down list are located.
  4. Now right-click on the drop-down list and select “ComboBox Object” in the menu, and Edit in the submenu.

Immediately after this, the specified items will be added to the drop-down list. This is how easy it is to select from a list in Excel using the second method.

Method 3: Create a Linked List

For selecting multiple values, a drop-down list in Excel is best suited, but sometimes there is a need to interconnect several such lists. Fortunately, the program allows you to do this, and further detailed information will be provided. step-by-step instruction with a detailed description of all actions.

Step 1: create an additional list

The first thing you need to do is create a basic dropdown list. We will not dwell on this for long, since the design is completely similar to that described in the first method. Let's just say that we will associate the name of the product with its weight. It is recommended to create range names with product measures (g, kg, ml, l).

Step 2: Link the first list with the second

Well, now let's move directly to the main thing - to creating a second “Select from a 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 Input 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 Source input box, enter the INDIRECT formula that references the first list. In this case, it will look like this: “=INDIRECT($B3)”.
  5. Click OK.

The second list has been created. It is tied to the first, which means that by selecting the value in this case of a product, you will also need to select its measure. To avoid creating the same lists in other cells, select those already added and drag the lower right corner of the selection down, thereby filling all the necessary cells.

Conclusion

The option to select from a list in Excel is quite useful, as can be understood from the above. But what is more important is that to create it you do not need to have deep knowledge of using a spreadsheet 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 performing them.

How to make a drop-down list in Excel, today we will consider this question in practice. With the help of this article, we will learn how to make not only drop-down lists, but also simple, dependent, expanding ones. Then copy the made list into the Excel program and paste it.

Why do you need lists in Excel?

Hello, friends! As far as we know, Excel is a very convenient software for recording and maintaining various records in a document. In it you can fill in data about your income and expenses, calculate formulas, and so on. But some people ask the question, how to make a drop-down list in Excel? Why is it needed?

In fact, drop-down lists are very necessary if you often work with tables in this program and fill it with various numbers and symbols. They are needed to save your work time and not spend it on filling out tables for a long time. In practice, the drop-down list works efficiently and remembers all the data you previously recorded in the tables. Next, we will create such a list, and make other similar lists that will help you simplify your work in this program.

Let's create a simple drop-down list. Open the Excel program and write some words or numbers in the first table. Select one column with words and then, at the top of the program control panel, click on the “Data” section and then “Data Check” (Screen 1).

Then you should click on “Source” and once again select the column we filled in, only after that click “OK”. So, we now know how to make a drop-down list in Excel; it is also called a “pop-up”. After its creation, it looks like this (Screen 3).

You can click on the arrow and select previously written words in the table. We considered a simple option. Further, there will be more lists, but with a more complicated version.

Dropdown list from another sheet in Excel

If you work from different sheets of the Excel program, then a drop-down list from another sheet will greatly help us in further work. It's quite simple to make. First, select the desired column of data in the program, and press the “Data Check” button again on the second sheet. Next, go to the first sheet and in the “Source” section, then you should select the cells and click on the “OK” button. After this, the drop-down list should be moved to another sheet.

Dependent Dropdown Lists in Excel

What are dependent dropdown lists? These are lists that are created from two sources. For example, there are two tables in which you have written down the names of your acquaintances and friends. And in the next column, a dependent drop-down list will be displayed, which shows the same data that was recorded in other tables. Next, we will create such dependent lists and expanders.

A drop-down list in Excel can be created using a special function, which we will talk about below. It is somewhat similar to the drop-down one, only using the given “OFFEST” value. So, let's move on to the creation process.

Do all the same actions that we did in the first part of the article and then in the “Source” specify the following formula “ = OFFSET(A$2$;0;0;5”, then click “OK” to create a drop-down list in .

Above we talked a little about dependent lists. Now, let's create them. But first, create and fill two columns with any data. Next, select these columns in the tables, and click the button at the top - “Create from selected fragment” (Screen 4).

Next, we create a drop-down list (the creation process was shown above in the article). Then, go to the “Sources” function and write the following command in the field - =INDIRECT($D$2). It will help you create dependent lists. Click the “OK” button, after which another column should appear in the table, with a dependent pop-up list.

How to copy a dropdown list in Excel

Copying a drop-down list will not be difficult for users. To do this, select the cell we need with the left mouse button and press CTRL+C (Copy). Then, click on any column in the Excel table. And right-click the command – “Paste Special”. In this insert function you need to set – “Conditions on values” and click on “OK” to save the changes.

After this, press the keys simultaneously again - CTRL + V, which means “Paste” and subsequently, the entire copied drop-down list in this program will be successfully pasted.

Conclusion

So, in the article we looked at the question of how to make a drop-down list in Excel and at the same time made other lists. This instruction has been tested in practice. Perhaps this tool from the Excel program will help you resolve some issues when filling out tables or when working with documents. Use these functions and work with Excel program will become fast and convenient. Thank you for your attention, and good luck to you!

Receive new blog articles directly to your email. Fill out the form, click the "Subscribe" button

mob_info