How to Change Data in a Drop-Down List in Excel 2007
Drop-down lists in Excel allow you to control the data entered into a cell by having users choose an item from a specified list. The data within the list is always pulled from another cell, either within the same workbook or in a different workbook. Once you find this list, you can edit it as necessary to change your drop-down list data. Drop-down lists fall under the data validation set of commands in Excel 2007.
Click the cell that contains your drop-down list, and then click the "Data" tab on the Office ribbon.
Click "Validation," and then click the "Settings" tab.
Find the "Source" box. This will tell you the name of the list's source if it's in a different worksheet from your current sheet, or its location on the current page. Most likely there's simply a name, such as "=Colors" for a list of colors. This is called a range name, and the cells within that named range are what you need to edit to change your list.
Close the validation dialog box and locate the named range from your source box. It may be in a different worksheet, often labeled "Tables" or "Lists." If you can't find it, use the "Find" function in the "Home" tab to search for a word from the list in each spreadsheet.
Edit your list as necessary once you find the source range by deleting, altering or adding items to the list in this location just as you would in any cell in the workbook. If you alter only data within the existing range, such as changing a list of "Red," "Yellow" and "Blue" to a list of "One," "Two" and "Three," you're finished. The new list will appear in your drop-down once you save your changes. If you delete or add items, you'll need to edit the range.
Note which cells your newly edited range covers. For example, if the old list fell in cells A2 through A5 and you added two more items, it will now cover cells A2 through A7.
Click the "Formulas" tab on the Office ribbon, and click the "Name manager" button.
Click your range's name from the list and click "Edit" at the top of the box.
Change the cell identifiers in the "Refers to" box to match your new range. In the example noted, look for the "A5" in the "Refers to" box and change it to "A7."
Click "OK." Your drop-down list will reflect your changes.
Tips & Warnings
- In Excel, a range is a collection of cells that are grouped together for some purpose, such as adding, subtracting, naming or performing other functions.