Advance Excel (Custom Validation)

VALIDATION CONTROL IN EXCEL

INTRODUCTION
Validation control is a very powerful tool in Microsoft Excel and it is used to insert different types of validations in excel sheet.
Let's start it, with a simple example



EXAMPLE 1: RESTRICT USER TO ENTER 0-100 MARKS.

1. Select the area in which you want to add validation.

2. Click on "Data" menu.

3. Under "Settings" select the validation criteria as "Whole number"

4. Select "Between" under "Data"

5. Enter 0 in minimum value and 100 in maximum value.

6. Enter "Input Message" and "Error Alert" message.

7. Finally, click on "Ok" button.

ERROR ALERT STYLES - TYPES
Stop: Never allow to insert invalid values

Warning: Allow invalid values with a warning message.

Information: Allow invalid values with an information message.

CIRCLE INVALID DATA

Circle invalid data is an important utility that is used to mark the invalid data items.

Let's start an exercise on it ...

1. Select the area in which you want to add validation.

2. Click on "Data" menu.

3. Under "Settings" select the validation criteria as "Whole number"

4. Select "Greater than or equal to" under "Data"

5. Enter 33 in "Minimum" criteria.

6. Click on  "Error Alert" tab.

7. Select "Information" under "Style"

8. Write the Title and Error message.

9. Finally, click on "Ok" button.

10. Now insert some values in validated cells.

11. Click on "Circle invalid data" under validation control.

RESTRICT INVALID MOBILE NUMBERS

By using custom validation control we can restrict invalid contact numbers.
So let's start an exercise on it...

1. Select the area in which you want to add validation.

2. Click on "Data" menu.

3. Under "Settings" select the validation criteria as "Custom"

4. Write this formula under =LEN(A2: A15)=10 under "Formula.

5. Now write the message, Invalid contact number under "Error alert"

6. Finally, click on "Ok" button.

HOME