Automating Data Validation Lists in Excel

Share this content

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.
Let's first take a look at 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 dialog box in Figure 3 will appear. 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.
Figure 3: Choose List and then specify your range name as the Source 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," as shown in Figure 4. It's not necessary to use quotation marks in the Description field.
Figure 4: Although optional, the Input Message tab allows you to document the validation.
Finally, on the Error Alert tab, enter a Title, such as "Invalid Input," and an Error Message, such as "You must make a selection from the list," as shown in Figure 5. Keep the style set as Stop to prevent the user from bypassing the list items. Click OK to close the Data Validation dialog box.
Figure 5: Be sure to complete the Error Alert tab; otherwise, invalid inputs will trigger a generic and cryptic error prompt.
Going forward, when you click in cell A2, a note should appear as shown in Figure 6. This documents the spreadsheet. If the user makes a valid choice, he or she will then be able to move to another cell; otherwise, the prompt shown in Figure 7 will stop the user in his or her tracks.
Figure 6: Our choices on the Input tab yield a helpful prompt when the user clicks on cell A2. 
Figure 7: The user will see this prompt if he or she tries to enter something that doesn't appear on the list.
Now, return to your original list, and add two more items, as shown in Figure 8. When you return to your cell with the Data Validation list, the new items should appear automatically. The combination of the table/list features and range names automates this aspect. Within the Data Validation dialog box, you can provide a specific set of cell coordinates in the Source field, but you would then need to manually change the setting if you later expanded the list. Further, Excel 2007 and earlier won't allow you to reference cell coordinates on other worksheets. Best practices in spreadsheet design call for separating supporting lists from the actual data on separate worksheets. Anyone using Excel 2007 and later is no longer faced with the struggle of where to safely position supporting lists for Data Validation on the same worksheet as the actual input is occurring.
Figure 8: Since the ultimate source for a validation list is a table/list, new items appear on the Data Validation list automatically.
Read more articles by David Ringstrom. 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.


Please login or register to join the discussion.

Excelant :D Just what I was looking for. Thanks man.

Thank you for the feedback! I'm glad you found my article helpful.

Hi David,

First of all, Thank for your article, really it helps lot.
I have one question in Drop down list in Data validation.. I have around 1500 in the list with names started "A to Z", I want to select one of the names with started "K". here I could not go directly "K", i will have to click n number of page downs.. hope you understand what i am questioning you.. rquest you to help me o this.

You're going to have to be a bit inventive here and insert 1500 blank rows beneath your Data Validation cell that you populate with your list. You'll then hide those rows. Having the list entries adjacent to the cell you'll type in enables the Excel feature that shows you related items you've typed in the adjacent area. Data Validation then prevents the user from making an invalid entry.

The other solution is to use a Combobox form control, but you can't access those from the keyboard, you have to click in the control with your mouse but then you can type the first letter and navigate the list.

I am sorry to say that I unable to understand "Having the list entries adjacent to the cell you'll type in enables the Excel feature that shows you related items you've typed in the adjacent area." this line.. can you please give me some more brief on this..

Go to a blank spreadsheet and type Red in cell A2, White in cell A3, and Blue in cell A4. Type the letter B in cell A1 and you'll see Excel offer to fill the cell with Blue. Add more items to rows 4 through 10 such as Rad, Brew, Whale, etc. and as you type two or more characters in cell A1 you'll see the prefill options change. Then apply this concept to your spreadsheet, but with placing all 1500 list items in hidden rows immediately adjacent to your data validation cell. It's not elegant, but works within the constraints of Data Validation.

Hi David,

I am very much clear.. It was wonderful idea.. how ever my case is quite different.. I will give some samples in the list here


I am really sorry for disturbing you again and again.. kindly help me.. o

We have a new forum section that is best suited to situations such as this where you need more assistance than I can provide in this context. Post your query here in the Technology section and perhaps another forum user will have the free time to assist you. Good luck with your project!

Hi David,

Thanks for your guiding on this..I will do it

Excellant thanks

My pleasure!

Great. Helped a lot. Clever.

Thanks for the feedback! I'm glad to be of assistance.

I'm building spreadsheets from a VBA macro in another app. I would like to build a sheet with my list values and hide the page, but I can't seem to add the drop-down menus on the user page. I can record a macro that creates the list and drop-down menu, but I can't seem to translate that to the original VBA macro I'm building.

This code will create a new sheet, build a list and name it.
(Note only the last five values are included in the list.)

Set HiddenValuesSheet = Excel_App.ActiveWorkbook.Worksheets(1)
With HiddenValuesSheet
.Name = "menus"
.Visible = False
.Cells(1, 1).Value = "Status"
.Cells(2, 1).Value = "Completed"
.Cells(3, 1).Value = "Draft"
.Cells(4, 1).Value = "Signing"
.Cells(5, 1).Value = "Corrected"
.Cells(6, 1).Value = "Electronically Filed"
.Names.Add Name:="Status", RefersToR1C1:="=menus!R2C1:R6C1"
End With

Now I create a new page, but attempting to make a cell display a drop-down menu fails with "Run-time error '438': Object doesn't support this property or method". Obviously I'm doing something wrong :/

With DataSheet
.Name = "All Data"
.Cells(2, 2).Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Status"
End With

You didn't say which version of Excel you're using, but in Excel 2007 and earlier your lists can't reside on another sheet. Also, your line of code .Name="All Data" is likely causing an error because range names cannot have spaces in them. I'd consider eliminating the hidden list and embedding it into your Data Validation code. Also notice that you didn't call Data Validation itself, so the line that starts with .Cells(2,2).Add is invalid, and should be .Cells(2,2).Validation.Add:

Thanks for the quick reply! First, I'm using "Microsoft Office Professional Plus 2010", but thanks for that, I didn't realize how new the 'lists on separate tabs' feature was. The name was for the sheet, not the list, but I'll be sure to keep spaces out of the list names if I ever use them again ;)
Thank you for showing me how to build a validation list on the fly. I had mistakenly assumed that a drop-down menu was only entered as a named list in a separate sheet. Now, instead of building an entire page and hiding it, I'm adding the drop-downs to the end of each row with a single line:

With DataSheet
DataSheetY = DataSheetY + 1
.Cells(DataSheetX, DataSheetY).Delete
.Cells(DataSheetX, DataSheetY).Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "Completed,Draft,Signing,Corrected,Filed"
End With

Thank you so much!

You're welcome! It was a fun question, and I'm glad I could help.

I have used ranges in other sheets as validation on another sheet and as lists, and they work fine. However they do not work on 2007, which is why your article is a lifesaver. :-)

I'm so glad you found my article helpful. As you found Data Validation lists must reside on the same worksheet in Excel 2007 and earlier. The Table feature makes it a set-and-forget approach in Excel 2007 and later.

Just like to say 'Thank you' for this article. I was trying to use ranges and was planning on using dynamic ranges as the validation criteria, but excel 2007 in work would not allow it. Absolute cell references were find as the validation was on another sheet.

I appreciate your feedback. Dynamic ranges are a viable solution as well, but it's hard for the uninitiated to wrap their brain around.

I have a list of categories which some of them contains white spaces, for example, Green Fruit - Apple, Grapes, and so on
and some of the category start with number, I can't use them as the Name because the name distriction, need serious help on this~

You'll have to be inventive and use underscores "_" instead of spaces in your range names. Preface the ones that being with numbers with a \. You can then use the SUBSTITUTE function to replace the _ and \ characters as needed. You might also have to use the INDIRECT function as well. A great message board to post your question to is if the aforementioned doesn't give you enough to work with.

Hi. Is there a way (excel formula or VBA) wherein I could choose from a drop-down list in Cell 1 and then with my choice, a unique set of items (in drop-down) on Cell 2 will appear?
Cell 1 - White, red
If i choose white, cell 2 should have a drop-down with 1,2 and 3
If I choose red, cell 2 should have a drop-down with 4,5,and 6.

Thank you!

Yes you can,
Put a Name to your lists >Formulas - Defined Names - Define Name<. On Cell 1 in the Data validation Source enter your main list name, then on Cell 2 in the data validation Source enter "=INDIRECT(cell1)" . This will show the drop down for the list you have chosen on Cell1.
List "Main":
- Red
- White
List "Red":
- Apple
- Strawberry
List "White":
- Eggs
- Coconut
On Cell A1 data validation source =Main
On Cell A2 data validation source =INDIRECT(A1)

I somehow missed the notification on this question. Thanks for sharing the solution, Carlos!

Hi, Is there a way where in the user can only use the validation on the cell and is not copy paste some other value.

Unless you go really deep and do some programming in Excel to disable the copy and paste commands every where you can't block someone from pasting over your data validation. But, if you unprotect your data validation cells, and then protect the worksheet, you'll at least force them into having to go into another worksheet to put the invalid value in. Further, when you get the spreadsheet back you can use the Circle Invalid Data feature on the Data Validation submenu to immediately identify any invalid values. Generally protecting the worksheet will keep most users in line with regard to Data Validation.

It requires some non-trivial VBA, but you could add a Worksheet_Change event handler to the sheet to validate the data using the Target cell and the named range of the validation table. This gives you a lot of options on how to handle invalid data - a pop-up error message (msgbox) or moving the cursor back to the invalid cell are two.

For situations where a simple warning will suffice, I've used conditional formatting to automatically highlight a cell with an invalid or missing value. Here's the formula:


Tom the Toolman

I was puzzled at first about the assertion that for Excel 2007 and earlier, "data validation lists cannot be placed on other worksheets". I routinely do this in both 2003 and 2007 and it seems to work fine. I simply use a defined name for the range of cells that covers the data for the validation list.

It turns out that the issue is due to the "table" or "list" feature in Excel. As I didn't know about this feature, I had never used it. (BTW thanks for that into - I love learning new stuff). I simply used a named range for my validation data.

Of course, when I want to insert items, I must be sure to insert *within* the list so that the named range is updated properly. Often, I then have to copy/paste or sort the data to put it where it needs to be in the list. But I inadvertently avoided the "different sheet" issue this way.

So I thought I'd mention this for users who really want to put the data validation list on another sheet. Just use a global named range for the values and it'll work fine. Just be sure to insert new entries between existing ones so that the defined name gets updated as needed.

Tom the Toolman

The Table feature makes it a set-and-forget approach in Excel 2007 and later

Register multiple betting accounts

With odds on each of your selections multiplied with your stake to determine the potential winnings, even the slightest difference between prices can seriously increase or decrease your returns. It is, therefore, crucial to open betting accounts at multiple bookmakers, since this strategy will ensure your bets are placed at the best available odds week in week out. And considering that you will mostly be backing odds on favourites, doing some research on which bookies consistently offer the best prices on top sides could pay off big time.

Thanks for the insight. I know Data Validation is a "restriction", but I want to be able to add to (and alphabetically sort) my "fruit list" (table) from the cell A2 on my "first workbook". Is that possible? Perhaps this is another function?