Excel tip: How to create a drop-down list in Excel

What you will learn.

In this video, you will learn how to create a drop-down so you can select from a list without having to retype each time. In this example, we have a simple database with a Department field and will create a list of departments as the drop-down list.



To create a drop-down list:

  • Create a new sheet and list all the options in column A. Include the name of the column from the database in A1 (e.g. Department).
  • Go to the database and click inside the first cell of the column (e.g. E5).
  • Click the Data ribbon > click Data Validation > under Allow select List > select Source and point to the list of options (e.g. sheet named Lists – row 2–6) > click OK.
  • Click on the small arrow on the right of the cell to see the list drop-down ready for you to select.
  • Copy the list down using Autofill for the drop-down to apply to the entire column.

 Icon of a light bulb
Looking to learn more about Excel? See more free Microsoft Excel tips or browse our range Microsoft Excel courses.