How To Create Dropdown List In Excel? – Easy & Clear 3 steps!

You are currently viewing How To Create Dropdown List In Excel? – Easy & Clear 3 steps!
Share this post and Earn Free Points!

Manual input of data in MS Excel forms and text cells can lead to many errors and occurrence [ How to create dropdown list in Excel ] 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!

Introduction

Microsoft Excel

Microsoft Excel is a spreadsheet application that allows you to store, organize, and analyze data. It is part of the Microsoft Office suite of productivity tools and is available for both Windows and Mac operating systems.

Excel allows you to create and format worksheets, enter and manipulate data, and create charts and graphs to visualize your data. It also includes a range of functions and formulas that you can use to perform calculations and analysis on your data. You can use Excel to organize and track expenses, create budgets, generate reports, and much more.

Excel files are saved with the .xlsx or .xls file extension and can be shared with other users, who can then view and edit the data using Excel or a compatible program.

Excel DropDown List

A dropdown list in Excel is a list of items that you can select from a dropdown menu. You can use a dropdown list to create a list of items that you can select from, making it easier to enter data into a spreadsheet and reducing the risk of errors.

Drop-down lists are very easy to use and friendly for business users, and they will let you become a superuser of MS Excel. 🙂

Create DropDown In 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.

To create a dropdown list in Excel, follow these steps:

  1. Select the cell where you want to create the dropdown list.
  2. Go to the Data tab on the ribbon and click the Data Validation button.
  3. In the Data Validation dialog box, select the Settings tab.
  4. Under the Allow dropdown, select List.
  5. In the Source field, enter the list of items that you want to include in the dropdown list, separating each item with a comma.
  6. Click OK to close the Data Validation dialog box.

1. MS Excel: 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. 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 (How to create dropdown list in Excel).

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 (How to create dropdown list in Excel)

Now, when you click on the cell, a dropdown arrow will appear. You can click on the arrow to open the dropdown list and select an item. You can also type an item directly into the cell, but it must match one of the items in the list exactly in order to be accepted.


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.

How to create dropdown list in Excel? - Easy & Clear 3 steps!
How to create a dynamic drop-down list in Excel

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

How to create dropdown list in Excel? - Easy & Clear 3 steps!
How to create a dynamic drop-down list in Excel

Summary

A dropdown list in Excel allows you to select from a pre-determined list of options within a dropdown menu, making data entry easier and more accurate by eliminating the risk of typos.

That’ all about: How to create dropdown list in Excel!

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 576

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?