Computer Basics -9 || Understand and Implement Data Validation in Excel.

Data Validation is the feature to restrict the end user to enter incorrect or invalid data in a particular cell or report.

Data Validation

Data Validation can be used for a range, specific list of values, formula-based condition etc. based on the requirement.

There are multiple scenarios where we need to restrict the user, few examples are given below:

  1. A user can enter a value within the range of 10-20.
  2. The user can enter a value less than equal to the existing value of already filled data.
  3. The user can enter a value from a specific list of items only.
Excel Data Validation
Excel Data Validation



Scenario 1:

The user can enter a value within the range of 10-20 using WHOLE NUMBER option.

  1. Select a cell or range where data validation needs to be implemented.

    Whole Number
    Whole Number
  2. Go to Data Menu > Data Validation > Data Validation
  3. Click on Allow drop-down > Select Whole Number
  4. Click on Data drop-down > Select Between (There are many other options available)
  5. Click on Minimum field > Enter lower range (here 10)
  6. Click on Maximum field > Enter higher range (here 20)
  7. Click on OK
  8. Try to enter a value outside range 10-20, the user would get below error.
  9. Enter a value within the range 10-20, no error appears.

Scenario 2:

A user can enter a value less than equal to the existing value of already filled data using CUSTOM option.

To understand this consider below example, any computer good can’t have Selling Price more than MRP(Maximum Retail Price).

Excel Data
Excel Data
  1. Click Data > Data Validation > Data Validation.
  2. Click on Allow drop-down and select Custom.
  3. Click on Formula field and select cell picker icon. 
  4. Select the range where validation needs to be implemented for column C. (Here select range C2 to C5)
  5. Type comparison operators <=

    CustomDialogbox
    CustomDialogbox
  6. Select target value or column (Here B2)
  7. Again click on the cell picker icon (mentioned in Step #3)
  8. Instead of performing step 3 to 7 you can directly type below formula into the Formula field. =C2:C5<=B2
  9. Click OK
  10. Try to enter any value greater than Column B under the Selling Price column.
  11. A user will get the same error message as mentioned in Scenario 1.
  12. Now try to enter any value less than equal to Column B. No error appears.

Scenario 3:

A user can enter a value from a specific list of items only using LIST option.

  1. Select the range where validation needs to be implemented
  2. Click Data > Data Validation > Data Validation.
  3. Click on Allow drop-down and select List.
  4. Click on the Source field
  5. Type comma-separated list of items like (Mouse,Keyboard,Wire,Bluetooth so on)
  6. Click on OK.

Instead of entering the values manually into the Source field, we could also use any list present in the same excel document.

  1. In place of performing steps 5 to 6, just click on Cell picker icon for Source field.
  2. Select the range of items (Here we could select values from Column A,the example of Scenario 2)
  3. Again click on Cell picker icon of Source field.
  4. Click OK
  5. Now, we will observe that there is tiny drop-down icon present beside the selected cell (where validation was implemented)
  6. On clicking the drop-down icon list of items will appear on the screen and ready to be use.
  7. Try to enter(type) any value other than list items. We would get an error message.

Computer Basics



1544total visits,6visits today

Leave a Reply

Your email address will not be published. Required fields are marked *