Excel Add Drop-down List – How to Add Drop Down in Excel

If you are a user of Excel then you know how difficult it can be, at times, to guarantee the cleanliness and consistency of the data. Having a drop down list is probably the easiest way to maintain the information and try to avoid the errors. A drop-down list is a list or menu that a user can select a value or a choice from a list of options that has been already chosen as a criterion of the values, as opposed to the users entering a value. This ensures that there are no spelling mistakes, no formatting mistakes, and no misrepresentation of information.

Whether you are making forms for your employees, product reports, attendance sheets or budgeting templates, formatting a drop-down in Excel can help make your spreadsheets that much more professional and easier to use. The good news is that you don’t need to do much in terms of configuration and organization on your Instagram account.

How to Insert a Drop Down List in Excel?

Excel also has a Data Validation feature that can be used to create a drop down list. Here’s how to do it:

• Open your excel file and open the cell where you want to create a drop down list.
• Click the Data tab on the header on the top toolbar.
• Click Data Validation.
• Under Settings tab, choose List from the Allow drop down list.
• In the Source field, a list of items, separated by a comma.  (Example: Yes,No,Pending,Complete)
• A dialog will turn into a drop-down list of data in the selected cell, click OK.
• Now if you click that cell it will have this little arrow and you can select from the list.

Using a Range Instead of Using Values

If your list is quite extensive, there is no way in which you would be able to type all the items in your list. However, you can put your list into a range of cells.

Example:

Input your values in A1: A5.

Then:

Click on the cell where you want to place the drop-down in.

Data → Data Validation → List.

In the Source box, enter the range:

=A1:A5

Click OK.

This also makes it very easy to change the list by simply changing the values in A1 to A5 and the drop down will automatically change.

Using Multiple Cells to Make Drop-Down Lists

If you have a lot of cells that require the same drop-down list, then:

• Firstly is to make the drop-down in one cell.
• Select that cell and copy it.
• Paste into your target cells.
• The drop down functionality is provided along with it.

Why is it important to use drop down lists in Excel?

Drop-downs are particularly good for shared spreadsheets. They help:

• Reduce typing errors
• Keep data standardized
• Limit incorrect entries
• Speed up data entry
• Form and worksheet usability improvement.

For the organization, they present clean data and can be analyzed without any major rework to the data at a later date.

Customization of Your Drop Down List

You can even elaborate such an extension of your drop down like:

• Open a dialog when the user has clicked the cel
• Select: Data Validation -> Input message
• Writing a good Instruction (Example: Please select status from the drop down).
• Return an error message if not numeric input is typed in the field
• On the tab Data Validation, under its name, choose Error Alert.

Type the message that should appear when an individual tries to enter the message shown in the message box that is not on the list. This saves the user’s effort and has the advantage of providing the correct data entry.

Dynamic Drop Down Based on Another Sheet

If you have your data on a different sheet:

• Store your list (e.g., Sheet2!A1:A10).
• Select your drop down cell in your master sheet
• Data → Data Validation → List.

In Source, type:

=Sheet2!A1:A10

Click OK.

This is appropriate very much when you are designing structured and professional templates.

Dynamic Drop-down (Automatically Expands List as Growth Increases):

If you find that you will be adding new items to your list frequently, you should:

Select your list cells.

List: Press Ctrl + T to convert the list to the Table.

Data Validation will be provided in the range of Table.

Now whenever you add a new item in the table it will automatically add in the drop down list.

Conclusion

For one thing, including drop down lists in Excel is a small thing that makes a lot of difference in terms of organization, clarity and efficiency. Whether you are creating a form, setting up reports or building a collaboratively managed data sheet, drop-downs make input easy and clean.

You will probably use them increasingly after you already begin to use them-and when you do, you will find yourself no longer explaining Excel to other people, since you have created a file that is not only easier to use for everyone, but also more effective.

Also Read: Best SaaS tools for startups & SMBs