How to Add Drop Down List in Excel – Data Validation Lists

Posted April 3, 2019 by Sheraz Ali in Microsoft Excel

A drop-down list in Excel can limit and regulate the number of entries in a single cell. You can use it to better organize your spreadsheet by containing the length of its data.

In my opinion, learning how to add drop down list in Excel is essential if you want to save valuable time and enhance productivity. This is why every Job position for an Excel expert requires you to know this little gimmick.

In this tutorial, I will show you how to add a drop-down menu to your cells in two different ways. In one, you will manually type the names of the items. This will be useful for small data sets or a limited number of items or values. In another, you will assign a drop list to an entire range of cells. This will come in handy when you’re dealing with a massive amount of data or items.

How to Add Drop Down List in Excel to a Limited Number of Cells (Two or Three)

1. Select the cell you want the drop-down menu added to. 
2. On top of your Excel window, select the Data tab.
3. Locate and tap Data Validation
4.In the Settings tab, select “List” from the Allow drop-down menu.
5. Tap the Arrow button beneath Source. 
6. Type the names of the values or items you want the drop-list added to. For instance, in this case, I’m adding the No and Yes option to the Yes value of my spreadsheet. Now, press Enter
7. And this is the result I’m getting.

How to Add Drop-Down List in Excel to an Entire Range of Cells

1. Select the entire range of cells you want the drop-down list added to.
2. In the Data tab, go to Data Validation and select List from the Allow drop-down menu.
3. Tap the data button underneath the Source option.
4. Select the entire range you want the drop-down list added to and hit Enter.
5. Tap OK.
6. This is the result. All the preselected cells will now appear as drop-down options.

You can also couple your knowledge of data validation lists with the understanding of custom functions in Microsoft Excel to gain advanced insights into how these two functions complement each other.

To get further guidance on how to add drop down list in Excel, check out these related tech-recipes.

How to Split One Cell Row into Multiple Rows in Excel

How to Add Powerful effects to Images in Excel

You can also leave your queries in the comments section below or glance through the FAQ if you still have any confusions.

Frequently Asked Questions

Is this tutorial applicable to the newer versions of Excel: Excel 2019, 2018 and 2016?

Yes, although, I’ve added screenshots from the 2010 version to explain the entire process to you, you can rest assured it works exactly the same way for the newer versions of Excel.

How can I add a No and Yes box in Excel?

You can add Yes and No to your drop down menu the same way you will add any other items or values. There’s no difference, whatsoever.

How can I create a drop-down list in Google Excel Sheets?

Please refer to the following tech-recipe for guidance: how to create a drop-down list in Google Sheets.

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply