Data entry is a tedious but important task—no matter what platform you use. Whenever you manually enter data, you leave room for errors. And these errors can lead to inaccurate information, inconsistent entries, and a host of other problems. In Excel, one way to decrease the number of data entry errors is to use drop-down menus.

What is a Drop-Down Menu?

A drop-down menu is a list of pre-defined options that appears when a text string or button is clicked. In Excel, these lists can be embedded within a cell or range of cells.

How Do you Create a Drop-Down Menu?

Embedding a drop-down menu in a cell is a simple task.

Step 1: First open a new Excel Workbook and then select the cell (or range of cells) where you want the list to appear. For this example, select cell A1.

Image of an Excel Workbook where cell A1 is selected.

Step 2: Next, toggle over to the Data tab on the Excel Ribbon and then click the Data Validation button (icon with a check and a cancel icon).

Image of the Data Tab on the Excel Ribbon. The Data Validation button is selected.

Step 3: When the Data Validation dialog box appears, navigate to the Validation criteria section, and, under Allow, select List.

Image of the Data Validation dialog box. The List option under the Allow is selected.

Step 4: A Source text box will become active. Enter the (text or numeric) values you want in your drop-down list separated by commas, and then click OK. For this example, I entered:

Yes, No, Not Applicable

Now when you select the cell, a small arrow should appear on the far-right corner. And when you click on the arrow, you can select any of the options available.

Image of an Excel Workbook showing the options available under the drop-down menu: Yes, No, and Not Applicable.

And when someone tries to enter a value not included in the list, a BIG alert message box will pop up informing you that the value is not valid.

But what if you need to update an existing list?

Using Excel's Table Feature + Named Ranges to Update Drop-Down Menus

Manually creating lists can be a tiresome task. Who wants to manually enter 30 or 40 different items in a list? Lucky for us, we can use Excel's table feature to create lists we intend to embed in a drop-down menu.

In Excel, you can turn a range of cells into a table. It's super easy. Create a new Worksheet. Starting from cell A1 enter the following:

Gender Identity
Woman
Man
Non-Binary

Image of an Excel Workbook where the following text is entered into cells A1 through A4: Gender Identity, Woman, Man, and Non-Binary.

(Note: The first cell (A1) contains the Table's header.)

Next, select all cells of the table

Image of an Excel Workbook where cells A1 through A4 are selected.

navigate over to the Insert tab on the Excel Ribbon and then click the Table icon.

Image of an Excel Workbook where cells A1 through A4 are selected, the Insert tab on the Excel Ribbon is highlighted, and the Table icon is selected.

The Create Table dialog box will appear on your screen. Be sure to check the option My table has headers and then click OK.

Image of an Excel Workbook where the Create Table dialog box is full view with the My table has headers box is checked.

You just created an Excel Table:

Image of an Excel Workbook with a single column Table that has the header Gender Identity and three rows of data: Woman, Man, and Non-Binary.

Now, you would think you could use the table as a reference to create the list for your drop-down menu, but not so fast. Excel does not allow you to directly use a table as a source for data validation lists. This is where named ranges come in handy.

Named ranges are an Excel feature that allows you to reference individual cells or groups of cells within a Workbook using a descriptive name. Here, we are going to create a named range for our newly created table.

Select cells A1 to A4. Toggle over to the Formulas tab on the Excel Ribbon and click the Name Manager icon (looks like a name tag).

Image of an Excel Workbook the Formulas tab on the Excel Ribbon is highlighted and the Name Manager icon is selected.

A Name Manager dialog box should appear on your screen. You should see a table called Table1. We want to create a named range that references our table.

To do this, click New (far left-hand corner of the dialog box). (A New Name dialog box will appear on your screen.) In the Name text box, enter a name that describes the data in your table. Since our table provides a partial list of options for gender identity, enter:

gender_identity

In the Comment box, enter:

A partial list of options for gender identity

Next, ensure that the Scope is set to Workbook. Finally, in the Refers to text box, delete everything after the table number. So, for our example, the text box should read:

Table1

Image of Excel's New Name dialog box. The Name text box reads: gender_identity; the Scope is set to Workbook; the Comment reads: A partial list of options for gender identity; and the Refers to text box reads =Table1.

Click OK and then Close the Name Manager.

Now, to use our named range.

Select cell C1, toggle over to the Data tab on the Excel Ribbon, and then click the Data Validation button (icon with a check and a cancel icon).

Image of an Excel Workbook where cell C1 is selected, the Data tab on the Excel Ribbon is highlighted, and the Data Validation button is selected.

When the Data Validation dialog box appears, navigate to the Validation criteria section, and, under Allow, select List.

In the Source text box, enter:

=gender_identity

Click OK to close the Data Validation dialog box. The cell should now have a drop-down arrow in the far-right corner. Click on the arrow and you should see the three options from your list.

Image of an Excel Workbook, where the drop-down menu in cell C1 is in full view. The drop-down menu options are Woman, Man, and Non-Binary.

The best part? You can add items to your list by inserting a new table row.

When you use a drop-down menu and limit what people can enter in a cell, data entry becomes more streamlined and efficient. So, the next time you find yourself facing the daunting task of data entry, decrease the number of errors that may be caused by manual entry by using drop-down menus.

Do you use drop-down menus? Let me know in the comments.

Decrease Data Entry Errors with Drop Down Menus

List Files in a Directory in R

Save Time with ChaRt Templates