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.

Difficulty

Easy

Duration

5 minutes

What You Need

  • Microsoft Excel

  • PC

Create a drop-down list by using 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 include them in 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 in 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: Click the cell where you want the drop-down list to appear.

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

Step 4: Click the Data tab at the top and then select the Data Validation button.

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. Click that arrow.

The List option within the Allow menu on Microsoft Excel.

Step 6: Once the aforementioned arrow has been selected, you’ll be taken back to your spreadsheet’s main view. From here, simply head to the column where you entered the data. Now, select the range of cells in question 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, click the arrow button. ​​The Source bar will include the entire cell range you select in the above steps. Click 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 inputting data into Source field

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: Click the Data button from the top row of menus and then choose the Data Validation button.

Step 3: Click the Allow menu from the subsequent window that pops up. Now 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 word(s) or number.

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

Step 5: Click OK.

The cell you initially selected from Step 2 will now have a functioning drop-down list consisting of all the information you inputted in Step 5.

Displaying a message when the drop-down list cell is selected

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

Step 1: Select the cell where the drop-down list is located. Then click the Data Validation button.

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

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

Displaying 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 within the list is entered.

Step 1: Select the cell where you created your drop-down. Click the Error Alert tab within the Data Validation window and then 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, the title will automatically be set to Microsoft Excel, while the message will read: “The value you entered is not valid. A user has restricted values that can be entered into this cell.”

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 the Stop (X) sign. Click OK.

Alternatively, if you want a message to pop up that doesn’t prevent individuals from entering invalid data not included within the drop-down list, 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.

Protecting 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, click the Home tab. Within the Alignment section, click the small arrow at the bottom right. This will open the Format Cells window. Head to the Protection tab, click the Locked check box, and then click OK.

Locking the cells where the data entries are located for a drop-down list on Microsoft Excel.

Step 2: Click the Review tab. Select Protect Sheet or Protect Workbook. Make any adjustments as per your requirements and click OK.

The Protect Worksheet option for locking cells on Microsoft Excel.

Editors' Recommendations