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...

Regulatory compliance, risk management and cost-cutting are the big heartburn issues for finance execs in the C-suite. Yet financial planning and analysis—a key antacid—is insufficient.That's just one of the...
Continuing its efforts to simplify accounting procedures, the FASB has issued a proposed Accounting Standards Update on customer fees paid in a cloud computing arrangement. The newly-proposed update (Intangibles—...
How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...

Already a member? log in here.

Upcoming CPE Webinars

Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 10
Transfer your knowledge and experience to prepare your team for the challenges and opportunities of an accounting career.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.
Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.