Creating drop down lists with 5 methods in excel

Drop down lists in Excel are a good method of organising and keeping your data in check. While the concept of creating functional drop down lists seem complicated, there are actually easy methods one can use to accurately create the drop down lists. One of these methods would be using data validation. Data validation is ideal when creating drop down lists since it improves data input efficiency and reduces the potential for errors. You can use restricted entries using the criteria, custom rules and be notified when data that is irrelevant is being entered:

1. Data Validation 

Begin by enabling the use of the data validation dialog box. To do this, you must first make sure to select a cell and look for your data tab. Locate data tools and choose data validation, if possible, you can also make use of the shortcut keys (ALT+D+L). You will then be prompted for validation criteria with a series of options, choose accordingly. In this case, you can select the option for list. You will then have to designate the data source for Excel to refer to. Choose the cell range you want. Once successfully accomplished, write in an input message for both the title and message body to create “instructions” for your drop down list. You can also edit your own error message in the error alert area. Now choose OK to finish creating the drop down list.

2. Defining 

You can also create a drop down list in Excel by defining range names. To do so, choose the data range you will require and use define name by accessing the formula tab. Once you have chosen the formula tab, select the defined names feature and choose define name so that you can write in the necessary label. You can then access the data validation window and choose the list option when prompted for criteria again. Type in the name you have created or choose the F3 key to paste it and name the range. Click okay and your Excel drop down list will be created successfully.

3. Indirect Function 

You can also enable more flexibility by going for dynamic drop down lists in Excel. One of the classic ways to do so is by making use of the indirect function. Create your table with the Insert tab- Once you have chosen to create the table, you will then be able to select the data range you need. This will ensure that the data range is converted into a table, when you add things to the bottom, your table will auto-expand to incorporate it into the list. Now make use of the data validation function and open up the dialog box. Once again, choose list and enter ( =INDIRECT( “table”) into the source section.

4. OFFSET/COUNTA

Open up your data validation dialog box as usual and make your relevant changes. This time, locate your source section and enter the formula for OFFSET and COUNTA ( =OFFSET($_$_,_,_,COUNTA($_:$_),_). This formula is created to explain to Excel which range you want to select, how many rows to move/not move from the starting, and how many changes you want/do not want in the column. The argument will also prompt Excel to check the number of rows to return non-blank counts and expand ranges when changes are made within the range.

5. Table Range

Locate the table range and select the cell range for your data with the exclusion of your header. Once complete, choose the name box and type in “tablerange” before pressing enter. Under the drop down list options, you will be able to see all your named ranges made available. Now enable the data validation dialog box and enter “tablerange” into the source section. All relevant changes should be added automatically to your drop down list when you work. But make sure to remember that if you skip cells after your last cell to add things, the table range will not be expanded so choose to enter only in the relevant cell.