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. It also shows how to create a dropdown from another workbook, edit and delete data validation lists. Excel 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 main purpose of using drop down lists in Excel is to limit the number of choices available for the user.

Apart from that, a dropdown prevents spelling mistakes and makes data input faster. • • • • • • • • How to create an Excel drop-down list 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. Right control on mac. 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: • • • • • Creating drop down lists with comma-separated values This is the fastest 3-step way to create a drop-down box in all versions of Excel 2016, 2013, 2010, 2007 and 2003. Select a cell or range for your drop-down list.

Aug 18, 2014 - On a new worksheet, type the values that you want to appear in your drop-down list. They should be in a single column or row without any. If you are on Excel 2010 for Windows instead of 2013, then you could create a PivotTable report to display the desired attributes and then use a Slicer to filter the report for the selected author. In Excel 2010, Slicers can’t be used to filter a table, but, they can filter a PivotTable report.

You start by selecting a cell or cells where you want a drop-down box to appear. This can be a single cell, a range of cells or the entire column. 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. You can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse. Use Excel Data Validation to create a drop-down list. On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation. Enter the list items and choose the options.

In the Data Validation window, on the Settings tab, do the following: • In the Allow box, select List. • In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces). • Make sure the In-cell dropdown box is checked; otherwise the drop-down arrow won't appear next to the cell. • Select or clear the Ignore blank depending on how you want to handle empty cells. • Click OK and you are done! 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.

Well, your drop-down box is ready in under a minute. This method works well for small Excel data validation lists that are unlikely to ever change. If it's not the case, consider using one of the following options. Creating an Excel drop-down list based on a named range 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. Type the entries for your drop-down list. Select the entries you want to appear in your drop-down menu in an existing worksheet or type the entries in a new sheet.

These values should be entered in a single column or row without any blank cells. For example, let's create a drop-down list of ingredients for your favorite recipes.

It's a good idea to sort your entries in the order you want them to appear in the drop-down menu. Create a named range. You can actually skip this step and create your drop-down list, but named ranges really make managing Excel drop-down lists easier. • Select all the entries you want to include in the drop down list, right-click them, and choose Define Name from the context menu. Alternatively, you can click Name Manager on the Formulas tab or press Ctrl + F3. • In the Name Manager dialog, click New.

• In the Name field, type a name for your entries, make sure the correct range is displayed in the Refers to box, and then click OK. Be sure your range name doesn't have any spaces or hyphens, use underscores (_) instead. Excel 2003 and older require the dropdown list to be on the same worksheet.

Apply Excel Data Validation. On the Excel ribbon, navigate to the Data tab and click Data Validation.

Configure your drop-down list. In the Data Validation window, go to the Settings tab, and choose the following options: • In the Allow box, select List.

• In the Source box, type the name you gave to your range preceded by an equal sign, for example =Ingredients. • Make sure the In-cell dropdown box is checked. • Click the OK button to finish creating your drop-down list. If you are creating a, 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.