Data validation in Excel 2007 – Drop down list

If you want to validate a cell to restrict data entry to values in a drop-down list follow these steps:

  1. Select the cell you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. xl7validation

  4. The Data Validation dialog box will open.
  5. xl7validation2

  6. In the Data Validation dialog box, click the Settings tab.
  7. Click on the Allow box then select List from the drop-down list.
  8. Click the Source box and then type the valid values separated by the appropriate list separator character depending on your installation( usually a comma “,” or semicolon “;”). For example if the cell is for a color of a car then you can limit the values by entering : Silver, Green, Blue.
  9. Instead of typing your list manually, you can also create the list entries by referring to a range of cells in the same worksheet or another worksheet in the workbook.
  10. To specify the location of the list of valid entries, do one of the following:
    1. If the list is in the current worksheet, enter a reference to your list in the Source box, for example enter: =$A$1:$A$6.
    2. If the list is on a different worksheet, define a name for your list then enter the name that you defined for your list in the Source box, for example, enter: =ValidProjects.

Notes:

  1. Make sure that the In-cell dropdown check box is selected. Otherwise, you won’t be able to see the drop-down arrow next to the cell.
  2. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
  3. If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

More Validation options:

  1. Display an input message when the cell is clicked.
    1. Click the Input Message tab.
    2. Select Show input message when cell is selected check box.
    3. Fill in the Title and text for the Input message.
  2. Display an error message when wrong data is entered.
    1. Click the Error Alert tab.
    2. Select Show error alert after invalid data is entered check box.
    3. Fill in the Title and text for the Error message.
    4. Select one of the following options for the Style box:
      1. Information: Display an information message. Does not prevent entry of invalid data.
      2. Warning: Display a warning message. Does not prevent entry of invalid data.
      3. Stop : Prevent entry of invalid data.

Tip

If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so

  1. Open the Data Validation dialog box.
  2. Click the Settings tab.
  3. Select the Apply these changes to all other cells with the same settings check box.

Comments

  1. Excellent site, keep up the good work

  2. admin says:

    Thanks.

  3. NM says:

    I used data validation including drop-down lists in the 2007 format of Excel. when i open the workbook in 2003 format, the validation does not work. Please assist in recifying this without having to redo the entire workbook.
    Thanks

  4. Xin says:

    The entire office is having its computers replaced so I have no choice but to switch to Excel 2007. The first time that I tried to save my worksheet (created in Excel 2003), there was a warning message that all my drop-down lists would stop working after I saved it under 97-2003 format. Any reason for this and any way around it? Thanks!

    • admin says:

      Unfortunately I don’t have an answer off hand, and I have uninstalled office 2003 from one of my machines just two months ago to leave room for office 2010. So I don’t have a chance to simulate your problem.
      Please check EXcel Help Forum. You may find some help there.

  5. Terri says:

    Why isn’t the data validation set up to sort referentially with the rest of a spreadsheet. Instead, the validation stays in the specific cell it was set up in.

  6. ajit says:

    Excellent………………..!!!

  7. Mike says:

    Why is the drop down list so small. It is very hard to read. \
    Thanks in advance for the help.

  8. sanjay Nai says:

    How to apply logical data validation for example : I have one column in Persional Area BIOMASS & second column i have apply data validation Biomass Padampur & Biomass Uniara.

    If the One column Biomass then second column list shown only Biomass Padampur & Biomass Uniara.

  9. Hemdeep says:

    please help me
    i am using an data validation funtion
    and the data is more than two pages
    i have to scroll down and upward to select a particular field

    please help if someone know the search button or seek by typing the alphabets

    • admin says:

      See this tutorial:

      Depending on your data you may be able to split it into groups to make selection easier.
      Other suggestions are welcomed from our readers.