To make data entry easier in Excel, 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 on the worksheet. When you create a drop-down list for a cell, it displays an arrow next to 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 Validation command under the Data menu.
- 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.
- If you want to use another worksheet or another workbook, do one of the following:
Use a different worksheet in the same workbook 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.
Use a different worksheet in a different workbook Type the list on that worksheet, and then define a name with an external reference to the list.
- Open the workbook that contains the list of drop-down entries.
- Open the workbook where you want to validate cells, point to Name on the Insert menu, and then click Define.
- In the Names in workbook box, type the name, for example, ValidDepts.
- Accept the default value in the Refers to: box, and then click OK.
- In the Refers to box, delete the contents, and keep the insertion pointer in the box.
- On the Window menu, click the name of the workbook that contains the list of drop-down entries, and then click the worksheet that contains the list.
- Select the cells containing the list.
- In the Define Name dialog box, click Add, and then click Close.
- If several users need to open the workbook simultaneously, set the workbook to read-only recommended when you save it. For more information, see Prompt to open a file as read-only.
- The workbook must be open for users to use the validation list. You can record a macro to open it automatically whenever the workbook with the data validation is opened. For more information about creating and using macros, see About macros in Excel.
- Select the cell where you want the drop-down list.
- On the Data menu, click Validation, and then click the Settings tab.
- In the Allow box, click List.
- 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 in the same workbook or a different workbook, 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.
- Make sure that the In-cell drop-down check box is selected.
- To specify whether the cell can be left blank, select or clear the Ignore blank check box.
- Optionally, display an input message when the cell is clicked.
- Click the Input Message tab.
- Make sure that the Show input message when cell is selected check box is selected.
- Type the title and text for the message (up to 225 characters).
- Specify how you want Microsoft Office Excel to respond when invalid data is entered.
- Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
- Select one of the following options for the Style box:
Type the title and text for the message (up to 225 characters).
- 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.
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."
- To delete a drop down list, select the cell with the list. Click the Data menu, and then click Validation. In the Data Validation dialog box, click the Settings tab, and then click Clear All. Learn more about removing data validation.
- 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 in the same workbook or another workbook and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information, see Display or hide a workbook or sheet and Overview of security and protection in Excel.