Excel tip: Automate data validation lists
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 as shown below. Having done so, make the list into a Table (Excel 2007 and later), or a List (Excel 2003 and earlier).
Then click on any cell in the list and follow the appropriate procedure:
- Excel 2007 and later – Choose Insert and then Table. Make sure that My Table Has Headers is selected and then click OK.
- Excel Mac 2011 – On the Tables tab of the ribbon, click the arrow next to the New command and then choose Insert Table with Headers.
- Excel 2003 and earlier – Choose Data, List, and then Create List.
Excel Tables (and lists in Excel 2003 and earlier) will automatically incorporate any items you add at the bottom. But you won’t be able to use this kind of self-expanding table or list directly with Data Validation. To do so, first create a range name that encompasses all but the first row of the table.
Select the rows starting after the first item on the list down (as shown in the illustration below) to the bottom and then:
- Excel 2007 and later – On the Formulas tab choose Define Name.
- Excel 2003 and earlier, or Excel – Mac 2011: Choose Insert, Name, and then Define.
Enter a name such as Fruit in the New Name dialogue box and make sure the Refers to field refers to the second through last row of your table. Then click OK.
Having created and named your source list it’s time to fire up the Data Validation feature. On the first worksheet of the workbook where you created your list, click on cell A1 and type the word Fruit. Next, select cell A2 and choose Data and then Data Validation. The dialogue box will appear as shown below. On the Settings tab, choose List in the Allow field. When the Source field appears, type an equal sign along with the range name that you assigned before, such as =Fruit.
On the Input Message tab, enter a title, such as the word Fruit, to describe the input field, along with a message, such as “Choose an item from the list” (see below). You don’t need to use quotation marks in the Description field:
Finally, on the Error Alert tab, enter a Title, such as “Invalid Input” and suitable text for your Error Message, such as the comment “You must make a selection from the list” illustrated below. Make sure the Stop option is selected from the Style pull-down menu to prevent the user from bypassing the list items. Click OK to close the Data Validation dialogue box.
When someone clicks in cell A2, your alert message should appear as shown below. This also helps to document the spreadsheet and its purpose. If the user makes a valid choice, he or she will then be able to move to another cell; otherwise, a prompt will appear to prevent them from doing anything else until they choose a valid item from the source list:
If you go back to your original list in Sheet 2 and add two more items, they should appear automatically in the Data Validation menu on Sheet 1, thanks to the automatic table/list update and range name configuration working in the background. Within the Data Validation dialogue box, you can define the cell addresses in the Source field, but you would then need to manually change the setting if you added any items to your list. Excel 2007 and older versions won’t let you reference cell coordinates on other worksheets.
Good spreadsheet design principles call for supporting lists to be maintained separately from the actual data on different worksheets. Excel 2007 addressed this shortcoming, you users no longer have to hunt for a safe place to position supporting lists for Data Validation on the same worksheet as the actual input is occurring.
About the author
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.