David H Ringstrom continues his series of productivity suggestions with a tutorial explaining how to make Excel’s data validation facility work even more smoothly.
If there’s one useful principle you should bear in mind when designing a spreadsheet that will be used by other people, it would be to apply Excel’s Data Validation feature wherever possible.
Rather than letting users loose to enter any value in any cell they like Data validation restricts them to a predefined set of values. This tutorial shows how to create in-cell drop-down lists, and configure them so you won’t need to adjust it if you add additional items in the future. This technique also addresses an Excel 2007 flaw that prevents you from placing validation lists in other worksheets.
To demonstrate how Data Validation works it’s traditional to use a selection of fruit as an example. On the second worksheet of a blank workbook, create a list of fruit...