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