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

Advertisement
  • pad

    very good and easy – thanks a lot

  • Mark

    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?

  • Terribiledonna

    Where is that tool bar my version does not have a data tool bar

  • Awrabideau

    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.

    • Jbr9007

      I have the same problem :( and have no idea why is that happening any help?

    • Suz1e

      Have you received a resolution with this as I have the same issue

  • Awrabideau

    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.

  • PAKS1995

    WHAT IS SHORCUT KEY FOR OPENING DATA VALIDATION LIST

  • Jo

    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?

  • Jo

    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?

  • http://www.facebook.com/anthony.larsson1 Anthony Larsson

    I have 3 lists that is giving me KW, size and production.
    I would like a cell to auto pick up the size from production and if i change production it would auto pick some different size, kW the same, it should auto pick from production list.
    Any ideas, please send me a mail anthony.larsson@hotmail.com

  • Jake

    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?

  • fadel

    It is nice way to explain, but I could not figure out the how to do the FAIL AND PASS COLUMN???? PLS HELP