Create a Drop Down List in Excel

Create a Drop Down List in Excel

Create a Drop Down List in Excel


When you use excel to share complex lists with lots of rows in one column, there is a bunch of blank space at the bottom of the list. Voilà! Manually create a drop down menu that appears in the bottom corner.


An Excel drop down list or dropdown menu can make it easier for the average Microsoft Excel user to enter data on a worksheet or workbook. Using a drop down menu in web forms, surveys, or polls can limit the entry choices for a selected cell, speeding data entry and reducing data entry error. In this Excel tip, we'll show you a quick and easy way to create a dropdown list or drop down menu using the spreadsheet application's Data Validation feature.

A programmatic solution, using Excel VBA, would need to be supported by using VBA equivalents of spreadsheet functions or where they don’t exist in Excel VBA using the Excel VBA “Application.Spreadsheetfunction …” method to access spreadsheet functions. Knowledge of conditional branching, eg “If”, “Select Case” and looping, For/Next, “Do Until” constructions would be very useful, if not essential in acheiving a programmatic solution. In addition you can make use of VBA User forms for editing records, or creating a spreadsheet-based form which writes and reads record data items from other sheets of data tables. If using either VBA User Form or a spreadsheet template to display records they would need to be accompanied by user entry text fields, buttons, dropdowns, etc (Controls in VBA speak) or cell(s) for fields, Shapes for buttons and spreadsheet dropdowns (data validation) cells. VBA buttons and spreadsheets would have VBA code assigned to them that would be executed when the cuttons are clicked. (Source: trumpexcel.com)


To create a drop down list in Excel, you can name a list of items, based on a named Excel table. Then, use that list as the source for the Data Validation drop down list. If you don't want to create a named table, you can follow the instructions in the named range section below. Watch the steps in this short video, and the written instructions are below the video

How does this work? – The conditional drop down list (in cell E3) refers to =INDIRECT(D3). This means that when you select ‘Fruits’ in cell D3, the drop down list in E3 refers to the named range ‘Fruits’ (through the INDIRECT function) and hence lists all the items in that category. (Source: trumpexcel.com)



Related Articles