Microsoft_Excel_2013_logo.svg

Excel Drop Down Lists: 3 Ways to Create and Use Them

Excel drop down lists are extremely useful if you are sending out a spreadsheet (such as in a survey or form) and you need to restrict the type of answers your users can put in your spreadsheet. Watch the above video to learn how to create an Excel drop down list. When you’re done watching the video, read our tips below to learn how you can use Excel drop down lists in your spreadsheets.

1. You Need to Limit Your Users’ Options

If you’re sharing an Excel workbook and using it to collect data from other people, you may want to limit the number of options your users can provide. For example, if you were conducting a survey among your co-workers to find out everyone’s favorite type of cake, you would want to specify some options for your users to select. Otherwise, you may end up with a situation where two people who both like chocolate cake may give you two different answers. One may simply write “chocolate”; the other may write “chocolate cake”. Before you could perform any analysis on your data, you would need to clean it up and standardize the responses (i.e. change all the “chocolate cake” responses to “chocolate”) and correct any spelling/punctuation errors. One way to avoid this situation is to simply use an Excel drop down list, which limits the range of answers your users can provide in the first place and makes analyzing the data much easier.

2. You Need to Limit Your Own Options

Similar to the logic above, if you are working through a long list of data and have some sort of response column (inputting a date or marking every line “yes” or “no”), you can use an Excel drop down list to limit your own response options. This will help you avoid typos, spelling errors, etc. So, when you go to analyze your data (perhaps by creating a Pivot Table), you don’t have to spend time going back through your list to find that one typo that is skewing your data.

3. Excel Drop Down Lists Can Feed Into Formulas

Excel drop down lists can also be useful if you need a particular response to trigger other activities in your worksheet (as in a dashboard). For example, if you’ve designed a dashboard (or some other report), you could use an Excel drop down list to provide a menu of options that will refresh the data every time you change your selection. So, if you have all of your formulas feeding off the Excel drop down list, your users can quickly see different views of the data by selecting a different option from the list. The advantage here is in the uniformity of options in the list. When your users have an Excel drop down list to use, they don’t have to worry about misspelling or mistyping their inputs.

The Excel drop down list function is a very useful and very flexible resource if you know how to use it. You can learn more about how to use Excel drop down lists and other Excel functions by registering for one of our upcoming classes.

Join our mailing list today to receive free Excel tips and exclusive discounts on our training classes right in your inbox!