You might be the data collector in your organization or in your school or college. You might have an excel file and this file might need to be filled in by so many different people. The way people enter data depends on them and could contain many errors and even data that might not be in the required range. At the end of the day, you might end up having the worst headache correcting and replacing all the data in the world.
So, is there a way how you can restrict the data that can be entered into a particular cell, like enforcing a dropdown list? Once you enforce a dropdown menu for a particular cell, data can be entered into this cell only by staying within the dropdown boundaries.
In this article, we explain through some simple steps, how you can easily add a dropdown menu to your excel cells. Hope you find the article useful.
How to Add a Dropdown Menu in Excel
Step 1: Right next to the Windows Start menu icon, click on the Search icon on the taskbar.
Step 2: In the search bar window, search for Microsoft excel and click on Excel from the Best match section.
Step 3: Once the excel launches, we will begin by creating the dropdown menu contents. We will create it on Sheet2 of the excel, in this example. For that, click on Sheet2 at the bottom part of the excel window, as shown in the screenshot below.
Step 4: Now in Sheet2, type in your dropdown menu contents anywhere. I have used column A to type my contents in.
Step 5: Once you have your dropdown menu source ready, let’s consume that source content in our main excel sheet.
For that click on Sheet1 at the bottom part of the window and then select all the cells where you want the dropdown menu to be applied. Or if you want the dropdown menu to be applied to a single cell, then just click on that single cell.
In the example below, I have a column named Author and I have selected some cells from A2 to A13, and I want the dropdown menu to be applied to each of these cells.
Step 6: Once again, make sure your cells are selected. You can select even a single cell, that’s perfectly fine.
As next, click on the DATA tab at the top panel. Now, click on the Data Validation dropdown and then click on the Data Validation option.
Step 7: You will now have the Data Validation window open before you. Click on the Settings tab first. Now, from the dropdown menu associated with the option Allow, click on the option named List. Finally, click on the Source icon to select the source list.
If you have any doubts, please refer to the screenshot below.
Step 8: You will be able to see the Data Validation window (marked in green), nothing to be done in this window now.
Since the dropdown menu source is in Sheet2, click on Sheet2 first.
Step 9: Now simply drag and select the cells that you prepared earlier. Once you select the source cells, the Data Validation window will be automatically populated. Finally, click on the source icon once again.
Step 10: Once you are back at the Data Validation window, you will be able to see that your source list is populated. Simply hit the OK button.
Step 11: You will now be back at your main sheet, which is Sheet1. Viola, you can now see that all the cells that you initially selected now have the dropdown menu applied to them.
Only the values in the dropdown source menu can be assigned to each of these cells. Enjoy!
If you try to double click on the cell and then try to type in some value that’s not present in the dropdown source, then you will get a message saying The value you entered is not valid. This way, the purpose of restricting values that can be entered into a cell gets met.
Please tell us in the comments section whether you found the article useful.
Stay tuned for more tricks and tips.