Data Validation Using Lists in Microsoft Excel

In the last MS Excel training video we learnt how to use data validation to avoid errors in the data entry of the students marks by limiting the entry between 0 and 100.
We will learn now how to use data validation with lists to avoid errors and also save time during entry of students names.

We first select a single cell or a range of cells
Next we click on Data Validation icon in the Data tools group of the data tab
From the drop down menu we select ‘data validation…’
In the window called Data Validation that pops up that pops up we select the Settings tab
In the text box below ‘Allow’ where you see ‘any value’ click on the drop down arrow
Select List
The text box under Data is greyed out so here we enter nothing
In the text box under source we type the names of the students separated by commas
We recheck the spellings of the students names thoroughly so that there are no spelling errors
We typed John, Barbara, Leslie, Tom, Karin as examples
Click on OK

When you return back to your Excel worksheet you’ll see a drop down arrow next to the cell(s) where you applied the data validation. If you click on the drop-down arrow the names you typed in the ‘Source’ will be visible as a list one below the other in the same order as we typed them. If we now click on one of the names it gets selected and is transferred to the cell. If you selected the wrong name just go back to the drop down arrow and select the correct name. Now you can see that you need to type the list once and you can use it repeatedly. Also the process of entering the names is quicker and easier using data validation.
Later we’ll learn how get a list of names as source by using data in a worksheet.

View the Excel training video:


Further reading:
Create Dependent Drop Down Lists

Leave a Reply

Your email address will not be published. Required fields are marked *