To make your Excel worksheet more efficient, use the drop-down list in the cell. It is easy to select from the list instead of typing in each cell particularly. It also saves time. In this article, I have tried to show you how you can easily make a cell into a drop-down list. So Let us proceed with steps of How to Create a Drop-Down List in MS Excel 2016?
Steps to Create a Drop-Down List in MS Excel 2016:
Step 1: In the image below you can see that I have prepared an Excel sheet which contains Sr no., Name and City columns. Now I want to create a drop-down list for the City column. I have also prepared the list of cities which I want in the drop-down list in the same Excel sheet. If you want, you can prepare the content of drop-down list in the separate sheet also.
Now, select the cell to which you want to convert into a drop-down cell.
Step 2: Go the Data tab. Under Data Tools group you have Data Validation drop-down button, click it. Select Data Validation from the options.
Step 3: A Data Validation Dialog box is opened. Under Settings tab, you have Allow: drop down box click it and among the options select List.
Step 4: After you select List, a Source fill box appears. Click inside the source box.
Step 5: After you click inside the Source box, select the content of which you want to create a drop-down list. You will see that inside Source box the code for the content is generated. Click OK.
Step 6: Now you can see that the cell you selected in step 1, is converted to a drop-down cell. Click on the drop-down arrow that appears on the right-hand-side of the cell and the entire drop-down list will appear.
Step 7: Now, if you want to apply data validation to all the cells below the City cell. Just drag the Autofill handle of the drop-down cell up to the cell you want to apply data validation.
Hence by doing this, you can make all the selected cells as the drop-down cells.
Or
The other method to apply data validation to the other cells connected to data validated cell is, select the cells including the data validated cell. Go the Home tab, Under Editing group you have Fill drop-down button. Click Fill button and as I have selected the cells in the vertically downward manner, I selected Down. If you have selected the cells in horizontal, you can choose Right or Left according to your requirement. In this way, you can apply data validation to adjacent cells in bulk.
Hope the content is useful. Keep reading.
Karen says
This works great! Thank you. However, I have a huge list in the drop down. How can I type in the box to filter my selection in the drop down?