How to make a dropdown list in Excel
Office Suite from Microsoft in addition to all the known Word is frequently used Excel (Exel). From the very beginning presented in the form of a sheet with tables and cells, very convenient for filling items, reports, list of goods etc. multifunctional Tool, therefore some features not immediately obvious to the user.
How to create drop down list in excel
When filling in large documents with multiple parameters there is a desire to simplify the process. This may be useful knowledge on how to create a list in excel. Sometimes it may save you from having to enter the same settings. For example, when you fill in the names of the brands, which are repeated in the next cell of the page (no spaces) press Alt+down arrow. You will see a list of the data entered in this column under which there is an opportunity to choose the necessary variant.
Another way how to make dropdown list in Excel – using tools ActiveX control. To use them you must first enable the developer tab. To do this:
- Open the top item is «file».
- Go to «Settings».
- In the tab «Customize ribbon» and check «Developer».
After that will appear an additional field in the top menu called «Developer», where the «Insert» button. There will be the opportunity to select the «combo Box» in the section «ActiveX». You will be able to draw a box anywhere on the Excel document where you want to make a drop-down list. Further, this element should be configured:
- In the same tab click on the tab «designer», click «Properties».
- Opens a lot of options, but the main ones are only three that you should adjust.
- ListFillRange – it is necessary to set the range of used values.
- ListRows: this indicates the amount of data for drop-down menu.
- ColumnCount – specifies the number of columns that will be used.
For easy filling there is a possibility to make a menu with multi-selection when there is the option to add multiple cell values from one list. Considered a classic, but need some additional configuration through code. To make such a menu, you can:
- Create a list of data from which values will be taken.
- Select the cells that you want to make a menu with multi-selection.
- In the tab «Data» in the top tool bar click on the «Test data».
- In «Options» set to «List» and «Source», specify the range of cells from which to take values.
Opposite the cells is the possibility of multi-selection, the data from them will be populated on the same row automatically. Function to work correctly, you need to use a code that will automatically add the value for the same string. For this shortcut list, click the right mouse button and choose Source. Without this piece the programming language of the function add will not work. A window will appear in the Visual Basic editor, 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
Target.End(xlToRight).Offset(0, 1) = Target
Application.EnableEvents = True
With the filling
This is another easy way to create a standard list that will simplify the process of entering data into tables. To do that, you need to use a «smart table» program, which is programmed in Excel. With their help you can easily edit and format all the rows with content. To make such a list as follows:
- Select the cells that will be used for lookup.
- Then you should click on the home tab, in the button «format as table».
- Editor Excel will allow you to change the columns according to your desire.
The drop-down list in an excel cell
The above described the easiest way how to make dropdown list in Excel. There are more sophisticated ways of using this tool. A selection from it to avoid typing errors, misprints and speed up filling. This method will allow you to add dropdown menu on any part of the document and make the data even from the other end of the table. The drop-down list is created according to the following algorithm:
- Create the list of values that will be used for the lookup.
- Highlight everything in the column and click the right mouse button on them.
- From the menu, choose «define name…».
- In the window that appears, enter a name that will be used in later formula. It must begin with the letters of the alphabet, not to have gaps.
- Select the cell or cells that will contain the drop-down menu. Go to the tab «Data» and click on «Test data».
- In the new window, select the item data type «List» and below in item source to put the sign «=» and enter the name that you specified in step 4 of the instructions.