How To Create A Drop Down List in MS Excel

If you want to add a dropdown list in MS Excel or in Google sheets, and you are not able to do so, then this article will help you.

For office jobs, knowing everything about Microsoft Excel is probably the most important skill. Excel has enormous potential and here we are going to find out how we could create a drop-down list in Excel.

There could be various reasons for you to want a drop-down list depending on your situation. You could want it to create surveys, web forms, and polls.

As you can find the option to create a drop-down list in the Data Validation tool, it is actually pretty easy to make one in Excel. But although it is pretty easy to create, you still have to know-how.

Simple Method To Create A Drop-Down List

You can just enter the list manually in Excel and that would be the easiest way to create a drop-down list. But, you can only do this when you need a dropdown list in a single cell and it doesn't have to be updated frequently.

For instance, if you are sending someone an article where they have to fill out some fields, and there is a simple list of choices with one field, then this method could be found ideal.

To do this:

1. Select the cells where you want the list to be created.

2. From the menu select Data and select Data Validation on the ribbon.

3. A Data Validation window will be opened. Then just, select the dropdown list under Allow and select List.

4. You can now type the items that you want in your list manually. Make sure that each item is typed into the Source field while they are separated by commas.

5. Select OK. There will be a dropdown arrow next to the cell that you selected. As you select this arrow you will find that all of the items you typed have been included in this list.

You must remember that this could be the fastest method to create a drop-down list in Excel but this is also the hardest to maintain. If you want to modify the list you will have to go back into the validation settings and update the source field.

Therefore, you should use this method only for single cells, and for lists that you don't expect to change.

Create a Drop-Down List Using A Range

You can pull items for your list from a range of other cells if you would like a more flexible solution in Excel.

To do this:

1. First, in a column of cells, list all of the items that you want in your list. These could be entered in the current spreadsheet or some other spreadsheet.

2. Then select the cells as you repeat the process above and launch the Data Validation window. Set the Allow field to List. Now, select the up arrow icon to the right of this field instead of typing anything into the Source Field. You can select the field where you want to pull your list items from using this range selection option.

3. Then the Data Validation window will collapse and you can see the entire sheet. Then just drag the mouse pointer across the entire range of cells. After doing this select the down arrow icon on the right of the selection field. The Data Validation window will be expanded again.

4. The range that you selected will now appear in the Source Field and you can just select OK to accept these settings.

5. Now, you will see all the items included from the range you selected as you click on the dropdown arrow to the right of the cell you've configured as a dropdown list.

This could be particularly helpful if you want to format a lot of cells into a dropdown using the same list items.

Adding Items to Your List

You can also add new items to your list, in addition to changing items from your range to update the list. No idem could be added on either end of the range as the range selection is limited by the first and last cells that you have selected.

So you would have to insert the new entry somewhere in the middle of the range. And as you do this your list will be updated by Excel automatically.

To do this:

1. In your source range, right-click any cell and select Insert from the dropdown menu.

2. Select Shift cells down in the small selection window and select OK. All the cells in the range will be shifted down by one, inserting a blank cell where you have selected.

3. In the empty cells that you have created, type the new item that you want to add.

Now, you will see the new item you just added in the range as you click on the down arrow icon to the right of the dropdown list cell.

Dynamically Adding Items to Your List

You will need to use the OFFSET function for a more convenient way to add items to your dropdown list by just typing the new item at the end of your configured range.

Select Data from the menu and Data Validation from the ribbon with the dropdown list cell selected.

Then change the Source in the Data Validation window to this:

=OFFSET($E$1,0,0,COUNTA($E:$E),1)

In the formula, change $E$1 and $E:$E to use the letter of the column where you have entered your list of items. Then just select OK and confirm the new Data Validation configuration.

Here's how the formula works:

• In the OFFSET function, the 0 arguments tell it to not apply any offset to columns or rows.

• The OFFSET function figures the height of the range through the output of the COUNTA function.

• The number of cells that aren't empty in the column that contains your range is counted by the COUNTA function.

After this, as you add a new value to that column the height parameter of the OFFSET function will increase by one, and the OFFSET function returns the entire range, updated to include your new entry.

These are the different ways you can create drop-down lists in Excel and add values to your lists.

Conclusion

Using MS Excel to create a drop-down list is a helpful feature that can save time and decrease errors. You can make a drop-down list that enables you to pick choices from a predefined list with just a few clicks. This function comes in handy when putting information into a big spreadsheet or database. You can simply make a drop-down list in MS Excel and increase your efficiency by following the steps described in this article.

Frequently Asked Questions

1: Can I make a drop-down grid in Microsoft Excel?

A: Using the Data Validation tool in MS Excel, you can make a drop-down list.

2: Can I change the choices in a drop-down list after it's been created?

A: Using the Data Validation feature, you can quickly add or delete choices from a drop-down list.

3: Can I input data into numerous cells at once using a drop-down list?

A: You can use a drop-down list to input the same data into numerous cells at the same time, saving time and reducing mistakes.

4: In MS Excel, can I change the look of a drop-down list?

A: Yes, you can change the typeface, height, and color of the drop-down menu.