There are many times in which data validation is useful in Excel. If you’re sharing a spreadsheet with others or using data as input to another set of functions, it can save you a great deal of debugging time.
If you are using the
List validation type, the usual way it is shown
is to have the items you want in the dropdown list to be in some range
in the Excel Workbook. But if you don’t want to hide helper cells, you
can enter your desired list options directly through the dialog.
What you have to do is enter your items in the “Source:” location, comma separated. I’m not sure there is a way to escape a comma if you wanted that to be a part of the options, but that should be rare.
and search for ‘comma’ in the
xlValidateList validation type. You’ll
see it in the definition for the
Formula1 parameter, giving you a
sense of how these options are parsed behind the scenes. I wish
Microsoft would have put “Range or Comma-delimited list” after “Source:”
to let you know this. But I wouldn’t hold your breath waiting for
Microsoft Office dialog boxes to update.