Create Dropdown List in Excel Cell [Best 1]

With the drop-down list, you can help people work efficiently. He or she can pick items from the list easily. So if you want to create dropdown list in excel, then do follow the step-by-step process and create the drop-down list easily. In fact, you can add and remove items also from the drop-down list easily with one click.

Create Dropdown List in Excel

If you want to create your own dropdown list, then you need to follow the below-mentioned process.

• Initially, you must be having all your list items in the Excel table.
• If you do not have the list items in the table then you can convert your list items into a table for which you need to select any cell and press Ctrl+T.
• You can also create a new worksheet where you can type all the entries that you want in your drop-down list. Here we are taking the above list shown in the screenshot.

• Now to create a drop-down list, you have to select the cell on which you want to apply the drop-down list.
• In the ribbon, you will find the Data tab. In the data tab, you have to look for the Data validation icon.

• After clicking on the drop-down list, you will find a pop-up menu.
• In the pop-up menu, there are three options. They are settings, input message, an error alert.
• Choose settings and in allow drop down the list you will find list option.

• Now in the source box, you need to mention your list range. For example, here want to put the drop-down list in section.

• For people who want to leave cell empty, for those you need to select the Ignore blank check box.

• Check In-cell drop-down box.
• If you want to show the pop-up saying “Please select a city”, then you need to click on the input message tab and check the Show input message when the cell is selected. If you do not want message then uncheck the box.

• Select the error alert tab, if you want to show an error message whenever someone enters any wrong details.
• In the Error Alert section, you will find an error message box where you can mention the message that you want to show.

How to create Dynamic DropDown List in Excel?

The process is done using the OFFSET function. If you are using Offset function while creating a drop-down list then the drop-down will not update automatically whether you added items to the list. Every time you need to update the list after making the changes.

• Follow a similar process mentioned and take a similar data set and table.
• Click on data, the data tools, and the data validation.
• In data validation box, choose settings tab, and you will find the source field.
• In the source field, you need to enter the formula

=OFFSET($A$2,0,0,COUNTIF($A$2:$A$10,”<>”))

• Before doing this, you need to make sure that the in-cell option of drop-down is checked.
• Press OK icon.

How does the OFFSET formula work?

In the above process, we have used an OFFSET function to create a drop-down list. The list of items comes with the range of A2:A7. The syntax of OFFSET is

=OFFSET(ref, rows, cols, [height], [width])

In this formula, you need to take five arguments. Check the process above to use the OFFSET function.

Few points to remember

• People can see the dropdown list only when they click on the cell.
• The user can only choose one of the options listed in the dropdown. If he or she enters his or her data on its own then he or she will receive an error message.
• You can use this drop-down cell to other cells in your spreadsheet. You can create many drop-down as you like.
• You need to be careful while entering data as when you copy any cell over the cell containing the drop-down list then you will find that you have lost the drop-down list.

If you create dropdown list in excel and the list of entries is there in another worksheet then make sure you must prevent the list from users so that he or she cannot make changes or not able to see those changes.  If you want to add or remove items in the drop-down list then use add or remove items available in the drop-down list. If you want to remove or delete items then you can remove the items from the drop-down list.