Drop Down List In Excel - Three Methods, Static and Dynamic - Delete Drop Down List

Sometimes we need to collect data from end-users in a specific format without giving them authority to write anything not related to the topic. Sometimes we also need to add data from some selective options in a specific Excel format. It is because if the data collected from the end-users varies on same topic and on same results then it becomes tedious job to work with that same data written differently. Therefore, we need some tool to make our format or more specifically the cells of an excel file to be selected from a drop down list.

So, friends, today our topic is how to create and utilize Drop-Down List in Microsoft Excel. It is very interesting because this technique will not allow users to enter random data in an uniform excel format. We shall create some constraint and validation rules to enter data in the Excel file.

Let me elaborate the requirement by the help of an example so that you can use it in proper place, suppose you have  an excel file with few questions and it needs to be filled by some different people in your country like, Do you like our sales unit ? Which is the best product in your view ? Which product needs up-gradation ? Will you use our product if we improve it ?.

If you analyze carefully the basic questions here, you can easily understand that these questions has specific answers, like 'Yes', 'Product 2', 'Product 5' etc., but in real life you will get numerous different types of answers and you have to work on this basic data only to rectify.

Here, we use constraints by using Drop down list. Hope you have understand the need and now we'll move to the discussion.

Description

Excel drop-down list or drop down box or combo box, is used to enter data in a spreadsheet from a pre-defined items list. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster.

How to create an Excel Drop-down list

There are two types of Drop-down Lists in my opinion, Static and Dynamic, both are equally useful. Both are very easy to use. There are some steps to follow and we are done.

Where you need to use such an option list that might change over time use Dynamic and if the list might not change in future use Static method.

1.     Select a cell or range for your drop-down list.

Start by selecting a cell or cells where you want a drop-down box to appear. This can be a single cell, a range of cells or the entire column. If you select the whole column, a drop down menu will be created in each cell of that column, which is a real time-saver, for example, when you are creating a questionnaire. You can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse.

2.     Use Excel Data Validation to create a drop-down list.

On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation.

3.     Enter the list items and choose the options.

In the Data Validation window, on the Settings tab, do the following:

a)     In the Allow box, select List.

b)    In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces).

c)     Make sure the In-cell drop down box is checked; otherwise the drop-down arrow won't appear next to the cell.

d)    Select or clear the Ignore blank depending on how you want to handle empty cells.

e)     Click OK and you are done!

End-users can simply click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu no deviation will occur.

4.     If you want to make this a more beautiful, you can add some information in the cells in the Data Validation Input message.

It is very simple add the message title (I wrote 'Note' in the image) and few words in the input message box.

IMAGE 7

5.     You can enter Error Alert also, if users tries to enter data using his/her own words there will be an error message.

Select Style, Enter Title and Error Message and done.

This was the Static Method, most of the job will be done through this method but Dynamic Drop down list is also has its' own importance where you need to update the list periodically.

Now my view is that the Dynamic Drop down list can be of two types, using two methods, Using Named List (Using Name Manager, already discussed in my previous article) and by creating Table..        

Dynamic Drop down list Using Name Manager:

Name Manager is a simple tool in excel which is used to name some collection of data, shortcut key is Ctrl+F3.

Follow the following steps to use Name Manager :

i.   Select the entire column.

ii.   Press Ctrl+F3, a new window will appear Name Manager

iii.  Click on New and this will open another small window New Name

iv.  Enter a name for this column, mine is Drop_down_List.

v.   Select the Scope whether it will be applicable to Sheet1 or Workbook.

vi.  Notice Refers to box is taking the selected column.

vii. Click OK and close the Name Manager window.

Now select the cell(s) where you want to put Drop Down list. Click Data from the menu, select Data Validation, click on Data Validation, Data validation window will appear.

Select List and in source box type '=' and your column name which you given in Name Manager, mine is "=Drop_down_list".

Do not uncheck any check box.

And you are done.

Dynamic Drop down list Using Table & Name Manager:

The concept of using Table in Drop Down List is using 

the property of the table of inheritance (I can say because) whenever you put a data below the table in normal cell, as you hit the enter it is converted to a part of table and if we use this property for list item generation it will be automatically added to Drop Down List. Lets' see how to do it.

i.   Select the Data to be listed.

ii.  Press Ctrl+T, this will open a new window, Create Table.

iii. Click OK.

iv.  Name this table Using Name Manager Ctrl+F3 (mine is Table_Test)

v.   Now select the cell(s) where you want to put Drop Down list. Click Data from the menu, select Data Validation, click on Data Validation, Data validation window will appear.

vi.  Select List in Allow box and type your table name in Source box.

vii. Hit Enter and you are done.

One more thing I would like to inform you additionally that while appling Data Validation rule to cell(s), either you can select all the cells or you can select one cell first and then drag them, this will work fine.

There is another Dynamic method is there using formula which I shall discuss in another article.

How to Delete Drop Down List ?

The procedure to delete a Drop Down List is also important because you might encounter problem while appending list or wrongly data entered or modification require in the excel format.

It is very easy. Go to Data Validation, Click on Clear All and click OK.