How to make a drop-down list in the excel in the cell and in the table: the instruction

In the office software package from Microsoft, in addition to all known Word, Excel( Exel) is often used. It is presented from the very beginning in the form of a sheet with a table and cells, it is very convenient for filling the nomenclature, reports, the list of goods, etc. The tool is multifunctional, so some features are not immediately clear to the user.

How to create a drop-down list in excel

When filling large documents with many parameters, there is a desire to somehow simplify the process. For this, it may be useful to know how to create a drop-down list in excel. This can sometimes eliminate the need to enter the same parameters. For example, when you fill out company brand names that are repeated, then in the next cell of the page( without omissions), press Alt + down arrow. You will see a list of previously entered data for this column, from which you can select the desired option.

Another way to make a drop-down list in Excel is using ActiveX tools. To use them, you must first make the "developer" tab available. To do this, you need:

1. Open the "file" item at the top.
2. Go to the "Options" section.
3. In the "Configure ribbon" tab, tick the "Developer" checkbox.

After that an additional field will appear in the top menu called "Developer", where there will be a button "Paste".There will be an option to select the "Combo Box" in the "ActiveX" subsection. You will be able to draw a field anywhere in the Excel document, where you need to make a drop-down list. Next, this element should be configured:

1. In the same tab, click the "Design Mode" tab, click the "Properties" button.
2. Many parameters will open, but the main ones are only three that you need to configure.
3. ListFillRange - here it is necessary to set the range of the values ​​used.
4. ListRows - here you specify the amount of data for the drop-down menu.
5. ColumnCount - this specifies the number of columns to be used.

With multi-select function

For convenience of filling it is possible to make a menu with multi-select when there is an option of adding several values ​​for cells from one list. This is considered a classic option, but some additional tuning is necessary with the help of the program code. To make such a menu it is possible so:

1. Create a list of data from which values ​​will be taken.
2. Select the cells for which you want to make a multi-select menu.
3. In the "Data" tab at the top of the tool menu, click the "Validate data" button.
4. In the "Options", set the value to "List", and in "Source" specify the range of cells from which the values ​​will be taken.

Opposite the cells there will be a possibility of multi-selection, the data from them will be filled on the same line automatically. For the function to work correctly, you need to use the program code, which will automatically add values ​​to the same line. To do this, right-click on the list label and select Source Code. Without this piece in the programming language, the addition function will not work. The Visual Basic Editor window will appear, paste the following piece of code:

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

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

With

filling This is another simple way to create a standard list that will simplify the process of entering table data. To do this, you should use the "smart program tables" that are programmed in Excel. With their help, you can easily change and format all the rows with the filling. Such a list can be made as follows:

1. Select all cells that will be used for substitution.
2. Next, click on the main tab button "format as a table".
3. The editor of Excel will allow you to change the columns of your choice.

Drop-down list in cell excel

The above is the simplest way to make a drop-down list in Excel. There are more complex uses for this tool. Choosing from it will avoid mistakes when typing, list and speed up filling. This method will add a drop-down menu on any part of the document and take data even from the other end of the table. The drop-down list is created using the following algorithm:

1. Create a list of values ​​that will be used for substitution.
2. Select all the elements of the column and right-click on them.
3. In the menu, select "Assign name. ..".
4. In the window that opens, specify a name that will be used in the following formula. It should start with the letter of the alphabet, do not have spaces.
5. Select the cell or cells that will contain the drop-down menu. Go to the "Data" tab and click on "Check Data".
6. In a new window it is necessary to select "List" in the data type item, and below in the source point put the "=" sign and enter the name that was specified in step 4 of the instruction.

Video Tutorial: how to make a drop-down list in excel