A drop-down list allows its users to choose a variety of options right from the preset list given there. You can fill out a form as well with a drop-down list. Usually, on websites or apps, you may have seen a number of lists that are instinctive. You can save plenty of time with such lists while entering data in the spreadsheet. Well, it is not tricky to create drop-down lists in Excel. You have to follow some easy steps in case you need to make the list.

We have some different tricks that simply show effectiveness. Actually, a drop-down list is known to be a little window that lets you peep into other content. Furthermore, you can even control the data you enter into the spreadsheet.

Simple Steps for Creating Drop Down Lists in Excel

Here, you will get to know the easiest steps for creating drop-down lists in Excel.

• Using Data from Cells
• Entering Data Manually
• Using the OFFSET Formula

Using Data from Cells

In the list given below, you need to follow the steps for further proceedings:

Let’s focus on the steps that could help in creating drop-down lists in Excel:

1. Choose a cell in which you want to make a drop-down list.
2. Open the Data option and then Data tools and Data Validation options.

3. Choose List as the Validation criteria given in the Data Validation dialogue box right from the Settings tab. Once you choose List, you will see the source field.

4. Add =$A$2:$A$6 in the given source field or you can just click in the Source field. Now, choose the cells from the mouse and click OK. In cell 2, you will see a drop-down list is created.

5. You need to ensure that the In-cell dropdown option is checked. If it is not, you will not see the drop-down and you can add the values manually in the list.

Remember that if you need drop-down lists in more than one cell with just one click, you have to select all the cells and follow the same procedural steps given above. Try to ensure that the cell references are not relative but absolute.

By Entering Data Manually

As you can see in the above example, cell references are there in the Source field. Furthermore, you can even add the values manually in the Source field directly. For instance, suppose you need two options to appear, Yes and No, in the given drop-down list. Below is the entire step-by-step solution to add in the data validation source field.

1. Choose a cell in which you need to make a drop-down list.
2. Open the Data option subsequent to Data Tools and then Data Validation.

3. Right from the Data Validation dialogue box, choose the List as the Validation criteria given in the Settings tab.

4. You will see the Source field once you select the List.

5. Enter Yes, No, in the Source field and ensure that the In-cell dropdown option is not unchecked.

6. Hit the OK button.

In the selected cells, you will see a drop-down list and each value entered in the source field will be separated using a comma. 

Also, note that if you really want to create drop-down lists in Excel then you have to choose all the cells in which you want to create the list and then follow the above-mentioned steps.  

Using Excel Formulas

Would you like to use Excel formulas rather than choosing cells and entering data manually? 

Sure, you will adore this trick because it appears handy as compared to other techniques explained earlier. Using Excel formulas can help you in creating drop-down lists in Excel. 

Using any formula that can return a list of values, you can create a drop-down list simply. For instance, if you have a data value as shown below: 

Follow the steps given below in order to create a drop-down with the help of the OFFSET function: 

1. Choose a cell in which you need to make a drop-down list. 
2. Click on Data > Data Tools > Data Validation options

3. From the Settings tab given in the Data Validation dialogue box, you need to choose List as the Validation Criteria. 

4. You will see the source field once you select the List. 

5. Right from the Source field, add the following formula: =OFFSET($A$2,0,0,5)

6. Ensure that the In-cell dropdown is not unchecked. 
7. Hit OK. 

Remember that if you need a drop-down list in more than one cell with just one click, choose all the cells in which you need to make a list and then follow the steps given above. Also, ensure that the cell references are not relative but absolute. 

Things to Consider While Working with Excel Drop Down Lists

Below are some things you need to consider when you are surrounded by the drop-down list that works in Excel. While copying a cell when there is no drop-down list, and another cell has a drop-down list, the drop-down list will go.

And one major thing you will notice is that Excel does not notify the user about this thing. So, you have to be careful while working with these things.