Dropdown in Excel
Many of you have experienced an Internet form where the author of the interactive form wants you to enter a specific piece of information. The nature of the information often suggests giving the user of the form a fixed choice. An example might be a request to enter a state. The interactive form would offer a down-arrow and the choices of states would open up on the screen. You simply select the correct state and the form will react as if you had entered the correct spelling of the state into the form. This process offers the freedom of selection while preserving the correct spelling once the form is submited.
That same control often extends to Excel documents which are developed by one person and used by another. Often input cells need to be entered "correctly" or the model won't work correctly. With the initial developer of the spreadsheet not around, the frustrated user is caught with no option but to guess what went wrong or quit. A dropdown collection of choices may be just the ticket for your spreadsheet and it's easy to install.
First you need to select the cell in your spreadsheet where you want the dropdown to be located. Once the cell is activated, click on Data + Data Validation as in the picture below.
This should open the Data Validation Wizard.
Clicking on the Settings Tab should give you the choices you need to get started.
In the Validation Criteria, click on the down-arrow to display the choices and select List.
(Note: the makers of Excel are using the "Dropdown Concept" rather than ask you to type the choice you want.)
With List selected, you can enter the names/values that should be in the list that will drop down on your Excel document.
Be sure to separate each name/value with a comma.
Note: If you have a large listing of names/values already typed, this may not be as quick. A slightly different method will be shown later.)
When you have your list, click on OK to submit your list.
In this example, Cell B4 has the text Select One as a prompt. Once a selection is made, the contents of
Cell B4 will be replaced with the selection made by the user.
See the example below to see how it works.
 |
 |
Making the selection from the dropdown list |
After the selection is made |
You may find yourself with a typed listing of choices which is quite long or tedious to type. It might be nice to have Excel pick up your previously typed and proofed work rather than force you to re-type your list and possibly introduce a typing error.
This listing of days of the week are not too difficult to retype, but they serve as an illustration of how the process could work.
In the source, simply enter the cell addresses of the starting cell and the ending cell preceded with an "=" . You might think this would be
"=E2:E8", but you need to add the "$" signs as indicated in the example to the left.
When you complete the source and click on OK, the resulting spreadsheet will look like the following:
Obviously the final location of the names in Column E could be anywhere in your spreadsheet which means they could be strategically located out of the viewing window of the portion of the spreadsheet that contains the dropdown selection.
The feature is so easy to implement, you might want to consider adding this process to your Excel documents when you have need for someone else to enter data, the choices of data entry are limited to specific keystrokes, and you can't afford to accept typing errors.
One instructional example might be a test given with Excel and feedback based on the user's answers.
Consider the question "Who was the first President of the United States?" You might be willing to accept: Washington, George Washington, G. Washington, etc. Who would want to setup a series of nexted IF statements to check the accuracy of the answers? Who could guess all of the possible "correct" answers a student might give? A multiple choice question would provide the correct spelling (and you could check the answer with only one IF statement).