[FAQ's: OD] How to do Data Validation in MS Excel-VBForums
Results 1 to 2 of 2

Thread: [FAQ's: OD] How to do Data Validation in MS Excel

  1. #1

    Thread Starter
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Mumbai, India

    [FAQ's: OD] How to do Data Validation in MS Excel

    Data Validation

    Note: This has been tested in Excel 2003

    Excel’s data validation feature is similar in many respects to the conditional formatting feature. This feature enables you to set up certain rules that dictate what you can enter in a cell.

    For example

    You may want to limit data entry to whole numbers between 1 and 13. If the user makes an invalid entry, you can display a custom message as shown in the picture below.

    Specifying validation criteria

    To specify the type of data allowable in a cell or range, follow these steps:

    a) Select a cell or a range. Say A1:D10

    b) Choose Data=>Validation. Excel displays the Data Validation Dialog Box.

    c) Click on the settings Tab
    d) Choose an option from the drop down box labeled ‘Allow’. Let’s say “Whole number”

    e) Specify the conditions by selecting the drop down box labeled Data. As shown below

    f) (Optional) Click on the input Message Box Tab and specify which message to display when a user selects the cell. You can also use this step to tell the user what type of data is expected. As shown below

    g) (optional) Click the Error Alert tab and specify which Error message to display when a user makes an invalid entry. As shown below

    h) Click OK to see the result as shown in picture below

    Types of Validation Criteria

    The settings tab of the data validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow Drop-Down box

    a) Any Value: Selecting this option removes any existing data validation.
    b) Whole Number: The user enters only whole numbers
    c) Decimal: The user must enter a decimal
    d) List: The user must choose from a list of entries you provide (covered later in this thread)
    e) Date: The user must enter a date
    f) Time: The user must enter a time
    g) Text Length: The user can enter text up to a certain length
    h) Custom: A logical formula determines the validity of the user’s entry

    The settings tab of the data validation dialog box contains two other check boxes:

    i) Ignore Blank: If checked, blank entries are allowed
    j) Apply these changes to 'All' other cells with the same setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria.


    Even with Data Validation in effect, the user could enter invalid data. If the Style setting in the Error Alert Tab of the Data Validation dialog Box is set to anything except Stop, invalid data can be entered. Also remember that Data validation does not apply to the calculated results of formulas. In other words, if the cell contains a formula, applying conditional formatting to that cell will have no effect.

    Creating a drop down list

    Perhaps one of the most common uses of data validation is to create a drop down list of items {at least for me :-) }. The figure below shows an example that uses the month names in A1:A12 as list of source.

    First, enter the list items into a single-row or single-column range. (These are the items that will appear in the drop-down list). Then select the cell that will contain the drop down list and access the data validation dialog box. In the settings tab, select the list option and specify the range that contains the list using the source control. Also, make sure that the In-Cell Dropdown check box is checked. See picture below.

    After performing these steps, the cell displays a drop-down arrow when it is activated. Click the arrow and choose an item from the list that appears. (See picture 8)

    a) Accepting text only

    To force a range to accept only text (no vales), use the following data validation formula:


    This formula assumes that the active cell in the selected range is cell A1.

    b) Accepting a larger value than the previous cell

    The following data validation formula enables the user to enter a value only if it’s greater than the value in the cell directly above it:


    This formula assumes that A2 is the active cell in the selected range. Note that you can’t use this formula for a cell in Row1

    c) Accepting non duplicate entries only

    The following data validation formula does not permit the user to make a duplicate entry in the range A1:C20


    This formula assumes that A1 is the active cell in the selected range. Note that the first argument for COUNTIF is an absolute reference. The second argument is a relative reference, and it adjusts for each cell in the validation range.

    d) Accepting text that begins with “k”

    The following data validation formula demonstrate how to check for a specific character(s). In this case, the formula ensures that the user’s entry is a text string that begins with the text “k” (either uppercase or lowercase) see picture below.

    =LEFT(A1)= “k”

    This formula assumes that the active cell in the selected range is Cell A1

    Last edited by RobDog888; Feb 26th, 2007 at 11:02 PM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  2. #2
    Frenzied Member
    Join Date
    Aug 2006
    India, Punjab, Bhatinda

    Talking Re: [FAQ's: OD] How to do Data Validation in MS Excel

    Thanks Koolsid,
    One more thing I noticed was that the list can also be typed directly in Source. Each value seperated by commas. Similarly the list can be generated for one cell and dragged to other cells or copy ->paste special->validation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

Survey posted by VBForums.