Drop-down list boxes are useful in Excel but, like many things in Office, there’s more than one way to do it and more to know that the simple examples often given.
Making a pull-down list is easy and there’re many tips showing the basics. In this article we’ll go beyond that. We’ll show that simple tip then how to use the pull-down and its limitations. Later, other options for better pull-down lists like AutoComplete, tooltips and different types of warning.
We’re talking about adding a pull-down list to any Excel cell. It’s useful for limiting the responses in a cell to what you expect. No risk of misspelling or other errors that mess up your charts and reports.
Without pull-down lists or some data validation, all sorts of little ‘bludners’ can creep into your lists.
Pull-down list 101
Make a simple drop-down list by selecting a cell then Data | Data Tools | Data Validation | Settings.
Allow: choose List
Ignore Blank: optional. Allow blank cells, otherwise there’s an error.
In-cell dropdown: ON
Source: type the options separated by commas.
Click OK and test on the cell. If you’re happy, copy the cell to others in the list or table.
Annoyingly, Format Painter does NOT copy data validation settings. Instead use the option ‘Apply these changes to all other cells with the same settings’.
Using the drop-down cell
There are various ways to use a dropdown cell, some obvious, others not so obvious.
Click the wedge on right will show the entire list of options.
Keep your hands on the keyboard by pressing Alt + down arrow to pull down the list.
Or type the selection.
AutoComplete
There’s no autocomplete available in this simple dropdown (in other words, the validation source list doesn’t become an autocomplete).
However, in a usual list or table, the standard Excel autocomplete will start working once there are cells with each option.
In this example, Optional is being autocompleted after typing ‘O’ or ‘Op’, because ‘Optional’ is already in the column, NOT because it’s in the data validation source.
That’s important when there are pull-down list choices not often used. Only existing selections can appear in autocomplete.
Try to make your list choices start with different letters or at least the first two letters different. That will make autocomplete faster. For example use Yes | No | Maybe not Yes | No | Not Sure.
Dynamic source list
A better and more flexible way to manage the pull-down list is to grab the choices from a list of cells.
Instead of a comma separated list of choices, have Source: point to a cell range.
It’s better to use a named range for the pull-down list. With a named range you can change the number of choices at any time by changing the Refers to: of the range.
Type beyond the pulldown
Sometimes the pulldown list has the common answers but you want the ability to put in other answers. That doesn’t seem possible from the Data Tools | Data Validation | Settings but it’s available.
Data | Data Tools | Data Validation | Settings has the Ignore Blank option to leave blank cells, without an error.
To type anything in the cell, turn off the error message at Data | Data Tools | Error Alert
Now anything can be typed into the cells without any error.
That’s just one way to allow data other than the pull-down list, see below.
Custom Error messages
If Error Alerts are on and the data entry is wrong a generic error message appears. “This value doesn’t match the data validation restriction defined for this cell.”
Happily, you can change that to something more helpful at Data | Data Tools | Data Validation | Error Alert.
Show error alert after invalid data is entered
Style: Stop prevents leaving the cell without a valid choice. More on this below.
Title: & Error Message: Something to appear in an error message box.
Error Alert Styles matter
Data | Data Tools | Data Validation | Error Alert | Style has three options: Stop, Warning or Information. It’s an important difference, not just the icon that appears on the message box. The Style changes what the user can do in the cell.
Stop
Stop: prevents invalid data (ie not on the source list) from being entered. The error messages options are Retry, Cancel or Help.
Warning
Warning: the user must confirm that the unexpected answer is correct. Continue? choices are Yes, No, Cancel or Help.
Information
Information: a softer warning. There’s a prompt that the entry isn’t normal with choices: OK, Cancel or Help.
Input Message or Tooltip
Finally, a mention of the Input Message, when you want to explain the cell and options in more detail.
Data | Data Tools | Data Validation | Input Message is really a tooltip, not a full message box.
