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.

Data Validation Dialog Box

See https://docs.microsoft.com/en-us/office/vba/api/excel.validation.add 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.