How to Create a Drop-Down List in Microsoft Excel
Microsoft Excel users can use the drop-down lists on their worksheets in order to increase productivity. People can select an item from a list you generate using drop-down lists.
How to Create a Drop-Down List in Microsoft Excel
The drop-down feature of Microsoft Excel allows you to build a list of options to choose from and put a drop-down selector into any blank cell on your spreadsheet.
-
Step 1: Adding of Entries
Type the entries you wish to show up in your drop-down list in a new worksheet. Your item lists should ideally be in an Excel table. If not, you can easily create a table from your list by choosing any cell in the range and executing the Ctrl+T keyboard shortcut.
-
Step 2: Access the Data Validation Settings Tab
In the worksheet, select the cell where you want to put the drop-down list. Click on Data Validation from Ribbon’s Data tab. A Data Validation pop-up menu will appear. In the Allow box under the Settings tab, select List. Next, click in the Source box, then choose your list range. As for the given example, the sheet is called Cities, in the range of A2 to A9. Remember to leave out the row header as it is not part of the selection options. After that, check the “Ignore blank” box if it’s permissible for users to leave the cell unfilled. Also, put a check in the “In-cell dropdown” box.
-
Step 3: Go to the Data Validation Input Message Tab
The next step is to go to the Data Validation Input Message tab. Check the box labeled “Show input message when cell is selected” and enter a title and message in the corresponding fields if you want a message to appear when the cell is clicked. You can write a message of up to 225 characters. Clear the check box if you don’t want a message to appear.
-
Step 4: Select the Data Validation Error Alert Tab
Now, select the Data Validation Error Alert tab. If you want a message to appear when someone inputs something that isn’t on your list, check the “Show error alert after invalid data is entered” box, select an option from the Style box, and type a title and message. Remove the check on the box if you don’t want a message to appear. After that, click OK, and your drop-down list is created.
-
Step 5: Work With the Drop-Down List
Once you have created your drop-down list, make sure that it functions as you want it to. For example, you might want to change the column width and row height to display all of your entries. You can also consider hiding and protecting your worksheet if the drop-down list’s entry list is on another worksheet and you want to prevent users from viewing it or making changes. Additionally, you can change the options in your drop-down list as well as delete a drop-down list.
FAQs
How do I remove a drop-down list in Microsoft Excel?
Choose the cell that contains the drop-down list (you can use Ctrl+Left click to pick several drop-down list-containing cells that you want to delete), click Data and then Data Validation, then click Clear All under the Settings tab, and select OK.
How do I edit an Excel table-based drop-down list?
If you set up your list source as an Excel table, then you just need to add or remove items from the list, and Excel will automatically update any associated drop-downs for you (a new item can be added by simply typing it at the end of the list, and to remove an item, simply press Delete).
What’s the purpose of a drop-down list in Microsoft Excel?
The drop-down list in Microsoft Excel is a fantastic tool included in the data validation group, and its primary purpose is to restrict the amount of data that can be entered (users can only select items from one fixed list in a given cell).
More in Microsoft Office
