If there are instances when you want to restrict the data that users can enter into certain cells, you might want to create a drop-down list. This allows people to choose from a predefined list of choices. Doing so makes it more efficient for the person entering data and can help eliminate errors or redundant data.
To create a drop-down list in Excel, you will first need to create a list of the entries you want to appear in the list in a separate worksheet or in another area of the current worksheet and then use the Data Validation feature to create the list. Once you create your list of values, you may wish to hide the list or protect the cells from edits.
Quick Guide: To Create a Drop-Down List in Excel
- In a new worksheet, enter the values you want to appear in your list. Each value needs to be in its own cell with no blank cells. It's a good idea to sort your list alphabetically.
- Select all the entries in the list.
- In the Name box (to the left of the Formula Bar), type a name for your list. Note that names cannot contain spaces.
- Click in the cell where you want your drop-down list to appear.
- Click the Data tab on the Ribbon and then click the Data Validation icon on the Data Tool group.
- Click the Allow drop-down arrow and choose List.
- In the Source box, type an equal (=) sign followed by the name that you have to your list (in step 3).
- If you want a message to appear when the cell is clicked, click the Input Message tab and enter in a title and the message in the boxes.
- Click OK. If necessary, widen the column so that the cell will accommodate the widest entry.
Looking for more Excel tips? Our Excel 2016: Up To Speed guide is now available in printable PDF format from this site. The Up To Speed series is designed to help you get productive quickly with your software. Check back as new titles will be added regularly. Also available as an ebook from Amazon, Kobo, and Apple iBooks