Data Validation in Excel – Adding Drop-Down Lists in Excel

Home/Excel/Data Validation in Excel – Adding Drop-Down Lists in Excel

We can control the type of data or the values that users can enter into a particular cell or range using Data Validation in Excel.

In This Section:

What is data validation and Its Use:

Data Validation is a feature available in Excel to define restrictions and what data can enter in a cell or a range.
For example,
1. We can restrict data entry to a certain range of values
2. User can select a choice form predefined list
3. We can display a message to provide the instruction to the user
4. We can display a message when user enter an incorrect value

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


Creating a simple list to enter gender of a person – Practical Learning

Step 1: Select a Range or Cells which you want to restrict or add data validation

data validation-1

Step 2: Click on the Data Validation tool from the Data Tab

data validation-2

Step 3: Set the Validation Criteria: Select List from the Allow drop-down list in the Setting Tabs

data validation-3

Step 5: Enter the values to show in the Drop-down list

data validation-4

You are done! Now you can check the range, your list is available to choose

data validation-5

How to choose list items from a Worksheet

It is a good practice to have your list of values in the worksheet and choose those values for drop-down list. It is particularly very useful when your data list is having more number of items or your list is changing frequently.

Follow the below Steps to choose the list items from worksheet:

  1. Select the Range / Cells to restrict or add data validation.
  2. Click Data Validation Tool from Data menu
  3. Click List in the Allow drop-down list from the Settings tab
  4. Click Source button to select the list Items
  5. Select the Range to fill the drop-down

data validation-6

How to set user instructions message

You can provide the instructions to the user while entering the data. In the following example, we will see how to restrict the enter values between a range and provide the user instructions.

Follow the below Steps to choose the list items from worksheet:

  1. Select the Range / Cells to restrict or add data validation.
  2. Click Data Validation Tool from Data menu
  3. Select Whole number from the Allow drop-down list in the Settings tab
  4. Select between number from the Data drop-down list in the Settings tab
  5. Enter Minimum and Maximum Values (example: 1 and 100)
  6. data validation-7

  7. Goto Input Message Tab and Enter Required Title and Instructions in the Input Message Box (example: ‘Note:’ and ‘Please enter any value between 1 and 100’), then Click on OK
  8. data validation-8

  9. You are done! Now you can select the cell, you can see the instructions
  10. data validation-9

How to Set user alert message

You can provide the error message to the user while entering the incorrect data. In the following example, we will see how to provide an alert message to the user.

Follow the below Steps to choose the list items from worksheet:

    The below first 5 steps are same as above

  1. Select the Range / Cells to restrict or add data validation.
  2. Click Data Validation Tool from Data menu
  3. Select Whole number from the Allow drop-down list in the Settings tab
  4. Select between number from the Data drop-down list in the Settings tab
  5. Enter Minimum and Maximum Values (example: 1 and 100)
  6. Goto Error Alert Tab and Enter Required Title and Instructions in the Error Message Box (example: ‘Note:’ and ‘You can only enter the values between 1 and 100’)
  7. data validation-10

  8. You are done! Now you can select the cell, and try to enter a value which is not between 1 and 100
  9. data validation-11

Example File

Download this example file and see different ways of using data validation features of Excel.

ANALYSIS TABS – Data Validation

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference
By |May 12th, 2013|Excel|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.