If you have an Excel spreadsheet into which you or other users will enter data, you can use the data validation tool to create drop-down lists in some of the cells. Drop-down lists make data entry quicker and more efficient by limiting the data that you can enter into those cells. By creating the data validation list from a range of cells, you can insert the same drop-down into multiple cells on your spreadsheet at once.
Enter the list items in a column on the spreadsheet where you will not need to enter other data, such as on the far right or bottom of the page. Enter the items in the order you want them to appear in the list, one item per cell. Make sure there are no blank cells between the items.
Select all of the cells into which you want to insert the drop-down list. If the cells are contiguous in one row or column, you can select them by clicking the first cell and dragging your mouse down or across the row or column. If they are in noncontiguous cells, press and hold the "Ctrl" key while you click on each cell into which you want to insert the drop-down list.
Go to the "Data" menu in Excel 2003 and select "Validation." In Excel 2007 or 2010, go to the "Data" tab, click the "Data Validation" button. The "Data Validation" dialogue box will open.
Make sure you are on the "Settings" tab. Select "List" in the "Allow" box. Click inside the "Source" box and then select the range of cells containing the list you created in the first step.
Make sure "In-cell Dropdown" is checked. Clear the "Ignore Blank" check box if you want to require users to enter data into the cell. Click "OK" to insert the drop-down list into all of the selected cells.
Things you need
- Excel 2003, 2007 or 2010