Skip to main content

How to create a drop-down list in Excel

Implementing a drop-down list in Excel is a quick and efficient way to choose predefined data. In the process, you’re able to save time from manually entering such data into a given spreadsheet. Drop-down lists are perfect for several purposes, such as inputting information into a form.

Here's how to create a drop-down list in Microsoft Excel.

Related Videos




5 minutes

What You Need

  • Microsoft Excel

  • PC

Create a drop-down list by selecting a range of cells

The most common way to create a drop-down list in Excel with multiple selections is by using a range, which relies on using data from other cells.

Step 1: Choose a column where you want to include the data that will be shown in the associated drop-down list. This can be from the same spreadsheet where the drop-down list will be located, but you can also use a separate spreadsheet (add a new spreadsheet at the bottom). The latter option will naturally make your primary spreadsheet look more tidy, professional, and less cluttered.

Step 2: Type all the data entries into the column, with each entry having its own cell.

Inputting entries for a drop-down list on Microsoft Excel.

Step 3: Select the cell where you want the drop-down list to appear.

Selecting the cell for a drop-down list on Microsoft Excel.

Step 4: Go to the Data tab and select the Data validation button or choose Data validation from the drop-down menu.

The Data Validation button on Microsoft Excel.

Step 5: Within the Allow menu, select List. An arrow will be displayed on the edge of the Source field. Select that Arrow.

The List option within the Allow menu on Microsoft Excel.

Step 6: You’ll be taken back to your spreadsheet’s main view. From here, simply select the range of cells by dragging the cursor from the first cell down to wherever your last cell is located.

Selecting a range of cells for a drop-down list on Microsoft Excel.

Step 7: In the Data validation mini pop-up window, select the Arrow button. ​​The Source bar will include the entire cell range that you select in the above steps. Choose OK.

You will now have a drop-down list menu located in the cell you chose from step 3.

The ​​Source bar that includes the range of cells required for a drop-down list.

Create a drop-down list by manually entering data

There are a few ways to create a drop-down list on Excel. This specific method is the most straightforward and is particularly effective for a Microsoft Excel spreadsheet beginner and for lists that won’t require constant updates.

Step 1: Select the cell in the column where you want to input a drop-down list.

Step 2: Go to the Data tab and select the Data validation button or choose Data validation from the drop-down menu.

Step 3: Pick the Allow menu from the subsequent window that pops up and select List.

Step 4: Within the Source field, enter exactly what you want to be included in the drop-down list. Be sure to apply a comma after each list item.

Manually entering data into the ​​Source field for a drop-down list on Microsoft Excel.

Step 5: Select OK.

The cell you initially selected from step 1 will now have a functioning drop-down list consisting of all the information you entered in step 4.

Display a message when the drop-down list is selected

Once you’ve created your drop-down list, you can make it more accessible by adding an input message.

Step 1: Choose the cell where the drop-down list is located. Then open the Data validation pop-up window once more.

Step 2: Select the Input message tab. Enter a relevant title and the text you want to be displayed when clicking the drop-down list. The text added here is limited to 225 characters.

Displaying a message for a drop-down list on Microsoft Excel.

Step 3: Pick "OK" to apply the message.

Display an error alert

Similar to how you can insert a message that describes the purpose of the drop-down list, you can also display an error alert, which can emerge when text or data that’s not found in the list is entered.

Step 1: Select the cell where you created your drop-down. Reopen the Data validation window, select the Error alert tab, and tick the Show error alert after invalid data is entered box.

Enter a custom title and a message. Should the title or text fields be left empty, Excel will apply a default message.

Entering the relevant data for displaying an error alert for a drop-down list on Microsoft Excel.

Step 2: Choose a style from the ones provided, such as Stop (X), and select OK.

Alternatively, if you want a message to pop up that doesn’t prevent individuals from entering invalid data, select either Information or Warning from the Style menu. Both options sport their own design.

Displaying an error alert for a drop-down list on Microsoft Excel.

Protect your drop-down list

To prevent someone from viewing or tampering with the source of the drop-down list’s data, you can lock those cells.

Step 1: Go to the column where your data for the drop-down list is entered. Now select the cells you wish to lock in order to prevent changes from unauthorized sources.

After you’ve selected the area you want to lock, go to the Home tab.

Step 2: Within the Alignment section, select the Small arrow at the bottom right to open the Format cells window. Head to the Protection tab, mark the Locked check box, and select OK.

Locked option checked.

Step 3: Go to the Review tab and select Protect sheet or Protect workbook. Make any adjustments as per your requirements and pick OK.

The Protect Worksheet option for locking cells on Microsoft Excel.

A drop-down list in Excel gives you a convenient way to enter data. For more, take a look at these additional Excel tips and tricks.

Editors' Recommendations

How to convert a PDF to a Word document
Young Adult Laptop Lifestyle

PDFs are a great way to share documents because the format can be read by mostly anyone. It's generally used with digital manuals, sales receipts, manuscripts, and so on — documents that aren't meant for editing. Think of PDFs as digital printouts with pretty designs.

Read more
How to drop a pin in Google Maps
google maps

Pins are a convenient Google Maps feature that allows you to save a location. You can drop a pin to save an address or mark a location if it doesn’t have an address or if the address is incorrect. Your pins will help you navigate to these locations again, and you can also share them with your friends to indicate a meetup location.

Read more
Edge Copilot finally delivers on Microsoft’s Bing Chat promises
Here's Microsoft's example of how Bing chat will work in the future.

Microsoft is finally making the version of Bing Chat we heard about in February a reality. The latest version of Microsoft Edge (111.0.1661.41) includes the Bing Copoilot sidebar, which allows you to chat, generate AI content, and get insights into topics powered by AI.

This is the form of Bing Chat Microsoft originally pitched. Since its launch, the chat portion of Bing Chat has been available through a waitlist that, according to Microsoft, has amassed millions of sign-ups. However, Microsoft also talked about Bing Copilot, which would live in the Edge sidebar and open up the possibility of generating emails, blog posts, and more, as well as provide context for whatever web page you were on.

Read more