In Excel, how do you insert a drop-down menu?

In Excel, how do you insert a drop-down menu?

When it comes to data entry, drop down lists are a huge help. Microsoft Excel's built-in data validation feature can be used to create useful lists in your worksheets.

With an Excel drop down list or drop down menu, a typical Microsoft Excel user can enter data more quickly and easily. Using a drop-down menu in web forms, surveys, or polls can reduce data entry errors and speed up data entry by limiting the selection options for a selected cell. To create a dropdown list or drop down menu in Excel, we'll show you how to use the spreadsheet application's Data Validation feature to do so.

Excel requires two things to create a drop-down list: In addition to the data entry cell, there is a list of values (contained within a cell range).

An Excel drop-down list is shown in Figure A. A user would place their cursor over a blank data-entry cell (E4 in this example) and click a drop-down arrow to see the values shown in the cell range A1:A4. Excel rejects any input that does not fall into one of the predefined values.

By creating a new sheet of data similar to that shown in Figure A, downloading our demonstration.xlsx files or using your own data, you can follow along with the steps in this tip

Excel drop down list

Doing so is as simple as typing the following into an Excel workbook:

  • Cells A1-A4 should be used to create a data validation list. The items can also be entered in a single row, for example, A1:D1.
  • Cell E4 is the one you want to work with. (The drop-down list can be placed in virtually any cell, or even in multiple cells at once.)
  • The Data ribbon menu has a Data Validation option that you can access.
  • From the Allow drop-down menu, select List. In fact, they're all over the place.
  • Cells A1-A4 should be highlighted by dragging the cursor over the Source control box. The reference (=$A$1:$A$4) can also be entered.
  • The In-cell dropdown option should be selected, so make sure it is. Excel still forces users to enter only list values (A1:A4) if this option is unchecked, but no drop-down menu is displayed.
  • Select OK.

The drop-down list can be added to multiple cells in Excel. Instead of selecting a single Excel cell, choose a range of cells (step 2). Non-contiguous Excel cells can also be used. Excel cells can be selected by holding down the Shift key while clicking on them.

A few tidbits of advice:

  • It's only visible if you click on the Excel cell where you're entering data.
  • In the drop-down menu, users can now only select one of the options. They'll get an error message if they try to enter their own data.
  • There are a number of ways to use this drop down, including copying and pasting it into any other Excel cells in your spreadsheet.

 

Enjoyed this article? Stay informed by joining our newsletter!

Comments

You must be logged in to post a comment.

About Author
Recent Articles