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:
- Create your list(s) in a separate worksheet – this makes them much easier to manage.
- Name the range (essential if you are using Excel 2007 or earlier) that contains your list. Remember – no spaces in the name
- Highlight the area in your spreadsheet where you want to add your list
- Click on Data tab, then Data Validation, then Data Validation again.
- From the dropdown list, under Allow, choose List
- 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
- 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.
- 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.