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.

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).

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

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.

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

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

Next, select all cells of the table

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

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

You just created an Excel Table:

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).

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

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).

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.

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.