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


hack excel vba passwordms office 2007 interview questions answers pdfexcel macros how todynamic chart excelinsert comment excelworksheetfunction.sumvba recordvba display messagehow to unhide top rows in excelopen file vba accessmicrosoft word meeting notes templateexcel concatenate functionshort cut excelprograming with excelgrant syntax in sqlms excel data validation listremove excel macro passwordcolor formula in excel 2007unhide lines in excelweekly calendar template 2014 exceladvanced filter excel 2013macro security exceladvanced excel vba programmingexcel join cellsvba color namessort excel alphabeticallysql sort commandprivate function vbaiferror vlookup exceldrop column syntaxexcel macro current rowhow to remove blank rows excelvba dim as stringtcl databaseend vbalearn to code vbasave vba excelsave vba excelms excel tutorial with examplesinterior color excelexcel hyperlink buttonshortcut key for deleting sheet in excelvba valueunlock vba projectexcel import data from another workbookmath and trig functions in excelexcel 2007 unhide sheetdefine activex controlhow to merge columns in excel 2007create object vbapivot sql examplestick box in excel 2007excel vba insert columnadjust row height in excelvba passing variablesexcel vba text boxexcel pie chart titletrim function in excelusing hyperlinks in excelhard return excelvba entirecolumnhyperlink to file in excelhlookup example in excelmeeting minute templates freevbscript sql server connectionvba commandsopentext readingvanilla foldersexcel vba pastehow to learn excel vba programmingexcel vba basicsexcel macro to open a filemultiple if condition in excelvba excel activeworkbook