How to Make a Drop-Down List in Excel: 2 Best Methods in 2023

To make data-entry tasks flawless and fast, you must learn how to make a drop-down list in Excel.

Excel is a powerful data entry software. You can use it for personal and professional data-entry needs like creating office supplies lists, making household supplies lists, creating an inventory for a small business or home, and much more.

In any data-entry job, you must type in small data like names, addresses, quantities, locations, prices, etc. When someone audits this database, they may want to enter comments like audited, pending audit, error found, etc.

If you or your employee start doing this manually, achieving the objective will take days. Hence, you must automate the Excel sheet where you capture data. Excel drop-down list command is basic automation that you can use without learning Visual Basic coding for Excel. Read on to learn how to make a list in Excel with drop-down.

What Is a Drop-Down List in Microsoft Excel?

Excel drop-down list is a Data Validation tool of the Excel application. It requires you to select a range or list of data for the drop-down list. Then, simply apply the Data Validation command for Drop Down list in the target cell or cell ranges to get suggestions for data entry.

Drop-down lists eliminate guesswork from the data entry task. Also, you’ll make zero typos and other mistakes when you use the Excel drop-down list feature. It’s also the most widely used data entry automation tool on Microsft Excel.

When Can You Use Drop-Down Lists?

Here are some scenarios where you can apply this Data Validation command:

• You’re creating a database on Excel, and you need the data-entry operators to type in the data you need, no garbage.
• You want the data entry operators only to use categorized data when entering information in an Excel worksheet.
• You’re creating free online or offline forms on Excel, and you want the user to enter data only available in the form and nothing else.
• Your database includes repetitive names, phone numbers, addresses, etc. Also, depending on these data, you pay bills or invoices. Hence, accuracy is highly critical for the project. In such a case, you must use drop-down lists in Excel.
• You’re creating an Excel sheet for household supplies, and you want the family members to make entries only from an allowed list of items.
• Your data entry team loves to use keyboard shortcuts a lot, and for this, Excel drop-down lists offer unmatched convenience.
• You’re making an office supplies or purchases request database, and you only want employees to create purchase requests for pre-approved stationeries, IT hardware, or accessories.

How to Make a Drop-Down List in Excel: Using a Data Range

There are two ways to make a list in Excel with drop-down arrows. One uses a pre-configured data range, and another manually types the options. Find below the steps for the most automated drop-down menu in Excel:

• Create a new Worksheet in Excel and name it Database.
• Now, create a column header in cell A1 according to the type or any other name you like. For example, I’m giving the drop-down menu list a column header IT Hardware in this tutorial.
• Now, copy-paste data from somewhere else or type in manually to populate the cells below the column header with items you want in the drop-down menu.
• I’m typing in IT device purchase request options like Monitor, Speaker, RAM, CPU, PC Case, GPU, PSU, and so on.

So far, you’ve successfully created the database from which the Excel Data Validation command will show drop-down list options or choices. Now, let’s create the actual drop-down list by following the steps mentioned here:

• Select the cell where you want to make a drop-down list in Excel.
• Now, click the Data tab on the Excel ribbon menu.
• In the Data Tools command section, click the Data Validation drop-down arrow.
• A context menu will open with a few options. Choose Data Validation.
• Now, you should see the Data Validation command tool on the Worksheet where you want to add a drop-down list for Excel.
• It contains three tabs. These are Input Message, Settings, and Error Alert.
• Click Settings.
• Under the Validation criteria option, click the drop-down list below the Allow menu.
• Choose List from the drop-down.
• By default, the In-cell dropdown and Ignore blank checkmark should be checked. If not, check them.
• Click the up arrow in the Source field. The Data Validation dialog box will now minimize to a thin bar with a blank field.
• Go to the Database Worksheet and select the cell range in the Excel drop-down list.
• Choose some blank cells below the last item. It’ll help you expand the list without editing the Source again.
• Excel will auto-populate the source data for the drop-down list. You just need to click the down arrow on the thin Data Validation bar.
• You should now see the expanded Data Validation dialog box again. But, this time, you’ll see a source data range in the Source field.
• Just click OK, and you’re all set.

Congratulations! You’ve created your first drop-down list in Excel. Now, Simply copy this anywhere else where you need to show the same items when an employee or any other user fills up data in a data-entry sheet.

You may want to lock the Database worksheet so that no one can make any changes to the list of options in the drop-down lists in the data-entry worksheet. Follow these quick steps to secure list items:

• Choose the cell ranges you need to lock.
• Right-click and then select Format Cells.
• Go to the Protection tab and checkmark the Locked option.
• Click OK to apply the Format Cells changes.
• Now, go to the Review tab on the Excel ribbon.
• Select the Protect Sheet icon.
• On the Protect Sheet dialog box, enter a password in the blank field.
• Reenter the password, and Excel will lock the Worksheet for good.

How to Make a Drop-Down List in Excel: Using List Items

If you want a short drop-down menu, like Yes/No, Audited/Not Audited, Assigned/Open, Complete/Incomplete, etc., you can use the manual list instead of the data range method. It saves time. Here’s how you can create a drop-down list in Excel using list items:

• Click a cell to populate a drop-down menu.
• Select Data Validation from the Data tab of the ribbon menu.
• Under the Allow menu, click List.
• Now, simply type the list of items in the Source field.
• Make sure you separate each item with a comma.
• Click OK to apply the drop-down list.

This is the easiest and fastest method to create a simple drop-down list in Excel. However, the first method is more reliable when you’ll be using drop-down lists abundantly.

How to Make a Drop-Down List in Excel: Showing Input Message

An empty drop-down list may not say much to the data-entry operators. You need to add specific instructions, so they don’t make any mistakes. Here’s how you can add an instruction message to drop-down lists in Excel:

• Click any cell that contains an active Data Validation command.
• Now, go to the Data tab > Data Validation > click Data Validation.
• Inside the Data Validation dialog box, click the Input Message tab.
• Type a small text for the Title and then a detailed message in the Input Message field.
• Click OK to apply the changes.

Now, whenever a user selects a cell with an active drop-down list Data Validation command, they’ll see a message on how to fill up the cell using items from the drop-down list.

How to Make a Drop-Down List in Excel: Display an Error Alert

If someone makes an error while using the drop-down lists on your Excel data-entry sheet, you can also show an error message. This will ensure that the user takes action to resolve the error and not skips the cell. Here are the steps to activate Display an Error Alert:

• Follow the steps mentioned earlier until you get the Data Validation dialog box.
• Now, select the Error Alert tab.
• Choose an Error Style from three options: Stop, Warning, and Information.
• Then, type in a short text for the Title of the Error Alert.
• Now, enter a detailed message in the Error message field.

Great work! With activated Display an Error Alert, the Worksheet will show an error dialog box if someone types anything other than the items mentioned in the drop-down list in Excel.

Drop-Down List in Excel: Final Words

Now you know how to make a list in Excel with drop-down in two different methods. You can try out ways to make Excel drop-down lists and create data entry forms to help your employees.

You can also create an intuitive home supplies list for groceries, hardware, clothing, utilities, and more using drop-down lists in Excel.

Don’t forget to comment below if you know another cool way to make a drop-down list in Excel.

Next up, learn to unhide rows or columns in Excel and some Excel calendar templates for any year.