How to create drop down list in Excel?

Excel is the world’s most popular software program that helps every user to create a data sheet. And it also allows everyone to organize, format and calculate data with the help of formulas by using a spreadsheet system. Microsoft Excel is created by Microsoft. You can do many things on excel like creating company data, balance sheets, To-do lists and much more. It’s fully beginner-friendly and anyone who is new can understand its function in just 4 to 5 days only, not advanced only basic. But on this topic we will discuss excel drop down list means how to create drop down list in excel in just a few seconds.

What is excel drop down list?

A drop-down list is a list of choices that appears when you click on the menu and it shows you many options in the list. On excel drop down list is a data validation function that allows every user to choose or select an option from a list. It’s the best or excellent way to give a user so many choices with a predefined list. You can use this to create your excel list interactive or while you want to create a form where users choose their name and fill the form.

How to create drop down list in excel?

To create a drop down list in excel we have 3 methods. All methods are quick and you can create your drop down list within a second. Let’s check out what’s the steps:

• By using Data from Cells
• By entering your data manually
• By Using the OFFSET formula

By using data from cells

In this example we will change this list into drop down list let’s see how can I do this:

• Create the list you want to show in your drop-down menu.

• Select the area or cell where you want to create a drop down list.
• Then click on Data and then Data Validation.

• A data validation pop up appears, click on Allow and then select List.

• After that, a source field option is left.
• Click on it (Source field) and then select all list items with the help of your mouse and then click ok.

Point to remember: Make sure that you checked the In-cell dropdown list by the way it’s checked by default by if not then check it otherwise your excel drop down is not showing.

By entering your data manually

On the upper point, we discussed how you can select your drop-down data from your excel sheet. Basically, cell reference works as a source field. But you can also add your excel drop down data manually by entering your data in the source field.

• Select the area or cell where you want to create a drop down list.
• Then click on the Data and then the Data Validation option.
• A data validation pop up appears, click on Allow and then select List.
• Again source field is left but now you don’t have to select your drop-down list items from the other excel sheet. Because we have to learn how to add data manually.
• Click on the source field and then write Agree or Disagree by adding a comma to separate text (If you don’t add a comma on each text then they will align on the same line that’s why we add a comma to separate text).
• And then click on OK.

This will also create a drop down list but here we add our data manually. Don’t forget to add commas to every word to separate your text to the next line.

By Using the OFFSET Excel formula

As we already know how to create drop down list in excel through data cells or enter the excel data manually. But now the turn is by using the OFFSET excel formula to create a drop down menu list on excel. Any formula on excel returning a list value can be used to create a drop down list. Let’s know how to create drop down list in excel by using the OFFSET function formula.

• Select the area or cell where you want to create a drop down list.
• Then click on the Data and then the Data Validation option.
• A data validation pop up appears, click on Allow and then select List.
• Now we don’t need to select drop-down list data from the other excel sheet or also we don’t need to enter the drop list data manually. But now we will create our drop down list by using the OFFSET function formula.
• Click on the source field and enter =OFFSET( and then click on the first text on your drop-down list and then write 0,0, and the number of your list. In my situation, I have 12 items in my drop-down list so I have to enter 12 after the 0,0. And my final formula is =OFFSET($A$2,0,0,12).

=OFFSET( – This is the starting formula.

$A$2 – This comes when you click on the first word of your drop-down list.

12 – In my case, I have 12 items that’s why I add 12 (If I have 50 and more items on my drop-down list then I will write 50 after 0,0).

The Syntax of OFFSET function is: =OFFSET(reference, rows, cols, [Height],[Width])

• And then click on OK.

This will also create a drop down list but here we use the OFFSET formula to create our drop down list. When we create our drop-down list by using the OFFSET formula we don’t need to put a comma after each word.

How to copy-paste drop down list on excel?

Now you well know how you can create drop down list by using data from cells, entering data manually, or by using an excel formula. But now we discussed how you can copy or paste the drop down list on excel. Now you think it’s very easy just CTRL + C to copy and CTRL + V to paste but if you do this then it will also copy your data validation. But if you want to copy the drop-down only not the formatting then here are the steps on how you can do that.

• First copy the cell that has already a dropdown.
• Select the cell where you want to copy your dropdown.
• After that click on the paste option on the top right corner. If the paste option is not showing make sure you are at the Home option.
• And then click on paste special.
• A paste special box appears and checks the validation box in paste options.
• After that click on OK.

Choosing this method to paste your drop-down menu only not the formatting of the copied cell.

How to find all drop down lists in excel?

When we create many drops down lists on our excel sheet then it’s hard to find where it is. So to easily find where we created our drop-down list there is a very easy method. Let’s see how we can do this.

• Open your excel sheet and make sure you are in the home section.
• Click on the find & select option on the top right corner of your screen.
• And then click on the Data Validation.

Data Validation has two options: first is, All and the other one is the same.

All have selected all the cells that have a data validation rule applied.

Same select only who have the same data validation rule.

• And then click OK.
• Now this will show you all the cells that have data validation rules applied including a drop-down list.

Final Words

Excel sheets are used everywhere. All companies use excel sheets to make a report of their work as well as their budget report. Using this drop-down trick on our sheet is a good or excellent way to make a good impression on our client or any user. Using a drop down list makes our excel sheet interactive. I hope after reading this you know how to create drop down list in excel.

FAQ