Share this content

Excel tip: Automate data validation lists

12th Dec 2013
Share this content
Kashflow logo

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.

Create a data source list on the second worksheet of a workbook

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. 

Assign a name to the second through last cell of your table or list.

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.

Specify the source data range name on the Settings Tab.

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:

The Input Message tab helps document the validation

 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.

Fill in the Error Alert tab to give users an understandable error prompt.

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:

Selecting input cell prompts the user with your instruction

Warning message if the tries to enter something that doesn't appear on the Data Validation 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.

New items appear on the Data Validation list automatically

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.

Replies (4)

Please login or register to join the discussion.

avatar
By lyndonbrown
13th Dec 2013 16:22

new trick for an old dog!

Thanks, I'm a big user of dynamic name ranges for all types of analysis from validation to use in arrays, normally using the Offset() function and Counta() to position and size used rows or (columns) into a range.  David's suggestion benefits from two points: firstly the table items are defined as contiguous, Offset/Counta could give unexpected results if there is data placed someway below the list in the worksheet as the counta() function normally link to the entire worksheet column; and this is simpler to setup than via a NameRange Function and you can see exactly the extent of the range quickly. I like it.

Thanks (0)
avatar
By waltere
18th Dec 2013 12:51

I agree!

A simple topic, clearly explained - thanks!

Thanks (0)
avatar
By chatman
09th Jan 2014 16:32

Good Tip. Thanks.

Thanks (0)
avatar
By atulthakrar
11th Jan 2014 16:59

GREAT TIP, CLEARLY EXPLAINED VIA A SIMPLE EXAMPLE. THANK YOU.

Thanks (0)