Drop down lists are used to select specific data, in our example, a list of Salutations. If the user tries to enter a Salutation that is not in the list, Excel rejects the entry.
I am using a simple drop down list in an Excel sheet. This feature is not well known nor initiative.
Salutations was a BIG DEAL for a religious organization that I worked for as a Consultant. The company had a donor base of 25,000+ names/addresses. (Of course, I was using a database, not Excel, to manage the data.) If the Salutation was incorrect on a donor letter, some donors refused to give monies for the company projects. For example, do you address them as Mr. and Mrs. or Dr. and Mrs. or Mr. and Dr.? Some donors wanted no Salutation. I spent a lot time getting the Salutations correct.
This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.
Setting Up the Excel Sheet
- Enter the headings in Row 1 (Optional)
- Create a list of cells E2:E5. Alternatively, you could put the list in another sheet.
- Select the empty cell A2. (You can position the drop down list in most any cell or even multiple cells.)
Using Data Validation
- Select the Data tab
- Select Data Validation
- Choose List from the Allow options drop down list
- Click the Source control and drag to highlight cells E2:E5. Alternatively, you could enter the cell reference $E$2:$E$5 or use a sheet and cell reference if your list is in another sheet.
- Ensure that the In Cell dropdown option is checked. If you leave this unchecked, Excel forces users to enter only the list values, but it won’t present a drop down list. The users would have to guess the values; not a good idea!
See the following screenshot:
- Click OK
You can add the drop down list to multiple cells. Select the range of data input cells instead of a single cell. Also, you can select noncontinuous cells by holding down the Shift key while you click the appropriate cells.
You can copy and paste this drop down cell to any other cells in your sheet.
Below is a screenshot where Mr. was selected as a Salutation for cell A2:
I Would Like to Hear from You
Please feel free to leave a comment. I would love hearing from you. Do you have a computer tech question? I will do my best to answer your inquiry. Please mention the app and version that you are using. To help me out, you can send screenshots of your data related to your question.
Tech Savvy Life 