Creating a Drop Down List in Excel

 If you find that you have a lot of data to enter on your spreadsheet, it may be a good idea to create a drop down list to allow your users to enter data more easily. Having a drop down list will allow you choose from a set number of entries, so saving time. The cell that features the list will have a small arrow that when clicked will bring up the data options.

  1. Start by creating your list by in a column of cells, for example A1:A4. But you are not restricted to entering the data in columns, you can also enter your list in a row, such as A1:D1.

  2. Next, select the cell where you want to place your list. You can place the list in multiple cells if you need to.

  3. On the ribbon, click the Data tab and then click the Data Validation button to bring up the dialogue box.

  4. From the Allow options drop down list, choose List.

  5. Click the Source box and select the cell range that contains the data that will make up your list. Alternatively you can enter the cell range manually, =$A$1:$A$4.

  6. Make sure that you check both the in-cell drop down and the ignore blank boxes. The ignore blank box will allow users to make a blank selection.

  7. While you are in the Data Validation box you can choose to set up an Input Message, such as Please Select and an Error Alert if the user tries to enter invalid data into the cell.

  8. Click OK to finish.

If you wish to create a drop down list that is not dependent on data entered in other cells, you can enter the list items directly into the Source area in the Data Validation box and then continue with the rest of the above steps.

If you wish to delete the list from the worksheet, click on the cell to make it active and then go to the Data Validation box and click on Clear All.

data-validation