Using a dropdown list in Excel can greatly reduce the time it can take to enter data into a spreadsheet. Fortunately, it’s very easy to create a dropdown list in Excel.
There are a few methods to do this, from simple to advanced. You will learn all the ways to do it in this article.
Create a Dropdown List in Excel: Simple Method
The easiest way to create a dropdown list in Excel is by entering the list manually. This is great for situations where you only need a dropdown list in a single cell and don’t need to update it often.
For example, if you’re sending people a file where you want them to fill out some fields, and a field has a simple list of options, this method is ideal.
To create a dropdown list using the simple method:
1. Select the cell or cells where you want to create your list.
2. Select Data from the menu and select Data Validation on the ribbon.
3. This will open a Data Validation window. Select the dropdown list under Allow and select List.
4. This allows you to type the items you want on your list manually. Simply type each item in the Source field with each item separated by commas.
5. Select OK. You will now see that the cell you selected has a drop down arrow next to it. When you select this arrow, you’ll see all the items you’ve typed included in this list.
While this is the fastest method of creating a drop-down list in Excel, it’s also the most difficult to maintain. Modifying the list requires going back to the validation settings and updating the Source field.
If you create a lot of these types of drop-down lists in Excel, it could take a lot of work to modify them. That’s why it’s important to use this method only for individual cells and for lists that you don’t expect to change.
Create a Drop-Down List in Excel: Using a Range
If you want a more flexible solution, you can pull items for your list from a range of other cells in Excel .
To do this:
1. First, list all the items you want in your list in any column of cells. You can enter them in the current spreadsheet or in any other sheet.
2. Repeat the above process to select the cell and open the Data Validation window. Set the Allow field to List. This time, instead of typing something in the Font field, select the up arrow icon to the right of this field. This is the range selection option that allows you to select the field from which you want to extract your list items.
3. You will see the Data Validation window collapse so that you can see the entire sheet. Drag the mouse pointer down the entire range of cells that include all the list items you want to include. When you’re done doing this, select the down arrow icon to the right of the selection field. This will expand the Data Validation window again.
4. You will see that the range you selected now appears in the Source field. Simply select OK to accept these settings.
5. Now when you select the dropdown arrow to the right of the cell you set up as a dropdown list, you’ll see all the items included in the range you just selected.
The nice thing about this approach is that you can change any of the items in that list simply by modifying any cell in the range. Any changes you make there will update all the dropdown lists you created where you selected this range as the source.
This method is best when you want to format many cells in a dropdown using the same list items. You can make the content of a single range control the items in all of those lists, and it doesn’t matter how many there are.
Adding Items to Your List
In addition to changing items in your range to update your lists, you can also add new items. You won’t be able to add an item to the end of the range, because your range selection is limited by the first and last cells you’ve selected.
Instead, you’ll need to insert a new entry somewhere in the middle of the range. Excel will dynamically update your range selection in the data validation settings to include the new range that you incremented by one cell.
To do this:
1. Right-click any cell in its source range and select Insert from the dropdown menu.
2. In the small selection window, select Move Cells Down and select OK. This will move all cells in the range down by one, inserting a blank cell where you selected.
3. Type the new item you want to add into the empty cell you just created.
Now when you select the down arrow icon to the right of the dropdown list cell you created, you’ll see the new item you just added in the range.
This is an easy way to add new items to your dropdown list, but it requires a few extra steps.It’s certainly not as simple as adding a new element to the end of your range.
If you want to do that, you’ll just need to change the way you’ve set up data validation for your range. You can learn how to do this in the next section.
Adding Items Dynamically to Your List
For a more convenient way to add items to your dropdown list by simply typing the new item at the end of your configured range, you will need to use the OFFSET function.
With the drop-down list cell selected, select Data from the menu and Data Validation from the tape.
In the Data Validation window, change the Source to the following:
=OFFSET($E$1,0,0,COUNTA ($E:$ E),1)
Change $E$1 and $E:$E in the formula to use the column letter where you entered your list of items. Select OK to confirm this new Data Validation setting.
Here’s how the formula works:
- The 0 arguments to the OFFSET function tell it not to apply any offset to columns or rows.
- The output of the COUNTA function tells the OFFSET function the height of the range.
- The COUNTA function counts the number of cells that are not empty in the column that contains your range.
Now, whenever you add a new value to that column, the height parameter of the OFFSET function increases by one, and the OFFSET function returns the range complete, updated to include your new entry.
To see this in action, simply add a new entry to your article range. Select the dropdown arrow to the right of your dropdown cell and you’ll see your new entry appear in the dropdown list.
Remember that you may need to use the scrollbar to the right if your the list of items is long.
Removing a dropdown list in Excel
Finally, if you ever want to remove the dropdown option from any of the cells where you set this to, just select that cell and reopen the Data Validation window.
Change the Allow drop-down menu to Any Value and select OK.
Once you change these settings, you will see that the cell will return to normal.
Creating Dropdown Lists in Excel
Creating a Dropdown List in Excel It’s easy and you have many options to choose from. Which method you choose just depends on how many cells you plan to enable with a list of items and how flexible you would like the list of items to be. If you plan to change list items frequently, definitely use the dynamic approach.
.