How to create a drop-down list in Excel

https://www.youtube.com/watch?v=E9AzF2sBb-MORE

Excel drop-down lists can make it easier for the average Microsoft Excel user to enter data. Using the drop-down menu limits the entry choices to a selected cell, increasing data entry speed and reducing data entry errors. In this article, I will show you a quick and easy way to create a drop-down list using Excel’s Data Validation feature.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use an older version. For your convenience, you can download the demonstration .xlsx and .xls files.

However, the Table object isn’t available in earlier versions of menus (.xls), so some of the content in this article won’t apply if you’re still using the .xls version. You can still use data ranges for the list, but the pop-up drop-down will not update when you update the list items. Microsoft Excel for the web supports data validation, and you can even add a data validation drop-down list in the web version. You can’t expand an Excel Table by tabing over a cell to insert a new row, but you can still add a row.

What is a drop-down list in Excel?

Picture A display a simple drop-down list in an Excel sheet. To use the drop-down menu, click the input cell (D2 in this case) and then click the drop-down arrow to display the list of values ​​in B3:B6. If the user tries to enter something other than an entry in that value list, Excel rejects the entry, preserving the validity of your data.

Picture A

To create a data validation drop-down list in Excel, you need two things: A list of values ​​and a blank cell to use as an input cell. The list is in B3:B6 and the input cell is D2.

How to add data validation drop-down menu in Excel

Now that you know how your users and data will benefit from the drop-down list, let’s add a list to D2. First, add the shortlist (Picture A) to B3:B6 and format it as an Excel Table object. Just click on any cell in the list and press Ctrl + T. In the resulting dialog, make sure that the My Table Has Headers option is selected and click OK. Technically, the list items don’t need to be formatted as a Table, but the Table makes the drop-down list dynamic.

To add the drop-down list in our example to an Excel cell, do the following:

1. Select D2 to follow the example, but you can place the drop-down menu anywhere.
2. Click the Data tab and then click Data Validation in the Data Tools group. Click the Settings tab, if necessary.
3. In the resulting Data Validation dialog box, click List from the Allow option drop-down menu.
4. Click the Source control and highlight B3:B6 – list items.
5. Click OK.

Figure BUT

Click the drop-down menu to see the list shown previously (Picture A).

You can add a drop-down list to multiple Excel cells. Select the range of input cells (Step 1) instead of an Excel cell. Doing so will populate multiple drop-down lists with the same list items. It even works for non-adjacent Excel cells. Hold down the Shift key while you click the appropriate Excel cells. The average user won’t benefit from this extra functionality, but it’s good to know that it can.

Earlier, you formatted the list items as Table objects. Now, discover why by entering a new list item into the Table:

1. Select B6 and press Tab to add a new cell to the Table.
2. Type “goat” and press Enter.

Excel added “goat” to the drop-down list (SIZE). You don’t have to do anything, other than enter a new list item. That’s the power of the Table object in Excel. I recommend using them whenever possible. To remove an item from the drop-down menu, remove the item from the Table.

SIZE

How to use drop-down menus in Excel Tables

We can expand the Table topic, because if you add a data validation drop-down to the first cell in the Table, Excel will expand the drop-down menu for each new record. Let’s take a look at this little miracle:

1. Create a Quick Table by selecting F2:G4. Then press Ctrl + T, click on My Tables titled option and click OK.
2. Select D4, the cell with the drop-down menu we created earlier, and press Ctrl + C.
3. Select G3, the first cell in that column, and press Ctrl + v to copy the drop-down menu to that cell.

The drop-down menu is available in the first cell in column G (Visualization).

Select an item from the drop-down menu and then press Tab to insert a new record into the Table. Switch to G4 and you will see the Copy Table drop down menu automatically (Figure E). If you’re using Excel for the Web, right-click the Table, choose Insert from the resulting submenu, and then select Table Rows above. The web version will duplicate the drop-down menu as the desktop version.

Visualization

Figure E

You may have created a data validation list using the interface, as you did earlier. However, I want you to know how easy it is to duplicate a drop-down menu.

Now, you may have noticed that you can only see the drop-down arrow if you click on the Excel cell. This is a bit of a deterrent. I recommend choosing a format, such as a fill color, to make it easy to find. The user must select an item; if they try to enter their own data, they will throw an error. There are options to display a specific error message, but we won’t cover that in this article.

Use multiple links to Microsoft Excel workbooks

A drop-down list can be populated by referencing the list items in another worksheet or even another workbook. This process takes a bit of work, so we don’t cover this option in this article.

Both workbooks must be open. If you try this, keep in mind that many links where the values ​​in workbook1 depend on the values ​​in workbook2, which link to workbook3, etc., are difficult to manage. Users forget to close the file and sometimes they even move the file. If you’re the only one working with the linked Excel files, you might not be in trouble, but if other users are reviewing and modifying them, you’re in trouble. If you really need a lot of such links, you can consider a new design.

Keep stable

In a future article, I’ll show you how to populate a drop-down list in one workbook by referencing items in another workbook. Linking between workbooks isn’t difficult, but it’s not intuitive.