Simple Yes, No drop down list in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

simple yes no drop box in excel microsoft excel 28645 - Simple Yes, No drop down list in Excel

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.

simple yes no drop box in excel microsoft excel 28646 - Simple Yes, No drop down list in Excel

Pull-down list 101

Make a simple drop-down list by selecting a cell then Data | Data Tools | Data Validation | Settings.

simple yes no drop box in excel microsoft excel 28647 - Simple Yes, No drop down list in Excel

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.

simple yes no drop box in excel microsoft excel 28648 - Simple Yes, No drop down list in Excel

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.

simple yes no drop box in excel microsoft excel 28649 - Simple Yes, No drop down list in Excel

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.

img 5d108deb86fc4 - Simple Yes, No drop down list in Excel

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.

img 5d108df6d16a4 - Simple Yes, No drop down list in Excel

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

img 5d108e0db3013 - Simple Yes, No drop down list in Excel

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.”

img 5d108e23b300e - Simple Yes, No drop down list in Excel

Happily, you can change that to something more helpful at Data | Data Tools | Data Validation | Error Alert.

img 5d108e3a592ec - Simple Yes, No drop down list in Excel

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.

img 5d108e4a6de04 - Simple Yes, No drop down list in Excel

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.

img 5d108e78374ca - Simple Yes, No drop down list in Excel

Stop

Stop: prevents invalid data (ie not on the source list) from being entered.  The error messages options are Retry, Cancel or Help.

img 5d108e8c40a70 - Simple Yes, No drop down list in Excel

Warning

Warning:  the user must confirm that the unexpected answer is correct.  Continue? choices are Yes, No, Cancel or Help.

img 5d108e9fc424c - Simple Yes, No drop down list in Excel

Information

Information:  a softer warning. There’s a prompt that the entry isn’t normal with choices: OK, Cancel or Help.

img 5d108eaf8cf96 - Simple Yes, No drop down list in Excel

Input Message or Tooltip

Finally, a mention of the Input Message, when you want to explain the cell and options in more detail.

img 5d108ecdb09d0 - Simple Yes, No drop down list in Excel

Data | Data Tools | Data Validation | Input Message is really a tooltip, not a full message box.

img 5d108ec1bed37 - Simple Yes, No drop down list in Excel

subs profile e1563205311409 - Simple Yes, No drop down list in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address