A useful skill to have – particularly if you need to do analysis on a list (in pivot tables etc) as you want to ensure that your data entry is kept consistent. It’s also a real time saver it you need someone else to input data for you and you want to restrict data entry.

I show you how to do it in the video below..

[flowplayer src=’https://s3-eu-west-1.amazonaws.com/the-excel-expert/creating+a+dropdown+list.mp4′]

But here are the steps:

  1. Create your list(s) in a separate worksheet – this makes them much easier to manage.
  2. Name the range (essential if you are using Excel 2007 or earlier) that contains your list. Remember – no spaces in the name
  3. Highlight the area in your spreadsheet where you want to add your list
  4. Click on Data tab, then Data Validation, then Data Validation again.
  5. From the dropdown list, under Allow, choose List
  6. Click in the Source box, click F3 (this calls up the list of range names) and then click on the range name that you created for your list
  7. You can also amend your input messages at this point as well. Note that when you use a list, the message displayed when someone clicks on the list is what you have typed into the Input Message box in the Input Message tab.
  8. Hey presto, your list is ready. Users can only input what is on the list.

You can also practise on the file given here as well.