Excel Drop Down List (Table of Contents)
Introduction to Drop Down List in Excel
In excel drop-down list is a useful feature that enables us to choose the value from the list box. Drop Down List in Excel is mainly used in an organization like data entry and medical transcription & data dashboards to choose and update the validation data in an easier way from the Drop Down list. Hence Excel Drop Down List saves the time where we can avoid errors in the validation part.
We can easily create a Drop Down list in excel by choosing the data tab where we can find the data validation option.
How to Create a Drop-Down List in Excel?
Click on the data menu and choose the data validation menu as shown below.
Click on the Data Validation TAB so that we will get a dialogue box that appears as follows.
Now we can see the Validation criteria part where it has to allow option click on the dropbox where it will list the several validation parts like the whole number, decimal, etc.
From the validation, criteria choose the List option to create an Excel Drop Down List, which will create and ask for selecting a cell to display the Drop Down List in Excel.
We can easily create a professional template by using the Drop Down List in Excel, mainly used for the data validation part.
23 Online Courses | 9 Hands-on Projects| 110+ Hours| Verifiable Certificate of Completion
4.9
View Course
First, create a set of a database to be shown in the drop-down list; this database has to be maintained in one sheet where the data comes from this main master database, So it is an important task to the main database need to be updated and maintained properly.
Let us now see how to create this with the help of some examples.
Excel Drop Down List – Example #1
Let’s see an example where we are going to create a set of students database with Name, Roll No and Grade. So that it will be useful to choose the name, roll no, and grade for the specific students from an excel Drop Down list in the easiest manner.
In the above table, we can see the database, which has student name, Roll No & Grade. To create Drop Down list in Excel, follow the below steps as shown below:
- Click on the Data Tab. Choose Data Validation.
- Data Validation Dialogue box appears as follows.
- Form Validation criteria choose the List option.
- Select the source tab where excel will ask for the database cell to appear in the drop-down list.
- Once you click on the source tab, we get the data validation selection box as follows.
- Select the column where the drop-down list needs to be get displayed and select columns A2:A8 for the student name drop-down list and then click ok.
- Once we gave the selection column, we will get a drop-down list as shown below.
We can see in the above screenshot an excel drop-down list created for the student’s name, which shows the student’s name list. Drop Down List feature allows us to select students’ names from the database list to avoid error and avoid entering duplication in the excel sheet.
Now let’s see how to get the Roll No to the drop-down list as follows:
- Go to the Data tab.
- Select the data validation option.
- You will get the dialogue box which will show validation criteria.
- Choose the List option from the drop-down list.
- Place the cell in the F column as shown below in the screenshot to get the drop-down list menu.
- Select the database column from B2:B8.
- Click ok.
So the result would be:
Now let’s see how to get the Grade to the drop-down list as follows:
- Go to the Data tab.
- Select the data validation option.
- You will get the dialogue box which will show validation criteria.
- Choose the List option from the drop-down list.
- Place the cell in the G column as shown below in the screenshot to get the drop-down list menu.
- Select the database column from C2:C8.
- Click ok.
The result will be:
Hence we have created a drop-down list for all Student Name, Roll No, Grade. We can now create the Drop Down list to choose the students name and their corresponding roll no and grade, as shown in the below screenshot.
We can see that a drop-down list has been created for only one column to apply to all the columns wherever we need; we have to copy and paste the drop-down box to the specific cell.
Now in the above screenshot, we can see that the drop-down list has been applied to all the column where it shows the entire students name from the database and Roll No and Grade.
Example #2
In this example, we will see how to add a “YES” or “NO” drop-down with a clear example.
Here in the status, we will add Drop Down List “YES” Or “NO” to know the present status of the students.
- Go to the Data tab.
- Select the data validation option.
- You will get the dialogue box which will show validation criteria.
- Choose the List option from the drop-down list.
- Place the cell in the F column as shown below in the screenshot to get the drop-down list menu.
- Select the database column from B2:B3.
- Click ok.
Now we can see the above drop-down list box of YES or NO to check the student present status.
Example #3
Error Message
Here is what happens if we type invalid data, i.e. unknown name list, from the database. Drop Down List feature first checks in the database list; if the name is matching, excel will not show an error dialogue box.
Assume that we have typed invalid data in the student name list like “geethas”, where we can see that this student name is not all in the database list. Dropdown list feature checks in the database whether names are matching or not; if not, it will display an error dialogue box as follows.
We can edit this error message so that the end-user can understand what data he needs to enter it.
In data validation, we can see that there are three tabs like Settings, Input Message, and Error Alert.
Here we are going to edit the error message as per our student database and follow the below procedure.
- Go to the Data validation tab.
- Select the Error Alert tab.
- We will get the above dialogue box with Title and Error Message.
- Give the Title Name and Error Message to be displayed for the cell.
Hence we have updated the Title Name and Error message to be displayed now; let’s see how the error is going to appear in the dialogue box.
Assume that we have typed an unknown name in the student name column, and the drop-down list feature will throw the following error with custom Title and Error Message as follows.
We can see that we have typed an unknown name like “Samantha”, which is not there in the database and the excel shown as an error message where we have given manually in the Error Message Dialogue box as highlighted above.
Things to Remember
- Maintain a separate database for creating a drop-down list in excel.
- Use an appropriate error message wherever we need it.
- Apply data validation to the specific cell.
Recommended Articles
This has been a guide to Drop Down List in Excel. Here we discuss creating a Drop Down List in Excel and practical examples and a downloadable excel template. You can also go through our other suggested articles –
