Drop-downs make data entry faster and cleaner. They’re the perfect way to keep spreadsheets clean and tidy and make entries consistent for fields that allow it.
Here’s how to create a basic drop-down list in Excel to streamline data entry.
Step 1: Choose the sheet where you’ve put the list of items.
It’s ideal to put them on a different sheet to keep your main sheet cleaner. We put our list items on the List Options sheet.
Step 2: Highlight the cells you want to convert into a list.
Step 3: Click in the Name Box and name the highlighted cells something you will remember.
We named it Status because we’re creating a list for the Status column in our spreadsheet. Naming the fields makes it easy to reference the correct cells for your drop-down list and makes it easier to add new items later.
Step 4: Open the sheet where the drop-down list will be located.
Step 5: Highlight the cells you want to turn into a drop-down.
Note: You can always extend this later by using the fill handle (the small square in the lower right of the selected cells)
Step 6: Click the Data tab in the Excel ribbon.
Step 7: Click the Data Validation button.
Step 8: From the Allow drop-down in the pop-up, select List.
Step 9: Type the source for your drop-down list in the Source box.
The source will always be the equals sign, followed by the name assigned to the group you selected in step 3. For this example, we named the list Status, so we’d type =Status.
Note: You can also type cells in this box or use the button on the right side of the Source box to select cells.
Step 10: Click the OK button.
That’s it, you’re done!
Now you have a drop-down list with the options from the List Options sheet in Excel. The cells in the drop-down you set up can be easily selected from the list.
You can now create drop-down lists for everything!
You can even set up multiple lists on a secondary sheet, making it easier to keep everything organized and add additional options later.
The attachment to this article lets you download the final version with a drop-down list and also the color-coding of Excel rows based on a cell value article.