Open the Name Manager and enter a structured reference such as =Table in the Refers to box:.Then, type the name for the selected range in the Name box.
Point to the top of the heading cell, and when a black down arrow appears, click to select all the cells in the table column without a header. To refer to the data cells, you can use one of the following methods: Create a named range for a column of data, not including the header cell in the table. Instead of using a regular named range, you can convert your data to a fully functional Excel table ( Insert > Table or Ctrl + T), and then create a data validation list from that table. If the source list contains more than 8 items, your drop-down box will have a scroll bar like this:Įxcel data validation list based on a table If you are creating a drop-down based on a named range, and that named range has at least one blank cell, selecting the Ignore blank box allows any value to be entered in the validated cell. These values should be entered in a single column or row without any blank cells.įor example, let's create a drop-down list of ingredients for your favorite recipes: Select the entries you want to appear in your drop-down menu in an existing worksheet or type the entries in a new sheet. Type the entries for your drop-down list. This method of creating an Excel data validation list takes a bit more time, but it may save even more time in the long run. Creating an Excel drop-down list based on a named range If it's not the case, consider using one of the following options. This method works well for small Excel data validation lists that are unlikely to ever change. Well, your drop-down box is ready in under a minute. Now, Excel users simply click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu. Select or clear the Ignore blank depending on how you want to handle empty cells. 
Make sure the In-cell dropdown box is checked otherwise the drop-down arrow won't appear next to the cell.In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces).
In the Data Validation window, on the Settings tab, do the following: Enter the list items and choose the options. On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation.ģ. Use Excel Data Validation to create a drop-down list. You can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse.Ģ. If you select the whole column, a drop down menu will be created in each cell of that column, which is a real time-saver, for example, when you are creating a questionnaire. This can be a single cell, a range of cells or the entire column. You start by selecting a cell or cells where you want a drop-down box to appear. Select a cell or range for your drop-down list. This is the fastest 3-step way to create a drop-down box in all versions of Excel 2016, 2013, 2010, 20.
Drop down list based on a range of cellsĬreating drop down lists with comma-separated values. Drop-down box based on a list of values. Below you will find a quick outline of the main advantages and drawbacks as well as the detailed step-by-step instructions for each method: On the whole, there are 4 ways to make a drop down menu in Excel, and all of them have their own strong and weak points. Allow entering other data in a combo box. Display a message when a cell with the dropdown is clicked. Additional options for the Excel drop-down box. #Combo in excel how to
How to fix Excel data validation not working.Make a drop-down list from another workbook.How to create a drop-down list in Excel.Apart from that, a dropdown prevents spelling mistakes and makes data input faster. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. It also shows how to create a dropdown from another workbook, edit and delete data validation lists.Įxcel drop-down list, aka drop down box or combo box, is used to enter data in a spreadsheet from a pre-defined items list. The tutorial demonstrates 4 quick ways to create an Excel data validation list (drop-down list) - based on a list of values, range of cells, named range and a dynamic dropdown.
0 kommentar(er)
