How to Make a Drop Down List in Excel

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. 

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

  1. 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.

  2. In a different worksheet or somewhere else within the current worksheet, select the cell(s) that will function as the drop-down list.

  3. Go to the Data tab on the ribbon at the top of the screen, then select Data Validation.

  4. On the Settings tab (the default tab that appears first), in the Allow box, click List.

  5. 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.

  6. If it’s permissible to leave cells empty, check the Ignore blank box.

     

  7. Check the In-cell dropdown box.

     

  8. 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.

  9. 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:

  10. 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

Abby Braden
Abby Braden is an award-winning writer and editor for websites such as TechnologyAdvice.com, Webopedia.com, and Project-Management.com, where she covers technology trends and enterprise and SMB project management platforms. When she’s not writing about technology, she enjoys giving too many treats to her dog and coaching part-time at her local gym.

Top Articles

The Complete List of 1500+ Common Text Abbreviations & Acronyms

From A3 to ZZZ we list 1,559 SMS, online chat, and text abbreviations to help you translate and understand today's texting lingo. Includes Top...

Windows Operating System History & Versions

The Windows operating system (Windows OS) refers to a family of operating systems developed by Microsoft Corporation. We look at the history of Windows...

How to Create a Website Shortcut on Your Desktop

Website Shortcut on Your Desktop reviewed by Web Webster   This Webopedia guide will show you how to create a website shortcut on your desktop using...

Generations of Computers (1st to 5th)

Reviewed by Web Webster Learn about each of the 5 generations of computers and major technology developments that have led to the computing devices that...

Veeam

Veeam Software is a global vendor...

Akamai

Akamai Technologies is a global web technology company specializing in content...

Podcast

A podcast is an episodic form of digital audio content that...