If you are wondering how to add a drop-down in Excel and getting stuck while working on your datasheet. This article will help you adequately for the same.
Working on Microsoft Excel is very dynamic and because of its wide array of formatting options, your work can get disrupted in the middle.
Dancing Numbers helps small businesses, entrepreneurs, and CPAs to do smart transferring of data to and from QuickBooks Desktop. Utilize import, export, and delete services of Dancing Numbers software.
One of such prerequisites of working on an MS Excel document is the adding of the drop-down list. It is a very useful option to use and can benefit you in laying out the data adequately.
While representing huge datasets for your business, there are various formatting options that you would need. One of such is the addition of a drop-down list in MS Excel.
Steps to Add a Drop-Down List in Excel
This article will assist you with the easy steps involved in adding a drop-down list in Excel. Keep a keen eye out on the following section that lays out all the respective steps you would need to add a drop-down list in MS Excel.
Step 1: The first step for adding a drop-down list in Excel would need you to type out the items that you wish to appear in the respective drop-down list. Note that you would need to perform this action on a second sheet.
In case, you are working on a sheet that is shared with other users too, then you need to hide Sheet 2. For hiding Sheet 2, make a right-click on the tab of Sheet 2 and choose Hide.
Step 2: In the second step, go to the first sheet and choose a respective cell.
Step 3: Navigate your mouse on the Data Tab and click on the Data Tools Group.
Step 4: Select Data Validation. When you choose the Data Validation option, you would find the Data Validation dialog box appearing.
Step 5: Under the Allow Box, hit the option List.
Step 6: Press on the Source Box and choose the range.
Step 7: Select OK.
If you are looking forward to copying or pasting a drop-down list, choose the cell with the drop-down list, thereby pressing CTRL + C. simultaneously, choose another cell and do the paste action by pressing CTRL + V.
If you wish to do this alternatively, instead of using a range reference for the same, you can choose to type the items on the Source Box directly.
This would enable case sensitivity for the drop-down list, which means any user typing on it would find an error alert displaying for them.
Now, that you have successfully understood how to add a drop-down list in MS Excel, the next immediate thing you would need to know is to allow other entries.
Steps to Enable other Entries in the Drop-Down List
When you create a drop-down list, it should also have the option for enabling other entries in the same. If you do not enable other entries in the drop-down list created, then any other user typing a value would find an error alert. Thus, this action is very crucial.
To enable other entries in the drop-down list created by you, here are the steps that have to abide for it:
Step 1: For step 1, it would include typing a value that is not currently present on the list select Data Validation. When you choose the Data Validation option, you would find the Data Validation dialog box appearing.
Step 2: Next, scroll to Error Alert Tab and execute the uncheck action for the option that says Show error alert after invalid data is entered.
Step 3: Press OK.
This would enable other entries in the drop-down list created by you.
Another interesting thing to know while you are learning about how to add a drop-down list in MS Excel is the Dynamic Drop-Down list.
Steps to Automatically Update Your Drop-down List
Using a formula, you would be able to update your drop-down list quite easily and automatically. Whenever you would need to add an item to the end of the list, you can make use of the Dynamic Drop-Down list that will automatically update your drop-down list.
Here’s how you can optimally use the Dynamic Drop-Down list:
Step 1: Go to the first sheet and choose a respective cell.
Step 2: Select Data Validation. When you choose the Data Validation option, you would find the Data Validation dialog box appearing.
Step 3: Under the Allow Box, hit the option List.
Step 4: On the Source Box, make a click and enter the formula: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1).
Step 5: Press OK.
Step 6: Go to the second sheet and make an addition of a new item to the end of the list.
Looks to be much easier now? Adding a drop-down list in MS Excel is now a no-brainer, right! If this article helped you with the learning of how to add a drop-down list in MS Excel, go ahead and make that datasheet look professional and complete.
For more detailed tutorials on working on Microsoft Excel, save this space in your bookmarks!
Looking for a professional expert to get the right assistance for your problems? Here, we have a team of professional and experienced team members to fix your technical, functional, data transfer, installation, update, upgrade, or data migrations errors. We are here at Dancing Numbers available to assist you with all your queries. To fix these queries you can get in touch with us via a toll-free number
+1-800-596-0806 or chat with experts.
Frequently Asked Questions (Faqs)
Why would I need to make use of Drop-down Lists in MS Excel?
Drop-down lists in MS Excel are very important and can be immensely useful. This helps users to select an item directly from a list thereby negating out the purpose of typing their respective values on the sheet.
How can I Remove a Drop-Down List in My Sheet once added?
If you wish to remove a drop-down list in your sheet once that is added, here are the steps that will enable you to do so:
Choose the cell that has the drop-down list. Select Data Validation and choose the Data Validation option, you would find the Data Validation dialog box appearing. Press Clear All. Hit OK.
+1-800-596-0806