How to add a drop down list to an Excel cell

Drop-down menus can make data entry much easier. Here’s a look at how you can use Microsoft Excel’s data validation feature to create useful lists within your worksheets.

The drop-down list or drop-down list for Excel can make it easier for the average Microsoft Excel user to enter data into a worksheet or workbook. Using a dropdown in web forms, surveys, or polls can restrict input options to a specific cell, speeding up data entry and reducing data entry errors. In this Excel tip, we’ll show you a quick and easy way to create a dropdown list or dropdown list using the data validation feature in the spreadsheet app.

To create a dropdown list in Excel, you need two things: a list of values ​​(included in a range of cells) and a blank cell to use as a data entry cell.

Figure A Displays a simple drop-down list in an Excel sheet. To use the dropdown shown here, someone might place the cursor over an empty data entry cell (E4 in this example) and click a dropdown arrow to display the list of values ​​shown in the range of cells A1:A4. If a user tries to enter something that is not an item within this list of values, Excel rejects the entry.

You can continue with the steps in this tip by creating a new sheet with data similar to that shown in Figure A, or downloading the demo files .xlsx and . xls , or using your worksheet and data.

Figure A


To add the dropdown in our example to an Excel sheet, do the following:

  1. Create a data validation list in cells A1:A4. Similarly, you can enter items in a row, such as A1:D1.
  2. Select cell E4. (You can place the dropdown in any cell, or even multiple cells.)
  3. Choose Data Validation from the Data bar menu.
  4. Choose a menu from the Allow option drop-down list. (See, they are everywhere.)
  5. Click the Source Control box and drag the pointer to highlight cells A1:A4. Alternately, enter the reference (=$A$1:$A$4).
  6. Make sure that the dropdown menu option is selected in the cell. If you deselect this option, Excel will still force users to enter only list values ​​(A1:A4), but will not present a dropdown list.
  7. Click OK.

We see: How to Create a Dropdown List in Google Sheets (TechRepublic)

You can add the dropdown list to multiple Excel cells. Select a range of data entry cells (step 2) instead of a single Excel cell. It even works with non-contiguous Excel cells. Hold down the Shift key while clicking the appropriate Excel cells.

Some quick notes:

  • You can only see the dropdown box if you click on the Excel cell used to enter data.
  • Users can now choose only one of the options in the dropdown list. If they try to enter their own data, they will receive an error message.
  • You can copy and paste this drop down cell into any other Excel cells in your spreadsheet, and you can create as many different drop downs like this as you like.

See: 10 Time-Saving Excel Programs You Might Not Know (Free PDF) (TechRepublic)

Additional tip from Microsoft Excel

This Excel tip is laid out in this free PDF 30 Things You Should Never Do in Microsoft Office.

Rely on multiple links

Links between two Excel workbooks are common and useful. But multiple correlations where the values ​​in workbook 1 depend on the values ​​in workbook 2, which are in workbook 3, etc., are difficult to manage and unstable. Users forget to close files, and sometimes even move them. If you’re the only one working with these linked Excel workbooks, you might not have a problem, but if other users review and modify them, you’re asking for a problem. If you really need that much bonding, you can consider a new design.

Get more tips about Excel

Read 56 Excel tips every user should master and tutorials on how to add a condition to a dropdown list in Excel, how to add a color to a dropdown list in Excel, how to create an Excel dropdown list from another tab, how to quickly change a conditional formatting in Excel and how to integrate a function VLOOKUP() in Excel with a combo box for better search. Also check out this free PDF download: 13 handy Excel data entry shortcuts.