Create a drop-down list. Structuring (grouping) data on a sheet
Make a dropdown list in Excel
Just. You can make linked dropdown lists in Excel .
When the list in the second column depends on the value in the cell in the first column. How to do V Excel dependent dropdown lists, Read the article "Linked drop-down lists in Excel".
You can make sure that the letters of the alphabet appear first in the drop-down list; by clicking on the selected letter, a list of words and surnames starting with that letter will appear. Read about this in the article "Related dropdown list in Excel alphabetically" .
You can make a dropdown list in Excel without additional list with data. See "How to make a drop-down list in Excel without a list."
When we create complex tables, questionnaires, survey sheets, and other documents, we can install a drop-down list in the cells. This is when we click on a cell and a list appears from which we select the right option words For example, like this.
Here, the names of the products are listed in the drop-down list.
So, first we create a list in a column, somewhere not in this table. We sign the column - this will be the future name of the range. This is convenient to do when there are several different lists.For more information on how to create a range and assign a name to it, see the article “Range in Excel» .
The list can also be written in a column of the same table. But then, hide the column, the list will work. To find out how to hide a column, see the article "How to hide columns in Excel".
All. We have a list, we have given a name to this range. Now we install this list in a cell.Place the cursor in the cell in which we will create the drop-down list. Go to the “Data” tab in the “Working with Data” section, click on the “Data Check” button, select “Data Check”. In the dialog box that appears, in the “data type” line, select “List”.In the “Source” line, indicate the name of our range,but before the name we put an “equal” sign. It turned out like this.
In this way you can make unlinked multi-level dropdown lists in Excel. For example, we have such a table with data.
In the cells of row 1 we will make a series of drop-down lists. Click on cell A1 and, using the “Data Check” function, set the drop-down list. The dialog box was filled in like this.
And so we do all the drop-down lists. The result is a table like this.
Now, to create a drop-down list in cell A1, instead of a range of cells in column J, write the name of this range.
How to make one list in Excel for a drop-down list from existing data, read the article “Creating a list in Excel without repetitions”.How to remove Excel dropdown list.
You need to select cells with drop-down lists. And, having selected the “Data check” function, select “Any value”.
We put a drop-down list in a cell so that users fill out the form or table correctly. But there are several other ways to check the correctness of the entered data. Read about this in “Validating Data in Excel.”
In Excel, you can make settings so that the list is filled in automatically. WITHSee the article “Automatically fill a list in Excel" A drop-down list in a cell allows the user to select only specified values for entry. This is especially useful when working with files structured like a database, where entering an inappropriate value into a field can lead to undesired results.
So, to create a drop-down list you need:
1. Create a list of values that will be provided to the user to choose from (in our example this is a range M1:M3), then select the cell in which the drop-down list will be (in our example this is the cell K1), then go to the " tab Data", group " Working with data", button " Data checking"
2.
Select " Data type" -"List" and indicate the range of the list 
3.
If you want to prompt the user about his actions, then go to the " tab Message to be entered" and fill in the title and text of the message 
which will appear when you select a cell with a drop-down list 
4.
You can also optionally create a message that will appear when you try to enter incorrect data 
If you do not do steps 3 and 4, then data checking will work, but when the cell is activated, a message to the user about his intended actions will not appear, and instead of an error message with your text, a standard message will appear.
5.
If the list of values is on another sheet, then you will not be able to create a drop-down list using the above described method (up to Excel 2010). To do this, you will need to give the list a name. This can be done in several ways. First: select the list and click right click mice, in context menu select " Assign a name"
For Excel versions below 2007, the same steps look like this: 
Second: use Name Manager(Excel versions above 2003 - tab " Formulas" - group " Specific names"), which in any version of Excel is called by a keyboard shortcut Ctrl+F3.
Whatever method you choose, in the end you will have to enter a name (I named the range with a list list) and the address of the range itself (in our example this is "2"!$A$1:$A$3)
6.
Now in the cell with the drop-down list, enter the name of the range in the "Source" field 
7.
Ready! 
To complete the picture, I’ll add that the list of values can be entered directly into the data check, without resorting to placing the values on a sheet (this will also allow you to work with the list on any sheet). This is done like this: 
That is, manually, through ; (semicolon) enter the list in the field " Source", in the order in which we want to see it (values entered from left to right will be displayed in the cell from top to bottom).
With all its advantages, the drop-down list created in the manner described above has one, but very “bold” disadvantage: data verification only works when you directly enter values from the keyboard. If you try to paste into a cell with data verification values from the clipboard, i.e. copied previously in any way, then you will succeed. Moreover, the pasted value from the buffer WILL REMOVE DATA CHECKING AND DROPPING LIST FROM THE CELL into which the previously copied value was pasted. Avoid it regular means Excel is not possible.
Microsoft Excel– an excellent tool for working with tabular data. With its help, you can quickly create a suitable table and fill it with data. At the same time, Excel simplifies not only working with data in a table, but also the process of filling out the table itself.
This material will focus specifically on filling out the table. Here you can learn how to make a drop-down list in Excel, which will allow you to fill the table with data much faster. The instructions will be relevant for Excel 2007, 2010, 2013 and 2016.
The easiest way to make a drop-down list in Excel is to use the data validation function. To create this dropdown list you first need to do regular list with data and place it in an Excel document. Such a list can be placed on the same document sheet where the drop-down list will be located, or on any other sheet.
So, first we create a list of data that should be in the drop-down list and place it in any convenient place in the Excel document. For example, you can place such a list behind the print area or on another sheet of the Excel document.
After that, select the cell in which you want to make a drop-down list. Select this cell with the mouse and click on the “Data Check” button on the “Data” tab.
After this, the “Checking entered values” window will open. In this window, you first need to open the “Data Type” drop-down list and select the “List” option there.
Then you need to fill in the “Source” line.
To do this, place the cursor in the “Source” line and then select with the mouse the list of data that should be in the created drop-down list.
After specifying the source, close the “Checking entered values” window by clicking on the “Ok” button. The drop-down list in Excel is made and can be checked.
But now in this cell You can only enter the data that is present in the drop-down list. And if you try to enter an incorrect value, you will receive an error message.
If you want to leave the ability to enter data into a cell that is not contained in the drop-down list, then you need to select the cell with the drop-down list and click on the “Data Validation” button. Next, in the window that opens, you need to go to the “Error message” tab and uncheck the “Show error message” function.
After saving these settings, you can enter into the cell not only the data that is available in the drop-down list, but also any other data that you need.
How to make a dropdown list with data added
The drop-down list option described above is quite convenient. But, if you want to regularly add new data to it, then this option will not work, since after each addition of data you will have to change the range that is indicated in the “Source” field. You can solve this problem using the Smart Tables feature, which appeared in Microsoft Excel 2007.
To do this, you need to make a list with data, exactly as described above. The only difference is that now the list must have a title.
After creating a list, you need to select it and use the “Format as Table” button on the “Home” tab to apply any style to the list.
After selecting a style, the “Format Table” window will appear. Here you need to check the box next to the “Table with header” function and click “Ok”.
As a result, you should have a table with data similar to the one in the screenshot below.
Now you need to select the cell in which you want to make a drop-down list and click on the “Data Validation” button on the “Data” tab. In the window that opens, you need to select “Data type – List”, and then place the cursor in the “Source” line and select with the mouse the list with the data that should be used for the drop-down list (there is no need to select the list title).
This method of creating drop-down lists with the ability to add new rows was tested in Excel 2010, but it should also work in Excel 2007. As in more modern versions Excel, such as Excel 2013 and Excel 2016.
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. 
When working in Microsoft Excel in tables with repeating data, it is very convenient to use a drop-down list. With it, you can simply select the desired parameters from the generated menu. Let's find out how to make a dropdown list in different ways.
The most convenient and at the same time the most functional way to create a drop-down list is a method based on constructing a separate list of data.
First of all, we create a template table where we are going to use a drop-down menu, and also make a separate list of data that we will include in this menu in the future. This data can be placed either on the same sheet of the document or on another if you do not want both tables to be visually located together.
We select the data that we plan to enter into the drop-down list. Right-click and select “Assign a name...” from the context menu.
The name creation form opens. In the “Name” field, enter any convenient name by which we will recognize this list. But this name must begin with a letter. You can also enter a note, but this is not required. Click on the “OK” button.
Go to the “Data” tab Microsoft programs Excel. Select the area of the table where we are going to use the drop-down list. Click on the “Data Check” button located on the Ribbon.
A window for checking the entered values opens. In the “Parameters” tab, in the “Data type” field, select the “List” option. In the “Source” field we put an equal sign, and immediately without spaces we write the name of the list that we assigned to it above. Click on the “OK” button.
The dropdown list is ready. Now, when you click on the button, a list of parameters will appear for each cell of the specified range, from which you can select any one to add to the cell.
Creating a Dropdown Using Developer Tools
The second method involves creating a drop-down list using developer tools, namely using ActiveX. By default, the developer tools features are missing, so we will first need to enable them. To do this, go to the “File” tab of Excel, and then click on “Options”.
In the window that opens, go to the “Customize Ribbon” subsection and check the box next to the “Developer” value. Click on the “OK” button.
After this, a tab called “Developer” appears on the ribbon, where we move. Let's talk at Microsoft Excel list, which should become a dropdown menu. Then, click on the “Insert” icon on the Ribbon, and among the elements that appear in the group “ ActiveX element» select “Combo box”.
Click on the place where the cell with the list should be. As you can see, the list form has appeared.
Then we move to "Design Mode". Click on the “Control Properties” button.
The control's properties window opens. In the “ListFillRange” column, manually enter the range of table cells separated by a colon, the data of which will form the drop-down list items.
The drop-down list in Microsoft Excel is ready.
To make other cells with a drop-down list, simply stand on the lower right edge of the finished cell, press the mouse button, and drag it down.
Related Lists
Also in Excel program You can create linked dropdown lists. These are lists where when you select one value from the list, in another column you are asked to select the corresponding parameters. For example, when choosing potatoes in the list of products, you are asked to select kilograms and grams as measurements, and when choosing vegetable oil, liters and milliliters.
First of all, let's prepare a table where the drop-down lists will be located, and separately make lists with the names of products and measures of measurement.
We assign a named range to each of the lists, as we did earlier with regular drop-down lists.
In the first cell, we create a list in exactly the same way as we did earlier, through data verification.
In the second cell we also launch the data verification window, but in the “Source” column we enter the function “=INDIRECT” and the address of the first cell. For example, =INDIRECT($B3).
As you can see, the list has been created.
Now, so that the lower cells acquire the same properties as the previous time, select the upper cells, and while holding down the mouse button, drag them down.
That's it, the table has been created.
We figured out how to make a drop-down list in Excel. In the program you can create both simple drop-down lists and dependent ones. In this case, you can use various methods creation. The choice depends on the specific purpose of the list, the purpose of its creation, the scope of application, etc.