Automating Data Entry with Data Validation Lists
Data Validation is one of Excel’s most underutilized tools from a data entry perspective, especially its ability to manage list type functions. What is Data Validation? Well it’s essentially a robust set of tools that allow you to define the parameters you want for certain data entry cells. In other words, you can control what users can & can’t enter in the cells that you define.
How many times do you have to enter some of the same information in a form, or run into situations where users butcher what should be normal entries, or they don’t enter data in a format you need? Have you ever wondered if you could come up with a simple solution that would make not only your life easier, but your users’ as well? With Data Validation you can fix almost all of that (although no matter what you do you can always count on a certain population to completely mess up what you’re trying to do).
NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.
The most common use of Data Validation is to populate a cell (or cells) with a list of pre-defined items. Starting with a fairly common example, let’s say you have an expense report with a cell for users to enter their office location. Instead of relying on manual entry (and errors), you can use Data Validation to create a list of locations for them to select instead (Figure 1). I like to add a “helper cell” either above or to the left of the data validation cell to point users to where they should be looking to enter data (in this case “Office Location”):
Setting up your list:
There are two methods of creating lists with Data Validation. The first is a manual method using the Data Validation dialog directly. This is preferable for smaller lists that won’t vary often, like “Yes/No/Maybe”, “Male/Female”, etc. To use this method, select the target cell (where you want the selection to be made), then goto the Data tab–>Data Validation (/avv). On the Settings tab, in the Allow box, select “List”, then in the Source box enter your values, separated by commas, but with no spaces in between. E.G. “Yes,No,Maybe” (Figure 2).
As you can see, this is fairly easy for a short list, but can become cumbersome for larger lists, especially as the nature of the source dialog box is to allow you to select a range on the worksheet, so if you try to use the Home, End or Arrow keys when you’re in the Source dialog box, all of a sudden you’ll find a cell range entered into your list items, and you’ll have to delete it in order for the list to be valid (Figure 2.1). This means that you have to manually select each item with the mouse if you want to replace it, or retype the list.
Note that the two check boxes you see (Ignore blank and In-cell dropdown) will be checked as their default positions, and in general when working with lists you won’t need or want to uncheck them.
Setting up Data Validation Lists
The second method for creating lists is to 1) enter the list in the same worksheet and refer to the range it occupies (Figure 3):
Or 2) the method I prefer is to use an unused worksheet (which can be hidden) and create a named range for the list. Enter your list on the sheet and goto the Formula tab–>Name–>Define (Figure 4):
Named range as a list source
Now you can go back to the Data Validation dialog and refer to the named range (Figure 5):
And you’ll get the same results as if you referred to a worksheet range (Figure 6):
Note: it’s not at all uncommon to forget the “=” when using named range references, but you’ll soon find out you forgot it when you select your data validated cell and your option is “MyList”. All you need to do is open the Data Validation dialog again and add the “=” to fix it.
The nice thing about using named ranges is that you can keep your lists away from your users, by adding a sheet for them, then hiding the sheet with goto the Home tab–>Cells–>Format–>Visibility–>Hide & Unhide). This is also a handy method if you need to maintain data sources hidden from users, like LOOKUP tables and other references.
User Input Message(s):
The second tab on the Data Validation Dialog is the Input Message, which will pop up a comment box on the target cell when it’s selected:
This is probably the easiest part of setting up Data Validation and you set it up through the Input Message tab:
There are two elements to the Input Message, the Title and the Message itself, both of which are pretty self explanatory. Note that there’s an option to show the message when the Data Validated cell is selected, which is pretty useless, as you don’t have to enter anything in there in the first place.
Error Alert Message(s):
The third tab on the Data Validation Dialog is the Error Alert Message, which will pop up a message box anytime a user tries to enter their own value instead of one that you have defined.
The entry for this is very similar to the Input Message:
The same goes for the check box to enable the Error Alert message check box as with the Input Message. If you don’t want to use those features, there’s not really a reason to check the box.
That’s pretty much it for the List feature in Data Validation, but I’ll be adding more stuff about Data Validation soon. If there’s anything you think I forgot in this post, or if you have any questions, please don’t hesitate to ask.
Smitty
Visit My Website
I have 7 groups of items I need to connect in drop-down menues without having all of the groups listed in the first menue. Groups 1,2,3 are the anitial parts, the other 4 are specific options for the original selection?
Visit My Website
Sounds like you’re looking for dependent lists.
See this Hall of Fame post from Excel MVP Aladin Akyurek: http://www.mrexcel.com/forum/showthread.php?t=51098
Visit My Website
Pls. tell me about how to use the background color of list for validation in the area where it is validated?
Visit My Website
It sounds like you’re after Conditional Formatting, which will change cell formatting if a certain condition is met. The helpfile has a good tutorial on it, but here’s a primmer.
Let’s say you want a row to turn green if the value in column A is “Yes” (it can be a validation selection). Select the entire range of cells that you want to apply the formatting to and goto Format–>Conditional Formatting–>Formula Is–>=$A1=”Yes”–>Apply the format you want. In 2007+ you’d goto Home–>Conditional Formatting–>New Rule–>Use a formula to determine which cells to format.
Visit My Website
How do I add a dropdown list to userform using Visual Basic in Excel????? I get the list to appear, but I can’t pick only one choice.
Visit My Website
Here’s an example populating a ComboBox and a ListBox with data in Sheet1 A1:A20:
Private Sub UserForm_Initialize()Dim i As Long
' Populate ComboBox
For i = 1 To 20
UserForm1.ComboBox1.AddItem Sheets("Sheet1").Cells(i, "A")
Next i
' Populate ListBox
For i = 1 To 20
UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(i, "A")
Next i
End Sub
If you want the ability to multi-select, you’ll need to use the ListBox and in its properties goto MultiSelect–>1 – MultiSelectMulti.
Hope that’s what you were after.
Visit My Website
Is there a way to control the number of entries displayed in the list drop down? I would like to show up to twenty before requiring scrolling.
Visit My Website
Nope, not natively anyway; Data Validation is going to show 8 entries and that’s it.
You can do it with a Combo Box though. Excel MVP Debra Dalgliesh has a good tutorial on it: http://www.contextures.com/xlDataVal10.html
Visit My Website
I’ve got my lists down, but is there anyway to limit the number of times a selection is chosen? For example if my list contains 50 names but after that name has been chosen once, I don’t want it to be available in the drop down list for the rest of the column or sheet.
Visit My Website
Sure. Debra Dalgleish has a full write up on how to do it here: http://www.contextures.com/xlDataVal03.html
Visit My Website
Great post. I’m creating a list describing the subject matter of a variety of reports, using a drop-down list to indicate the subject. However, some reports are cross-cutting. Is there any way to select more than one option at a time using drop-downs, having the multiple results appear in the cell at the same time? (perhaps separated by a comma or a slash?)
Visit My Website
Not with Data Validation, but you can use an ActiveX ListBoxand set its Multi-Select property to True (1 – frmMultiSelectMulti).
To pull results in one cell I suppose you could use concatenated VLOOKUPS.
Note that it sounds like it might be more trouble than its worth.
Visit My Website
I have like thousands rows that required me to use data validation list to use as data entry. Now i want to use Combo Box, as it has a feature that I can just type one letter to show all items with the behinning of letters selected
My question is do I need to set the combo box one by one for those thousands rows? Is there any other way to do it
Visit My Website
In the ComboBox’s properties you just need to set the ListFill Range to reflect your range.
Visit My Website
I am just starting with data validation, and finding it very usefull..but how do I go about doign the following:
I can setup a dropdown box with certain brands of equipment with no worries, but in the next coluum, i need to just show the various models from the brand I selected in the previous coluum.eg brand one has models a and b, but brand two has models c and d….how can i set this up, so that only options for c and d would show up because brand 2 was selected previously???
does that make sense to you???
Thanks.
Visit My Website
Mark,
See Aladin Akyurek’s post on Dependent lists: http://www.mrexcel.com/forum/showthread.php?t=51098
Visit My Website
I like the data validation function, but two questions: How do I set the font size and color? How do I refer to it with a VBA event? I tried a sub like Private Sub dropdown1901_Lostfocus()and it didn’t seem to recognize the reference to the dropdown name. Thanks.
Visit My Website
Dennis,
You need to use a ComboBox. See: http://www.contextures.com/xlDataVal10.html
Visit My Website
I would like my dropdown menu to have checkboxes in order to select more than one entry. Then I will want to sort my table based on the entries. i.e. I am working with geographical zones. Some lines will require more than one geo zone entry.
Visit My Website
You’ll either need to build a user form, or you can use a ListBox with its MultiSelect property set to True, but you’re not going to get both natively.
Visit My Website
I want to create a combo box that a user can add to. I have a list of major manufacturers, but the team may have to add to the list. I would like it to update the choices in the combo box with the new manufacturers.
Visit My Website
You can use Dynamic Data Validation. Goto Insert–>Name–>Define–>New. Add your list name and in the refers to box add:
=OFFSET(‘Sheet1′!$A$2,0,0,COUNTA(‘Sheet1′!$A:$A)-1,1)
Where sheet one column A holds the list. A2 implies that you have a header row for your list. When you set up your Data Validation, just refer to that list name. Anytime an entry is added to the list it will be added to your DV list as well.
Visit My Website
I want to create a calendar with two drop downs on each line. One for a name and the other with the job assigned. Help!
Visit My Website
You’re not giving much to go on. I suppose you could refer one Data Validation to an employee list and another to a pre-defined set of assignments?
If you give more specifics I can give more back.
Visit My Website
How to create a sub menu from previously selected option from a list? For example; I have a list of states, then when I choose a specific state, I want to display a list of cities for that state only in a seperate menu (to show only cities of the state selected)
Visit My Website
Take a look at Excel MVP Aladan Akyurek’s explanation of Dependent Lists: http://www.mrexcel.com/forum/showthread.php?t=51098
Visit My Website
Thanks for your comment… it me let me alot, I did the validation list but now i want my list typable as when i enter a alphabet and the all names under the same alphabet appear instead of scroll. I saw u reply on 30/01/2010 but still unclear on how to do it as i could not find any menu under the command of “combobox” . Iam using excel 2007. Kindly look into this. thanks.
Visit My Website
Yes, you need an ActiveX ComboBox to have that functionality. In Excel 2007 goto the Developer tab–>Insert–>ActiveX Controls–>ComboBox. Once drawn on the sheet you can either hit the Properties icon on the Ribbon, or right-click the control. In the Properties window goto ListFillRange and add your range.
If it’s on the same sheet you can refer to just the cell address: A1:A5
If it’s on another sheet you need to add the sheet name: ‘Data Sheet’!A1:A5
Or if you have a named range you can refer to that directly: MyListName
Visit My Website
Abby, sorry for the delay.
If you’re in Excel 2007+ go to the Developer tab. If you don’t have it visible, you need to go into FIle–>Options–>Customize the Ribbon and click the Developer button. Now when you go there you’ll see Insert. From there, select a Combo Box from the second set of controls. Then you use the Properties dialog (Properties button) to assign your range. When you’re done, click off Design Mode.
Visit My Website
Is validation available in windows Mobile?
Visit My Website
I’d seriously doubt it. But you can search around the Office Mobile site: http://www.microsoft.com/windowsmobile/en-us/meet/applications/office-excel-mobile.mspx
Visit My Website
I would like my dropdown menu to have checkboxes in order to select more than one entry. Then I will want to sort my table based on the entries. i.e. I am working with geographical zones. Some lines will require more than one geo zone entry.
Visit My Website
That’s only going to happen with a 3rd party control or if you build your own user form.
But the good news is that you can use a ListBox and set its MultiSelect property to True. That way users can make multiple selections.
You might also want to look at AutoFilter, which allows you to select multiple criteria to filter/display.
Visit My Website
I am using data validation with lists on another sheet. I just realized I need to change the wording of some of the selections. I wouldl ike to change on the list, then have all of the items already select in the data to reflect the new wording, without haveing to remake all of the selections from the dropdowns. Thanks.
Visit My Website
The easiest thing to do would be to create a VLOOKUP table with the old list and the new. Then use VLOOKUP to reference the old list, creating the appropriate matches. After that you can copy/paste special–>values the revised list over the original.
Visit My Website
I’m trying to set up a sign in sheet for staff members. I need to put the day and date at the top with each staff member’s name listed down the left side of the sheet. There will be one sign in sheet per weekday.
How do I set up Excel to automatically change the date?
Thank you
Visit My Website
Sorry for taking so long to get back to you. If this is something you print out weekly, you can probably use =TODAY() for the first date, then add to it for each additional day with =TODAY()+1, or =Sheet1!A1+1 if you want a sheet reference.
Noting that those will be dynamic dates, so if you want static dates you’ll need to use VBA.
Visit My Website
Hi,
Loved the method 2. I’ve created the drop down and done all that is required for displaying an error message. However, i can still enter values that are not present in the list and the error message pop up does not appear as well.
Visit My Website
You need to make sure that “Show error alert after invalid data is entered” is checked on the Error Alert tab.
Visit My Website
Also.. I am trying to automate this procedure as my list will keep changing. What would you suggest is the best way to do it? Is it better to create a macro and then run the macro every time I want to do it or will I have to write a vb script and source the list in it every time?
Visit My Website
Nope, just use a named range. Then anytime you make a change in the range it will automatically update in Data Validation.
Visit My Website
Hi,
The “Show error alert after invalid data is entered” is checked. Is this a bug or is there some other issue?
Visit My Website
I’m not sure. You can e-mail me the file and I’ll take a look. (Sorry for the late reply – I’ve been building a new PC).
Visit My Website
Hi I’ve managed to create a project that automatically opens and excel file then at a pre defined time execute a macro. about half way through i get a message box that asks if i want to replace the contents of a cell. Is there a way i can automate a yes click on this box???
Visit My Website
You can use:
Application.DisplayAlerts = False
Which will suppress those messages. Just make sure to set it back to True at the end of your code because the VBE won’t reset it.
Sory for taking so long to respond, but I just got a new PC up and running after my old one died.
Visit My Website
Can you create multiple choice using a validation list?
Visit My Website
What do you mean by multiple choice? Can you clarify? You can have multiple options in a validation list, but I need a better idea of what you’re trying to accomplish.
Visit My Website
Hi,
I’m trying to create a dependent list, which will allow a price to be selected based on the object’s size. I’m wondering whether it is possible to name a list a number like “1’0 X 2’0″?
Visit My Website
Sorry for taking so long getting back to you. The short answer is No. But you can just create a list with your callouts. E.G.
1’0×4’0
2’0×4’0
3’0×4’0
4’0×4’0
And name it something like “Sizes”, then for your data validation Source just use =Sizes and your list of callouts will show up. You can then use a lookup table to reference the correct price. Note that if you’re pricing by UI you’ll also need to convert the callout size. I also have a method for doing that and showing an actual callout with the “oh” in superscript. I’ll be happy to send you an example if you want.
Visit My Website
Hi, In reference to the post on July 15, I have a list whose values keep on changing. The problem is that values selected in the dropdown (before the change) do not change automatically. How do I automate this procedure ?
Thanks
Visit My Website
The only way I can think of offhand would be to build a reference table to show what the old values are in relation to the new ones. E.G.
Dog – Mastiff
Cat – Calico
If you have that it shouldn’t be too hard to use the Find method (VBA) to loop through the existing selections, compare them to your list and swap out the new value if a match is found. If you post back with some specifics (cell range for the currently selected validation items and the range for the swap list) I can probably put together an example.
Visit My Website
Maybe you should edit the webpage name title SmittyPro » Automating Data Entry with Data Validation Lists to more suited for your blog post you write. I loved the blog post all the same.
Visit My Website
Hopefully I’ll have more time to expand soon.
Visit My Website
[...] SmittyPro » Automating Data Entry with Data Validation Lists [...]
Visit My Website
Is it possible to create data validation where more than one option is selectable. For example, I have four different possibilities, AP, AR, Automation and P2P. Some cells may be only one type, where others may be all four.
Visit My Website
Not with Data Validation. For that you’d need to use a Multi-Select List Box or Combo Box (preferably ActiveX controls). Here’s a good article on dealing with them from Dick Kusleika’s Daily Dose of Excel: http://www.dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/
Visit My Website
I have created a form with several Data Validation lists that are quite lengthy. Is there a way to get the lists to take me to whatever letter I am typing instead of having to scroll through the list and select an item?
Or, is there a better way of doing this altogether?
i.e. We have about 200 company names that we need to select from. I would like to start typing a name in the cell and have the corresponding names pull up from my list.
Visit My Website
If you use an ActiveX ComboBox you’ll get that type of behavior. Just set the ListFillRange to match your data validation list’s range. Post back if you need more help setting it up.
Visit My Website
Hi, I have created a data validation list, but I need to keep adding to this list as more stock items are added, how can i ensure the list is updated to take on the new entries in the data validation selection. So for instance I have names a range 12 to 20, but I now add an item in 21 how can this be reflected in the data validation. Many thanks Paul.
Visit My Website
Hey Paul,
If you create a named range, then in the Refers to: dialog box you can add:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Where Sheet1 houses your validation list and it starts in A1.
Then in the Data Validation options you can simply refer to the list name like you normally would, but as you add items to the list, they’ll be reflected in the Validation as well.
Visit My Website
Is it possible to do this where typing in the cell can can add to the list for future use?
Visit My Website
Sure, you can use a Dynamic Named Range. Debra Dalgleish details it here: http://www.contextures.com/excel-data-validation-add.html
Visit My Website
I’ve kind of got the same issue except that on my months list, I get the error message pop-up if I enter a non-existent month. Even though I’ve got it capitalized in my months list, it still allows me to type the months in lower case. My goal is to force the used to select from the drop-down and not manually enter it. Any suggestions would be greatly appreciated.
Visit My Website
Unfortunately, there’s no way to prevent a user from making an invalid entry, since validation can’t evaluate an entry until after it’s made. Then you get the error message.
You could use a sheet activate event to pre-populate the dv cell with the current month if that would help. That way there’s already a selection made.
Visit My Website
Hi
I was wondering if it is possible for a list to be displayed in colour for example:
No – red
Yes – green
Na – orange
Visit My Website
I’d use Conditional Formatting on the validation cell. CEll Value=–>Equals–>”No”–>Red, etc.
HTH
Visit My Website
http://www.contextures.com/xldataval02.html
more useful than given explanation
Visit My Website
I am using data validation list that requires a whole number equal to or greater than 1. I want to copy the list to several rows, but don’t want to see the list data value until my cursor is on the list for each row.
Visit My Website
A Validation List won’t show its contents until you click on the drop-down for each cell.
Visit My Website
Hi Smitty,
Thanks for your fabulous post. We have a validation xls which is fab, however we have multiple clients that require a differing internal review panel for pieces of work… there are staff who need to update the form to reflect who the reveiw panel is (i.e. per customer there are a couple of panellees who differ from the standard group, but knowing who they are can be a nightmare. We have them listed in the validation, but we’d like them to do the form within an outlook template, so we as admins can just send the email directly to the review panel. Can the datavalidation be ‘exported’ or transferred into Outlook to work as per xls, or do I need macros etc?
Visit My Website
I don’t know if you can export the validation itself to Outlook, but you can create custom forms in Outlook. You could export the list from Excel to Outlook and use a VBA combo box. But I’m just guessing on that as I haven’t worked behind the scenes in Outlook in years.
Excel MVP Ron DeBruin is the real genius when it comes to programming between Excel and Access. http://www.rondebruin.nl/tips.htm
And he’s a nice guy and should respond to questions.
Smitty
Visit My Website
I want to change the footer automatically, by selecting from the drop down list.
Visit My Website
You would need to use the BeforePrint event to read the footer. You can record applying Page Setup up with a custom Header/Footer, then modify the code to use the validation cell. E.G.
With ActiveSheet.PageSetup
.CenterHeader = Range("A1").Value
.CenterFooter = Range("B1").Value
End With
Visit My Website
I have validated a data in a cell, while printing the sheets, i want that cell data in Header / Footer. What can i do?
Visit My Website
I validated a data(35Nos), thru validation, i retrieved from various sheets. so 1 sheet contains all data. Now i want to separate the data in a sheet to another excel sheet i.e. 35 Sheets.
It is possible in MSWord Using, Mail Merge, we can separate data and converted to 35 pages. Each page contains 1 data pertaining to particular items.
Visit My Website
I made a data validation list (say in cell A1) where the list source is on a different sheet. In B1, I want to automatically display the value of the cell in the next column over from the source of the data validation list. I know I will need to use the OFFSET function but I don’t know how to refer to the source address of a specific choice in the data validation list. My data validation list source is dependent on other items in my spreadsheet, so I would like cell B1 to populate automatically once a choice is made in the data validation list.
Thanks for your help!
Visit My Website
You can use VLOOKUP. If you do a search on the blog for VLOOKUP you’ll find a detailed article on how to set it up.
HTH,