In Microsoft Excel, a drop-down list is used to enter data into a spreadsheet from a predefined items list, limiting the number of choices available to the user. It prevents spelling errors, makes data input faster, and allows for easier spreadsheet filtering. In this Excel tutorial, we’ll demonstrate how to create a drop-down list using the application’s data validation feature.
In this definition...
What is data validation?
In computing, data validation is the process of ensuring the accuracy and quality of data. Within Excel (and also Google Sheets,) it’s a feature that restricts the type of data or the values that users enter into a cell. It allows users to set parameters around numbers, dates, and text to prevent inputting errors.
Among other things, data validation is used for the following:
- Restricting entries to predefined items in a list
- Restricting numbers outside a specific range
- Restricting dates outside a certain time frame
- Restricting times outside a certain time frame
- Limiting the number of text characters
- Validating data based on formulas or values in other cells
- Stopping invalid user input
How to make a drop down list in Excel
- In a new worksheet, list the entries you want to appear in the drop-down list. For this example, we’ll use students’ favorite candy.
- In a different worksheet or somewhere else within the current worksheet, select the cell(s) that will function as the drop-down list.
- Go to the Data tab on the ribbon at the top of the screen, then select Data Validation.
- On the Settings tab (the default tab that appears first), in the Allow box, click List.
- In the Source box, select your list range by clicking into the worksheet and selecting the group of cells containing the items required for the drop-down list by holding down the shift key.
- If it’s permissible to leave cells empty, check the Ignore blank box.
- Check the In-cell dropdown box.
- Click the Input Message tab. If you’d like a message to pop up when the drop-down cell is clicked to give instructions, check the Show input message when cell is selected box. Add a title and short message. If you don’t want a message to pop up, uncheck the box.
- Click the Error Alert tab. If you want an error message to pop up when someone enters a value that is not on the predefined list, check the Show error alert after invalid data is entered box. Enter a title and message. If you don’t want a message to show up, uncheck the box.
An error message like this will appear: - Press OK after all settings are configured. You’ve now created a drop-down list. Click one of the cells in which you’ve configured data validation, and select the appropriate value.
Also read: How To Outline Cells With Borders In Microsoft Excel
Excel drop-down list FAQ
Can I make a drop-down list without listing the values in the spreadsheet?
Yes. Follow these steps:
1. Select a cell range for your drop-down list
2. Go to Data > Data Validation.
3. Within the Settings tab, Select List in the Allow box. Make sure the In-cell dropdown box is checked. In the Source box, type the items you want to appear in your drop-down menu separated by a comma. This can be done with or without spaces.
4. Click OK.
Why is the data validation option greyed out or disabled?
There are a few reasons why the data validation button at the top of the screen may be disabled:
– Drop-down lists can’t be added to protected or shared worksheets. To enable data validation, a user must remove the protection or stop sharing the worksheet.
– Drop-down lists created from an Excel table cannot be created if they are linked to a SharePoint site. To enable data validation, unlink the table or remove table formatting.
Can I add color formatting to my drop-down list?
Users can add color formatting by using the Conditional Formatting feature available in Microsoft Excel.
1. To add a conditional rule that changes the font color of a specific item in the list, go to the Home tab, and click Conditional Formatting in the Styles section.
2. Choose New Rule and highlight the Format only cells that contain option.
3. In the lower section that appears, change the far left drop-down setting that is set to Cell Value by default to Specific Text. Make sure the second drop-down menu reads containing.
4. In the far right box, type the name of the item you want to have color formatting.
5. Click the Format button at the bottom. Under Font, choose your color.
6. Click OK.
Can I make multiple selections within the drop-down list?
Users cannot select multiple items within a drop-down list. There is no way to do this with Excel’s built-in features. The only way to do this would be to add Visual Basic for Applications (VBA) code to the drop-down list itself.
Read next: How To Use A Fill Handle In Excel
