About Microsoft Excel

Data Validation Using Lists in Microsoft Excel

Home Back Next

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 dalta 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 'anay 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 speelings 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.
Home Back Next