MS Excel – How to create drop-down list in Excel?

You are currently viewing MS Excel –  How to create drop-down list in Excel?

Manual input of data in MS Excel forms and text cells can lead to many errors and occurrence of dirty records in your data. If the user makes e.g. a typo in the city name or adds a space at the end of the text, it will be a completely new record in the database. By preparing forms, you can avoid such situations by allowing users to enter only allowed values in selected fields. Drop-down lists will help you with this!
Drop-down lists are very easy to use and friendly for business users, and they will let you become a superuser of MS Excel. 🙂

In this post I will show you how to create a drop-down list in MS Excel and how to inform the users if they wants to enter incorrect data. A short message will definitely help them understand what they did wrong.

1. Prepare a list of values

First, you need to prepare a set of values that will be available on your list. These may be cities in which your company has branches, months of the year or a list of products that your company sold.

2. Go to Data Validation tab

Put the mouse cursor in the cell where you want to enter the drop-down list. Then select the Data -> Data Validation tab in the toolbar:

In the allow field, choose the List option and click at the arrow icon to select your list of values:

3. Test your list

Your drop-down list in Microsoft Excel is ready to use! Check it by clicking on the cell where list was applied and choose one value from list.

Show the error message after putting the incorrect values

It may happen that a new user will want to enter a text value that is not on the drop-down list. Struggling with data validation through the list can lead to frustration and incorrect reporting of errors in the form. We can avoid this by informing users that a specific field only accepts the values available in the attached drop-down list.

To set a warning message, select the Data -> Data Validation -> Error Alert, choose Warning as a Style and put your own Title and Error message which the best describe situation.

If the users will want to put the invalid text value, they got the warning message.

If you enjoyed this post please add the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments