Dropdown button. Create Linked Dropdown Lists in Excel - The Easiest Way! Creating a Dropdown Using Developer Tools

IN Microsoft program Excel has the ability 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

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 to work 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.

The disadvantage of 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 “Check 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 data entry

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 that is captured in a specific range of 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: =OFFEST(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 the formula to work correctly, 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 drop-down 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, maintaining the original list format (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;
right-click => 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 highlight all the cells with the drop-down list on the sheet. This way you can bring all the lists to a common format at once, 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.

Good afternoon, dear reader!

In this article, I would like to talk about what a drop-down list in a cell is, how to make it, and, accordingly, what is it for?

This is a list of fixed values ​​that are only available from a specified range of values. This means that the cell you specify can only contain data that corresponds to the values ​​of the specified range; data that does not correspond will not be entered. In a cell, you can select the values ​​that a fixed list in the cell offers.

Well, let's look at creating drop-down lists and why it is needed:


I personally use the dropdown list all the time for all 3 reasons. And it greatly simplifies my work with data; I deliberately reduce the possibility of entering primary data to 0%.

Well, here are 2 questions, what and why, I told you, but we’ll talk about how to do this below.

And we will create a drop-down list in a cell in several stages:

1. Determine the range of cells in which we will create a fixed list.

2. Select the range we need and select the item in the menu “Data” - “Data check”, in the context window that appears, select the item from the specified selection "List".


3. In the line unlocked below, indicate the range of data that should be in our drop-down list. Click "OK" and the job is done.

In older versions of Excel, there is no way to create a drop-down list in a cell using data from other sheets, so it makes sense to create lists in the same sheet and hide them. Also, if necessary, you can create a vertical list - a horizontal one using the feature.

And that's all for me! I really hope that all of the above is clear to you. I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it!

The progress of mankind is based on the desire of every person to live beyond his means
Samuel Butler, philosopher

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 do in a cell Excel list selection, 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.

When filling out a table in Excel, the data is often repeated. For example, you write the name of the product or the full name of the employee. Today we will talk about how to make a drop-down list in Excel so that you don’t constantly enter the same thing, but simply select the desired value.

There is the easiest way to cope with the task. You right-click on the cell under the column with the call data context menu. Here we look for the “Select from drop-down list” item. The same action can be easily performed using the combination Alt + down arrow.

That's just this method will not work if you need to make such an object in Excel in another cell and in 2-3, etc. before and after. If necessary, use the following option.

Traditional way

Select the area of ​​the cells themselves from which you will create a drop-down list, then proceed:

Insert/Name/Assign (Excel 2003)

In the latest versions (2007, 2010, 2013, 2016) go to:

Formulas/Defined Names/Name Manager/New

Enter any name and tap OK.

Then select the cells (or several) where you plan to insert the list of specified fields. Go to the menu:

Data/Data Type/List

In the “Source” section, enter the previously written name, simply mark the range. You can copy the resulting cell to any place; it will already have a menu of specified table elements.

Additionally, you can stretch it to create a range. By the way, if the information in it changes, the list information will also change; it is dynamic.

How to make a drop-down list in Excel: using management methods

When you use this option, you insert a control that represents the data range itself. For this:

  • firstly, look for the “Developer” section (Excel 2007/2010), in other editions you activate it yourself through the “Customize Ribbon” parameters;

  • secondly, go to the section, click “Insert”;
  • thirdly, select “Field...” and click on the icon;
  • draw a rectangle;

  • right-click on it and click Format Object;

  • look for “Form…”, select the required boundaries;
  • mark the area where you want to set the serial number of the element in the list;
  • Click OK.

How to create a drop-down menu in Excel: using ActiveX elements

The steps are similar to those described above, but we are looking for “Field with ActiveX”.

The main differences here are that the ActiveX special element can be in 2 variants - debugging mode, which allows you to change parameters, and input mode, which only allows you to select information from it. You can change the mode using the key Design Mode In chapter Developer. Using this method, you can customize the color, font, and perform quick searches.

Additional features

IN Excel program It is possible to create a linked drop-down list. That is, when you select a value, you can select the parameters it needs in another column. For example, you choose a product, and you need to mark the unit of measurement, for example, gram, kilogram.


First of all, you should make a sign with the lists themselves, and then separate windows with the names of the products.


For the second, we activate the information check window, but in the “Source” item we write “=INDIRECT” and the address of the 1st cell. Everything worked out.

After this, so that the windows below are supplemented with the same properties, select the upper section and drag everything down with the mouse pressed. Ready.

How to make a drop-down list in Excel? As you can see, this is easy to do using any of the above methods. Just choose the most optimal one for yourself. And the choice will depend on the purpose of creation, purpose, area of ​​use, amount of information and other things.

mob_info