Lifehacker has collected seven ways: from simple to difficult.
How to make a simple drop -down list in Excel through the insert function
Elementary option: does not need any preliminary setting and is available thanks to the standard insert function. It allows you to choose previously introduced values from the list and quickly add them. However, this works only if the data is contained in the column without passes, and the input is carried out in order.
Put the cursor in the next empty range of the range. In our example, this is b7.
Make the right click with the mouse and specify “choose from the opening list” or just click on the ALT + “Arrow Dop” keyboard.
After that, a list of all the above values without repetitions will appear, and you just have to choose the desired and click on it.
How to make a drop -down list in Excel from the data range
The next method allows you to use any data range for a drop -down list, which is located anywhere in the sheet, as well as on a different sheet or even in another file (more on that later). The list will not be updated if you add information below the specified range, but if you are not inserted at the end, but between the lines, everything will work. When removing the list elements, it will change in any case.
Select the cell in which the list of the list should be (we have H3), switch to the data tab and click the “Data Verification”.
In the window that opens, select the “List” data, click in the “Source” field and highlight the desired cells.
Click OK, and a drop -down list with the desired values will appear in a given cell.
How to make a drop -down Excel list from a called range
Any range of cells can be made named by assigning it to the name. This will allow him to refer to him and simplify the memorization of the ranges with which you often work. Otherwise, this option of the drop -down list does not differ from the previous one: it will be updated only if you delete one of the values or add the line inside the range.
First, create a named range. To do this, select the cells with future points of the drop -down list and entitled by entering the name in the field with the names of the cells, and then click Enter. A prerequisite: the first symbol should be a letter, you can not use gaps (put “_” instead).
Now put the cursor in a cell where you want to make a list, and on the “Data” tab, click “Data Verification”.
In the “Data Type” column, select the “list”, and indicate the name of the range as a source by setting it “=” in front of it. In our example, “= brands” came out.
Drop -down list with elements from the called range is ready.
How to make an outlet list in Excel with data of another sheet or file
Sometimes you need to use external data that are located on another sheet or generally in a separate book. In this case, the function of the DVSIL will help, which will allow them to refer to them. Since the range of cells is used as a source, the list will be updated only if you fit new values inside the range or delete them. In addition, with a link to another file, it must be open.
Open both files, then select the cell with the future list and on the “Data” tab click “Data Verification”.
Select a “list”, indicate the following formula as a source: = DVSIL ("[name_fila] name_list!range") That is, inside the quotes should be the name of the file with data for the list in square brackets, then without a gap the name of the sheet, and then also without a spaces of the exclamation mark and the designation of the range. In our example it will be like this: = DVSIL ("[Book1] Liszt1!A3: A9")
The list is ready. To use data from another file, it should also be open at this time.
How to make a dropping list in Excel from the table
The next method is slightly more complicated, but much more universal and correct. It is based on the so -called smart tables, they are dynamic. So, any changes, whether it is the addition of new points or their removal, will instantly reflect in the drop -down list.
Highlight the range with the list elements and their category, and then on the “Home” tab “Format as a table”.
Select any design option, mark in the window that opens the “table with headlines” option and click OK.
Put the cursor in one of the cells of the table and on the “Designer of the Tables” tab, enter the list name, confirming it by pressing Enter.
Click on the cell in which there should be a list, and click “Data Verification” on the “Data” tab.
Indicate the “list” as a type of data, and for the source enter the following formula: = DVSL ("Name_ Tables") In our example it will be like this: = DVSIL ("Doors")
The list will immediately work and will be updated when removing and adding elements.
How to make a dropping list in Excel with a search
If the list is large enough, then you can facilitate the introduction of values with prompts that will accelerate the search for the desired elements. You can do this through the tools of the Activex developer.
If the “Developer” tab is not displayed, first you should turn it on. To do this, open the menu and select “Other teams …”.
Click “Request the tape”, then turn on the “Developer” item and click OK.
On the “Developer” tab, click “insert” and select the “List Field” from Activex elements.
“Draw” with the cursor of the list cell, then click on it with the right mouse button and select “Properties”.
Find Listfillrange, enter the range of values for the list and click Enter. In our example, this is B6: B9.
Disconnect the design of the designer by clicking on the same button. Now the list works, and when entering, suitable points will be highlighted.
How to make a related drop -up list in Excel
A very useful type of list, which is quite common. After choosing a line in one list in the second, different sets of values will be available, depending on the previous. For example, when indicating the category of goods in the first list in the second, models change. Consider the implementation of such a list based on tables so that the data is automatically updated when adding or removing points.
Prepare the columns with data: in the hats the points of the first, parent list, and under them the points of the second. Highlight the first column with a hat and on the “Home” tab “Format as a table”.
Select any of the styles, and then in the window that appears, turn on the “table with headlines” option and click OK.
Click on any of the cells of the table, and then assign the name to it by entering it in the “table name” field and pressing Enter. This will be the paragraph of the first list, in our example – “Computers”. Create tables for other columns in the same way and rename them.
Put the cursor in a cell where there will be the first drop -down list, and on the data tab, click “Data Verification”.
Indicate as the type of “list”, and as a source, highlight the hats of columns with categories that will become points of the parent list.
Next, install the cursor in the cell in which there will be a second list, click the “Data Conservation”, select the “List” option, and in the “Source” field, enter = DVSIL and the address of the parent list in brackets. That is = Dvsil (h3) For our example.
Now, when choosing a parent list, only the corresponding options will be displayed in the associated. For example, if you press the “laptops” line, then Excel will show only the laptop models.
And if you indicate the “headphones” – the corresponding names of accessories from the column of the same name will be displayed.
How to make a simple drop -down list in Excel through the insert function
Elementary option: does not need any preliminary setting and is available thanks to the standard insert function. It allows you to choose previously introduced values from the list and quickly add them. However, this works only if the data is contained in the column without passes, and the input is carried out in order.
Put the cursor in the next empty range of the range. In our example, this is b7.
Make the right click with the mouse and specify “choose from the opening list” or just click on the ALT + “Arrow Dop” keyboard.
After that, a list of all the above values without repetitions will appear, and you just have to choose the desired and click on it.
How to make a drop -down list in Excel from the data range
The next method allows you to use any data range for a drop -down list, which is located anywhere in the sheet, as well as on a different sheet or even in another file (more on that later). The list will not be updated if you add information below the specified range, but if you are not inserted at the end, but between the lines, everything will work. When removing the list elements, it will change in any case.
Select the cell in which the list of the list should be (we have H3), switch to the data tab and click the “Data Verification”.
In the window that opens, select the “List” data, click in the “Source” field and highlight the desired cells.
Click OK, and a drop -down list with the desired values will appear in a given cell.
How to make a drop -down Excel list from a called range
Any range of cells can be made named by assigning it to the name. This will allow him to refer to him and simplify the memorization of the ranges with which you often work. Otherwise, this option of the drop -down list does not differ from the previous one: it will be updated only if you delete one of the values or add the line inside the range.
First, create a named range. To do this, select the cells with future points of the drop -down list and entitled by entering the name in the field with the names of the cells, and then click Enter. A prerequisite: the first symbol should be a letter, you can not use gaps (put “_” instead).
Now put the cursor in a cell where you want to make a list, and on the “Data” tab, click “Data Verification”.
In the “Data Type” column, select the “list”, and indicate the name of the range as a source by setting it “=” in front of it. In our example, “= brands” came out.
Drop -down list with elements from the called range is ready.
How to make an outlet list in Excel with data of another sheet or file
Sometimes you need to use external data that are located on another sheet or generally in a separate book. In this case, the function of the DVSIL will help, which will allow them to refer to them. Since the range of cells is used as a source, the list will be updated only if you fit new values inside the range or delete them. In addition, with a link to another file, it must be open.
Open both files, then select the cell with the future list and on the “Data” tab click “Data Verification”.
Select a “list”, indicate the following formula as a source: = DVSIL ("[name_fila] name_list!range") That is, inside the quotes should be the name of the file with data for the list in square brackets, then without a gap the name of the sheet, and then also without a spaces of the exclamation mark and the designation of the range. In our example it will be like this: = DVSIL ("[Book1] Liszt1!A3: A9")
The list is ready. To use data from another file, it should also be open at this time.
How to make a dropping list in Excel from the table
The next method is slightly more complicated, but much more universal and correct. It is based on the so -called smart tables, they are dynamic. So, any changes, whether it is the addition of new points or their removal, will instantly reflect in the drop -down list.
Highlight the range with the list elements and their category, and then on the “Home” tab “Format as a table”.
Select any design option, mark in the window that opens the “table with headlines” option and click OK.
Put the cursor in one of the cells of the table and on the “Designer of the Tables” tab, enter the list name, confirming it by pressing Enter.
Click on the cell in which there should be a list, and click “Data Verification” on the “Data” tab.
Indicate the “list” as a type of data, and for the source enter the following formula: = DVSL ("Name_ Tables") In our example it will be like this: = DVSIL ("Doors")
The list will immediately work and will be updated when removing and adding elements.
How to make a dropping list in Excel with a search
If the list is large enough, then you can facilitate the introduction of values with prompts that will accelerate the search for the desired elements. You can do this through the tools of the Activex developer.
If the “Developer” tab is not displayed, first you should turn it on. To do this, open the menu and select “Other teams …”.
Click “Request the tape”, then turn on the “Developer” item and click OK.
On the “Developer” tab, click “insert” and select the “List Field” from Activex elements.
“Draw” with the cursor of the list cell, then click on it with the right mouse button and select “Properties”.
Find Listfillrange, enter the range of values for the list and click Enter. In our example, this is B6: B9.
Disconnect the design of the designer by clicking on the same button. Now the list works, and when entering, suitable points will be highlighted.
How to make a related drop -up list in Excel
A very useful type of list, which is quite common. After choosing a line in one list in the second, different sets of values will be available, depending on the previous. For example, when indicating the category of goods in the first list in the second, models change. Consider the implementation of such a list based on tables so that the data is automatically updated when adding or removing points.
Prepare the columns with data: in the hats the points of the first, parent list, and under them the points of the second. Highlight the first column with a hat and on the “Home” tab “Format as a table”.
Select any of the styles, and then in the window that appears, turn on the “table with headlines” option and click OK.
Click on any of the cells of the table, and then assign the name to it by entering it in the “table name” field and pressing Enter. This will be the paragraph of the first list, in our example – “Computers”. Create tables for other columns in the same way and rename them.
Put the cursor in a cell where there will be the first drop -down list, and on the data tab, click “Data Verification”.
Indicate as the type of “list”, and as a source, highlight the hats of columns with categories that will become points of the parent list.
Next, install the cursor in the cell in which there will be a second list, click the “Data Conservation”, select the “List” option, and in the “Source” field, enter = DVSIL and the address of the parent list in brackets. That is = Dvsil (h3) For our example.
Now, when choosing a parent list, only the corresponding options will be displayed in the associated. For example, if you press the “laptops” line, then Excel will show only the laptop models.
And if you indicate the “headphones” – the corresponding names of accessories from the column of the same name will be displayed.