Using a drop-down list in Excel can significantly reduce the time it takes to enter data into a spreadsheet. Luckily, it’s very easy to create a drop-down list in Excel.
There are a number of methods to do this, ranging from basic to advanced. This article will show you how to do it.
Reading: How to create drop down list in excel 2011
Create a dropdown list in Excel: Easy method
The easiest way to create a dropdown list in Excel is by typing the list manually. This is ideal for situations where you just need a drop-down list in a single cell and don’t need to update it frequently.
For example, if you’re sending people a file where you want them to have some fields fill in and a field has a simple picklist, 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 Data Validation from the ribbon.
3. This will open a data validation window. Select the drop-down list under Allow and then select List.
4. This allows you to manually enter the items you want into your list. Simply enter each item in the Source field, separating each item with commas.
5. Choose OK. Now you will see a drop-down arrow appears next to the selected cell. When you select that arrow, you’ll see all the items you’ve entered in that list.
While this is the quickest way to create a drop-down list in Excel, it’s also the most difficult to maintain. To change the list, you need to go back to the validation settings and update the Source field.
If you create many such drop-down lists in Excel, it can take a lot of work to change them. Because of this, it’s important to only use this method for single 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 drag items for your list from a range of other cells in Excel.
To do this:
See also: How to Build a WordPress Website (In 7 Easy Steps)
1. First, list all the items you want in your list in any column of cells. You can enter these in the current spreadsheet or in any other sheet.
2. Repeat the above process to select the cell and launch the data validation window. Set the Allow field to List. This time, instead of typing something in the Source field, select the up arrow icon to the right of that field. This is the range selection option that allows you to select the field you want to drag your list items from.
3. The data validation window minimizes so you can see the entire sheet. Drag the pointer over the entire range of cells that contains all the list items you want to include. When you’re done with that, select the down arrow icon to the right of the selection box. This will expand the data validation window again.
4. The selected range is now displayed in the Source field. Simply select OK to accept these settings.
5. Now when you select the drop-down arrow to the right of the cell you configured as a drop-down list, you’ll see all the items from the range you just selected.
The beauty of this approach is that that you can change any item in this list just by changing any cell in the range. Any change you make there will update any dropdown list you create that has that range selected as the source.
This method works best when you have many cells in a dropdown list want to format with the same list items. You can have the contents of a single pane control the items for all of these lists, and it doesn’t matter how many.
Adding items to your list
Also, around items change in your range, update your lists, you can also add new items. You cannot add an item to the end of the range because your range selection is limited by the first and last selected cell.
Instead, you must insert a New Entry somewhere in the middle of the range. Excel dynamically updates your range selection in the data validation settings to include the new range that you incremented.
To do this:
1. Right-click any cell in your source range and select Paste from the drop-down menu.
2. In the small selection window, select Move Cells Down and then OK. This moves all cells in the range down one cell and inserts a blank cell where you selected it.
3. In the blank cell you just created, type the new item you want to add.
Now if you select the down arrow to the right of the drop-down list cell you created, you’ll see the newer one Item you just stocked.
See also: How To Start an Online Store in 2023 (Step-by-Step Guide)
This is an easy way to add new items to your dropdown list, but it requires a few extra steps.It is certainly not that easy to simply add a new item to the end of your range.
If you want to do this, all you have to do is change how you have configured data validation for your area. You’ll learn how to do this in the next section.
Adding items to your list dynamically
For a more convenient way of adding items to your drop-down list, just by selecting the new item enter At the end of your configured range, you must use the OFFSET function.
With the drop-down list cell selected, select Data from the menu and Data Validation from the Ribbon.
In the data validation window, change the source as follows:
=OFFSET($E$1,0,0,COUNTA ($E:$E),1)
Change $E$1 and $E:$E in the formula to use the letter of the column in which you put your entered an item list. Choose OK to confirm this new data validation configuration.
This is how the formula works:
- The 0 arguments in the OFFSET function tell it that no offsets on columns or rows to be applied.
- The output of the COUNTA function tells the OFFSET function the height of the range.
- The COUNTA function counts the number of cells contained in the Column that contains are not empty your range.
Each time you add a new value to this column, the height parameter of the OFFSET function is incremented by one, and the OFFSET function returns the entire range updated to include your range New Listing.
To see this in action, simply add a new listing to your range of items. Select the drop-down arrow to the right of your drop-down cell and your new entry will appear in the drop-down list.
Note that you may need to use the scroll bar on the right-hand side of the list of items is long.
Removing a dropdown list in Excel
Finally, if you ever want to remove the dropdown option from one of the cells where you set it up, choose Just select this cell and open the data validation window again.
Change the Allow dropdown menu to Any Value and select OK .
Once you change this setting, you will see the cell return to normal.
Creating drop-down lists in Excel
Creating a drop-down list in Excel is easy and you have many options to choose from. The method you choose depends only on how many cells you want to activate with a list of items and how flexible you want the list of items to be. If you plan to change the list items frequently, be sure to use the dynamic approach.
See also: How to Make a Vector Image in Photoshop
.
