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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

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.

mongopono.ru – Data Validation

LIMITED TIME OFFER
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


Related pages


how to remove empty rows in excelexcel vba automationdays360 functionmicrosoft estimate templatehow do i make a pivot table in excel 2010excel vba operatorsmicrosoft outlook questions and answersvba directorymerging worksheets in excelvba integerexcel hyperlink to filestacked bar chart excel 2013excel macros vba examplesexcel hidden cellshow to protect a worksheet in excelvba delete worksheetaccess vba forecolorhow do i check for duplicates in excelexcel vba close userformvba xlqlikview introduction pptbubble charts excelvba worksheets.addwhat are the ddl commandsunshare excelvba tutorial advancedexcel delete double entriesformula excel vlookupexcel 2010 vba select casevba save excel fileinsert new worksheet excel 2010how to use vlookup in excel 2003 step by stepmerging cells excelvba procedurevisual basic checkboxsample excel macrosremove password protection excel 2010vba conditionalvb6 checkboxhow to remove cells in excelexcel unhide all columnsadodb connection string sql serversales trend analysis excel templatehow to get data from another worksheet in excelhse meeting minutes templatehow to automate graphs in excelvba code formattervba word textboxpivot sql exampleshow to merge excel columnshow to merge cell data in excelexcel vb macrounprotect xls fileremove empty cells in excelhlookup formulaexcel mysql odbcoffset in vbavba fileformatvba find last rowhow to learn vba coding in excelhow to lock an excel worksheetfind duplicates in excel sheetpowerpivot tutorial excel 2010vba opentextfilehow to write macros for excelunprotected excelexcel formula in vba codevba cellremove duplicates in excel 2007using vbaexcel insert pivot tablewhat are activex controls in wordshow developer tab in excel 2013pivot table dynamic rangenotepad vbaxml vbabasic excel for beginnersadvanced excel vlookup