Dropdown Lists in Excel
August 06, 2005
To try this tip on your own computer, download and unzip CFH249.zip.
Many times, we are building spreadsheets for other people to fill out. If those other people don’t watch Call for Help, they may not be as spreadsheet savvy as you. These people might find unusual ways to enter the wrong type of data – entering text where you expect numbers, or spelling regions the wrong way. Use Excel’s data validation features to control what can be entered in your spreadsheet.
By default, every cell in Excel is set to allow any value. You can use the Data - Validation dialog box to control what values people can enter in a cell.
Set up a cell to allow integers between 1 and 50
In the worksheet below, you want to limit cell B1 to accept only integers between 1 and 50.
Select cell B1. From Excel's menu, select Data - Validation.
Initially, the Data Validation dialog shows that the cell is set up to allow Any value.
In the Allow dropdown, choose Whole Number.
After choosing Whole Number, the dialog box changes to show a Minimum and Maximum value. Enter 1 and 50 here.
In the Data Validation dialog, choose the Input Message tab. The values on this page will appear in a tooltip that will pop up whenever anyone selects the cell. Enter a title and some text to guide the person using the worksheet.
On the Error Alert tab, enter an error message that will be displayed if someone enters a wrong value.
Choose OK to dismiss the dialog box.
When anyone moves the cellpointer to cell B1, a tooltip will appear with your text from the Input Message tab.
If they try to enter a wrong value, Excel presents your error message.
Once they enter a wrong value, they have to either enter the right value, or hit the Esc key to get out of the error message. Hitting Esc will clear the entry in B1.
Additional Details
You have some control over how viligant Excel will be. I hate when machines try to pretend that they are smarter than me. What if some day, your company has grown and it really is valid to have a rep #51? If you change the Style from Stop to Warning, then Excel will discourage the person from entering 51, but it will allow it. Here is the Error Alert tab:
The resulting error message has a default button indicating that the result should be changed. The person using the worksheet can choose Yes to allow the entry of 51.
If you change the error style to Information, the person is alerted that they entered an unexpected value, but the message box defaults to OK to allow them to keep the wrong value. In this case, you might use a message like this one.
Enter a wrong value and the error message will happily let you accept the wrong value with a simple press of Enter to select the default button of OK.
Using Validation to Create a Dropdown
If you allow people to type in a region, you will soon find that there are a dozen ways to enter "Western Australia". It would be better to allow people to select from a dropdown of valid values. This is very easy to set up.
First, go to an out-of-the-way place on the worksheet and type your list of valid regions.
Select the cell that should have the dropdown. From the menu, select Data - Validation. Change Any Value to List. In the Source textbox, select the range that contains your list. Leave In Cell Dropdown selected.
The result: any time that the cell is selected, a dropdown arrow will appear and your list will be available.
Additional Details
You might want to move the list to another worksheet so that it won't be accidentally deleted. Technically, Microsoft says that this is not allowed. However, there is a workaround. Type the list on another worksheet. Assign a range name (Insert - Name - Define) to that range.
On the original worksheet, set up validation. In the List box, enter an equal sign and then the name of the range on the other worksheet.
Add Tooltips to Cells
One interesting use of Validation is to set up input messages for a cell while leaving the other tabs of the data validation dialog blank. This way, the program will accept any value without an error message, but when someone moves the cell pointer to a cell, they will see a tooltip guiding them with what values are expected.
