How to Add a Dropdown Menu in Microsoft Excel

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.


ADVERTISEMENT

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.

 

20 Search Icon Min

 

Step 2: In the search bar window, search for Microsoft excel and click on Excel from the Best match section.

 

12 2 Excel Open Optimized

 

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.

 


ADVERTISEMENT

1 Sheet 2 Min

 

Step 4: Now in Sheet2, type in your dropdown menu contents anywhere. I have used column A to type my contents in.

 

2 Enter Dropdown Values Min

 

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.

 

3 Select Cells Min

 

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.

 

4 Data Validation Min

 

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.

 

5 Source Min

 

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.

 

6 Sheet 2 Min

 

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.

 

7 Select Source Min

 

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.

 

8 Soirce Populated Min

 


ADVERTISEMENT

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!

 

9 Dropdpwn Added Min

 

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.

 

10 Restriction Window

 

 

Please tell us in the comments section whether you found the article useful.

Stay tuned for more tricks and tips.

ADVERTISEMENT