Create a drop-down list from a range of cells Excel 2007

Author: mety Nagm Labels::

A drop-down list created by using data validationTo make data entry easier, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab.

  1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:

    Note You may want to sort the data in the order that you want it to appear in the drop-down list.

  2. If you want to use another worksheet, type the list on that worksheet, and then define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the list.

  3. Select the cell where you want the drop-down list.
  4. On the Data tab, in the Data Tools group, click Data Validation.

    Excel Ribbon Image

    ShowIssue: The Data Validation command is unavailable.

    An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

    You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

    The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. For more information about how to stop sharing or remove protection from a workbook, see Use a shared workbook to collaborate and Change or remove protection of worksheet or workbook elements.

  5. In the Data Validation dialog box, click the Settings tab.
  6. In the Allow box, click List.
  7. To specify the location of the list of valid entries, do one of the following:
    • If the list is in the current worksheet, enter a reference to your list in the Source box.
    • If the list is on a different worksheet, enter the name that you defined for your list in the Source box.

    In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.

  8. Make sure that the In-cell dropdown check box is selected.
  9. To specify whether the cell can be left blank, select or clear the Ignore blank check box.
  10. Optionally, display an input message when the cell is clicked.

    ShowHow to display an input message

    1. Click the Input Message tab.
    2. Make sure that the Show input message when cell is selected check box is selected.
    3. Type the title and text for the message (up to 225 characters).
  11. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

    ShowHow to specify a response to invalid data

    1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
    2. Select one of the following options for the Style box:
      • To display an information message that does not prevent entry of invalid data, click Information.
      • To display a warning message that does not prevent entry of invalid data, click Warning.
      • To prevent entry of invalid data, click Stop.
    3. Type the title and text for the message (up to 225 characters).

      Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."


  • The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
  • The maximum number of entries that you can have in a drop-down list is 32,767.
  • If the validation list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet.



Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2