Automating Data Validation Lists in Excel

By David Ringstrom, CPA
 
Working in an Excel spreadsheet can be somewhat like the Wild West  unless other provisions are made, users can enter any value in any cell. One way you can restrict users to a predefined set of values is by way of Excel's Data Validation feature. I'll explain how you can create in-cell drop-down lists, along with how to "future proof" the Data Validation list so that the feature won't require maintenance if you add additional items in the future. This technique also resolves an annoying problem in Excel 2007 where data validation lists cannot be placed on other worksheets.
 
Before we look at Data Validation, let's first establish our list. Let's say we want the user to choose a type of fruit. On the second worksheet of a blank workbook, create a list, such as shown in Figure 1. Once you do so, make the list into a Table in Excel 2007 and later, or a List in Excel 2003 and earlier. After you click on any cell within your list:
  • 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.
 
Figure 1: Enter a few items on the second worksheet of a workbook.
 
A benefit of tables (and lists in Excel 2003 and earlier) is that if you add items to the bottom of the list, the table will expand automatically to encompass the new items. However, we can't use this self-expanding table or list directly with Data Validation. To do so, we must create a range name that encompasses all but the first row of the table. Select the second through last row of your range, 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.
Once the Define Name dialog box shown in Figure 2 appears, enter a name such as Fruit, ensure that the Refers to field references the second through last row of your table, and then click OK.
 
Figure 2: Assign a name to the second through last cell of your table or list.
 

You may like these other stories...

More and more businesses are adopting the cloud in order to take advantage of benefits such as greater efficiency, increased productivity and lower costs. Companies in general are flocking to the cloud for email hosting,...
A great way to increase business for your accounting firm is to remain at the front of your clients' minds all throughout the year. One of the best ways to do that is through an email newsletter. Perhaps you send out a...
Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...

Already a member? log in here.

Upcoming CPE Webinars

Oct 9In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards.
Oct 15This webinar presents the requirements of AU-C 600, Audits of Group Financial Statements (Including the Work of Component Auditors).
Oct 21Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience’s communication style.
Oct 23Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.