Create a drop-down list. Creating a Dropdown List in Excel
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 you can enter into this cell only 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.
If you have a list of data that you want to group and summarize, you can create an outline up to eight levels deep, each with one group defined. Each inner level, represented by a higher number in the structure symbols, displays details for the previous outer level, represented by fewer in structure symbols. Using the outline, you can quickly display total rows or columns, and also display detailed data for each group. You can create a row structure (as shown in the example below), a column structure, or a row and column structure.
|
A structured row of sales information grouped by geographic area and month, displaying multiple total and detail rows. |
1. To display the level rows, click the corresponding structure symbols. 2. Level 1 contains the sum of sales for all detail lines. 3. Level 2 contains the sum of sales for each month in each region. 4. Level 3 contains rows with detailed data (in in this case lines 11 to 13). 5. To expand or collapse data in an outline, click the outline symbols and . |
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 the dropdown Excel list happens with 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
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 only get the addresses of the selected settlement.
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, let's rename these cities by putting 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
Can be used dynamic range data 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 Excel sheet 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 told you how to add and change a dropdown list in Excel cell. We hope this information helps you.
Have a great day!
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: ActiveX element 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. 
To make it easier for users to work with a worksheet, add drop-down lists to the cells so they can select the item they want.
Select the cell on the worksheet where you want to place the drop-down list.
On the ribbon, open the tab Data and press the button Data checking.
Note: If the button Data checking unavailable, the sheet may be protected or shared. Unlock specific areas of a protected workbook or unblock general access to the sheet, and then repeat step 3.
On the tab Options in field Data type select item List.
Click the field Source and highlight the list range. In the example, the data is on the Cities sheet in the range A2:A9. Note that the header row is not in the range because it is not one of the options available for selection.
If you can leave the cell blank, select the checkbox Ignore empty cells.
Check the box List acceptable values
Open the tab Message to be entered.
Open the tab Error message.
Don't know which option to select in the field View?
On a new worksheet, enter the data you want to appear in the drop-down list. It is desirable that the list items be contained in an Excel table. If this is not the case, you can quickly convert the list to a table by selecting any cell in the range and pressing CTRL+T.
Notes:
Working with a Dropdown List
Once you create your dropdown, make sure it works the way you want it to. For example, you might want to check whether you need to change the column widths and row heights to ensure that all records are displayed.
Downloading examples
We suggest downloading a sample book with several examples of data verification, similar to the example in this article. You can use them or create your own data verification scripts. Download Excel data validation examples
You can enter data faster and more accurately by limiting the values in a cell to the options in the drop-down list.
First, create a list of valid elements on the worksheet, and then sort or arrange them in in the right order. These elements can later serve as a source for a drop-down list of data. If the list is small, you can easily reference it and enter items directly into the data validator.