How to Create a Drop Down List in Excel

Making use of a dropdown list within Excel will greatly cut down the time needed to input data into an Excel spreadsheet. It’s actually quite simple to create a dropdown list using Excel.

There are many ways to achieve this, from basic to more advanced. You’ll discover the best way to achieve this in this post.

Create a Drop-Down List using Excel Simple Method

The most straightforward method to create dropdown lists using Excel is to enter it manually. This is ideal in situations where you need an option list within only one cell and you don’t have to keep it updated regularly.

For instance, if you’re sending them a document that requires people to fill out certain specific fields and the other of them contains a basic list of options the method you choose is best.

To make a drop-down list, you can use the following simple technique:

1. Choose the cell in which you would like to make your list.
2. Click on the data from the Menu and then click the Data Validation option in the Ribbon.
3. This will start the Data Validation window. Select the dropdown list that is under the Allow tab and then select The List.
4. This lets you type the items you’d like to add to your list by hand. Enter each item in the Source field, making sure that each item is separated with commas.
5. Choose Ok. The cell you’ve chosen is a dropdown arrow that’s adjacent to it. When you click that arrow, you’ll find everything you wrote out on this page.

Although this is the most efficient method of creating an option list using Excel, however, it’s also the most difficult to keep. Making changes to the list requires going back to the validation settings and then updating in the Source field.

If you make a lot of these kinds of dropdown lists using Excel you could end up doing an enormous amount of time altering the lists. This is the reason it’s crucial to use this method only for single cells as well as for lists you don’t anticipate changing.

Create a Drop-Down List using Excel by using a range

If you’re looking for to have a more flexible approach you can choose items from various other cells within Excel.

1. Begin by listing everything you would like to list in any column. It is possible to enter them in the spreadsheet you are currently using, or any other sheet.
2. Repeat the procedure below to choose the appropriate cell. Then open The Data Validation window. Make your Allow Field to the List. Instead of entering any information in the Source field, choose the up arrow to the right of the field. This is the option for range selection which lets you choose the field you wish to draw your list items from.
3. You’ll notice the Data Validation window collapse so you can view the entire sheet. Move the mouse pointer downwards to the entire range of cells that contain the items on your list you wish to include. After you’ve finished this, click on the down arrow icon just to the right-field for selection. This will open your Data Validation window once more.
4. You’ll notice that the range you’ve selected will appear within the Source field. Simply click “OK” and accept the options.
5. If you now select the dropdown arrow located to the left of the cell that you’ve created as a dropdown menu You’ll be able to see all the items that are included in the selection you made.

The great thing about this method is that it allows you to alter any item in this list simply by changing every cell within the range. Any changes you make will affect each dropdown list that you have constructed using the range you want to use as your source.

This technique is ideal when you wish to arrange a large number of cells to a dropdown with the same list of items. It is possible to have the contents of one range be the basis for the items on all the lists regardless of how many you have.

Add Items to Your List

Alongside changing items within your range to keep your lists updated and include new ones. It isn’t possible to add an item at the top of the range because your choices are limited to the initial and last cell you’ve chosen.

In the alternative, you’ll have to add the new number somewhere between the two ranges. Excel will update dynamically your range selection in settings for data validation to include the new range that has been increased by one cell.

1. Right-click any cell within the range you are aiming at, and choose AddFrom the drop-down menu.
2. In the small window for selection Select shift cells downwards and choose the OK button. This will cause every cell in the range one, and insert blank cells in the cell you’ve chosen.
3. Enter the new item you’d like to put in the cell you’ve just created.

Then, when you click then the down arrow just to the right on the list of dropdowns cell that you made, you’ll be able to see the item you’ve just added to the list.

This is a straightforward method of adding new options to the list of dropdowns, however, it will require some additional steps. It’s definitely not like adding a new item at the bottom of your list.

If you’d want to do this it’s just a matter of modifying the way you’ve configured verification of data for your range. You’ll learn how to accomplish this in the following section.

Dynamically adding items to your List

To make it easier for adding items to your dropdown list, simply by typing the item near the end of the range you’ve set You’ll need to utilize your OFFSET feature.

When the dropdown list cell is selected, choose the Data option in the dropdown menu, and then the Data Validation option on the Ribbon.

When you are in the Data Validation window, change the Source to this:

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

Modify the formulas $E$1 as well as the $E:$E to your formula to make use of the alphabetical column you’ve filled in the checklist of things. Click OK to confirm the change to the Data Validation configuration.

Here’s how it functions:

• The 0 arguments that are part of the OFFSET function instruct that it is not applying any offsets to rows or columns.
• Its output from the COUNTA function informs the OFFSET function of the maximum height of the range.
• The COUNTA function calculates the number of cells that are empty within the column that is containing your range.

When you add a new value to this column it’s the height of the OFFSET function is increased by one and it returns the OFFSET function will return the complete range, with the latest version including the new value.

To experience this in action simply add a brand new item to your list of products. Click the dropdown arrow to the right of the dropdown cell and you’ll see the new entry show up on the list of dropdowns.

Be aware that you may have to scroll the bar to the right if the list of items you want to add is lengthy.

Remove a Drop-Down List from Excel

In the event that you wish to eliminate the dropdown menu from one of the cells in which you’ve set it up Simply select the cell, and then open the Data Validation window once more.

Modify to the allow drop-down menu to any value and click Yes.

When you make the change You’ll notice that the cell will return to normal.

Excel to create Drop-Down Lists Excel

Making a dropdown list using Excel is simple, and you’ll have plenty of options to select from. The method you select is dependent on the number of cells you’re planning to allow by an item list, as well as the degree of flexibility you’d prefer your listing of the items. If you are planning to modify the list of items regularly make sure you use the dynamic method.