1. Home
  2. MS Office

Excel 2010: Data Validation List

When you are working on a datasheet inputting the same type of data in the cells, it would become very monotonous to type same data over and over again. To prevent typing same data, you can make a list of data that will be used for any number of times, Excel 2010 provides a  Data Validation feature which is very handy for making lists.

Open a Excel datasheet in which you want to create data validation forms. We have included Student Evaluation datasheet worksheet as shown in the screenshot below.

excel form

Now enter some data in other fields that eventually matches with data type of  Name, Course and Grade fields respectively. As an example we have created three new fields as shown in the screenshot below.

sample data

Now Select the Name field and switch to Data tab and click Data Validation.

name field

data tab 1

Click Allow drop-down button and select List from the Data Validation dialog. Select source field from where you want to make a list.

allow drop down 1

Now select the field and click button in Data Validation dialog. You can select multiple fields by holding down Ctrl key.

select source 1

You will reach Data Validation Dialog again, click OK to include list in Name field.

click ok list

You will notice that a drop down button is attached with every cell in Name field that include names of the students.

name filed list button

Click the drop down button to fill the Name field.

filed list filled

Now repeat the above process with Course and Marks field to insert list of courses and grades in to respective fields as shown in the screenshots below.

course field 1

grade list 1

Now you can see that we have populated Name, Course and Grade fields by applying Data Validation through list.

table marks list

13 Comments

  1. This is close to what I need but I’m looking for something slightly different:

    I have this already set up on one worksheet, corresponding to a list on another worksheet, but I’d like to have my user to be able to select multiple options on the dropdown list and then, after selecting them, have those choices fill the cell. Any help?

  2. I want to colour sonme of the fields. once I have made the drop down menu they revert to black. Why? How can I eep the coloured text?

  3. I want to colour sonme of the fields. once I have made the drop down menu they revert to black. Why? How can I eep the coloured text?

  4. I have followed these steps, however, when I close and re-open the same file, all of my drop-down boxes are no longer there.  Why is this happening?  In the same document, some cells have only “instructions” no drop-down boxes and the instructions come up when you click on the cell, so those are saving, but the drop-down boxes are not.  Thank you for any help.

  5. I have followed these steps, however, when I close and re-open the same file, all of my drop-down boxes are no longer there.  Why is this happening?  In the same document, some cells have only “instructions” no drop-down boxes and the instructions come up when you click on the cell, so those are saving, but the drop-down boxes are not.  Thank you for any help.

  6. Use of lists etc are great and easy to set up. I’m wondering if there is a way to indicate on the cell that there is drop down list, i.e. an actual drop down arrow?

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.