The first three arguments are required and the last two are optional. The syntax of the OFFSET function is: = OFFSET (reference, rows, cols, , ) You can also use the OFFSET formula to create dynamic drop down lists, which automatically update when items are added to the end of the list. Method 3 - OFFSET formula (dynamic drop down lists) Each item listed is shown in a different line in the drop down menu. This will create a drop down list in the selected cell(s).

In the ‘Source’ field, enter the range which contains the list of values to be used as your drop down list, or you can just click inside the ‘Source’ field and select the cells on the Source worksheet.

Select ‘List’ as the Validation criteria.Next, select the cell or range of cells where you want to create the drop down list.We’ve entered the source data in cells A2 to A7 on another worksheet named Source within this same workbook. Of course, first, you'll need to set up a source, or list elsewhere.There is also the option to create a drop down in Excel using a range of cells as the source data for your validation list. Method 2 - Referencing data from other cells All the items listed in the source field are listed in different lines in the drop down menu. This will create a drop down list in the selected cells. Make sure that the In-cell dropdown option is checked. In the Source field, enter the options, separated by commas.In the Data Validation dialog box, within the Settings tab, select List as the Validation criteria.Within the Data Tools command group, select the Data Validation icon. Select a cell or range of cells where you want to create the drop down list.If you want a simple option (for example, Black/White Yes/No/Don’t Know, etc.), then the quickest method may be to do it manually. We’ll explore how to create a drop down list in Excel using three methods. You can make a drop down list in Excel in a variety of ways. They are very user-friendly and are a great way to reduce input errors. Excel drop down lists are an easy way to control the values which are entered in a cell.