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


vba macro excel examplesexcel dynamic chart titlesql basics interview questionssort formula in excelvba macros in excel 2007 tutorials pdfhow do i lock excel spreadsheetadvanced excel tutorialvba excel activesheethow can i unprotect an excel sheetbyval vbaexcel vba select rowsexcel vba screen updatehow do you unhide columns in excel 2013developer tool in excel 2007chart wizard on excelexcel how to delete empty rowshow do you create a checkbox in excelhow to check if there are duplicates in excelhow to record macros in excelcopy file vb6ppt project plan templateexcel developers tabsleep function in vbadelete macro excelunprotect an excel sheet without passwordhow to create combobox in excel 2010writing an excel macroado connectionstringcombobox additem vbaexcel macro save as xlsxexcel 2010 unprotectinput message box vbavba code exceldcl statementusing ifs in excelexcel vba adodbabsolute value vbaexcel vba open applicationhighlight duplicate rows in excel 2010shortcut key insert row excelmacro to delete columns3d graphing excelunhide hidden columnsvba booleansql identify duplicatesvba squarecreate bar chart in excel 2010vba callvba code to save excel fileprotect workbook excel 2007excel timelines templateexcel pivotsms excel macros examplesopen excel file in vbacreate vba macrovba save filetask tracker excelexport vbakpi excel dashboard templatesexcel 2007 activex controlsuse of pivot table in excel 2010excel offset functionvba checkboxunshare excelsumif function in excelunprotect excel workbook 2013array vba excelexcel duplicate findervba syntax errorproject status dashboard excelmacro security excel 2007delete row excel shortcutsumif formulas in excelexcel rept functionsaveas vbaif isna vlookupall shortcut keys of excel 2007unlock protected excel sheetdays360 function