Data Validation

User avatar
Administrator
Site Admin
Posts: 9
Joined: 05 Apr 2020, 03:06
Location: PTP Nagar, Trivandrum
Contact:

Data Validation

Postby Administrator 10 Apr 2020, 20:24

Data Validation
Use data validation in Excel to make sure that users enter certain values into a cell.

Data Validation Example
In this example, we restrict users to enter a whole number between 0 and 10.
excel-data-validation-example.png
excel-data-validation-example.png (2.03 KiB) Viewed 586 times

Create Data Validation Rule
To create the data validation rule, execute the following steps.

1. Select cell C2.

2. On the Data tab, in the Data Tools group, click Data Validation.
click-data-validation.png
click-data-validation.png (7.95 KiB) Viewed 586 times
On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.
validation-criteria.png
validation-criteria.png (5.05 KiB) Viewed 586 times

Input Message
Input messages appear when the user selects the cell and tell the user what to enter.

On the Input Message tab:

1. Check 'Show input message when cell is selected'.

2. Enter a title.

3. Enter an input message.
enter-input-message.png
enter-input-message.png (4.02 KiB) Viewed 586 times

Error Alert
If users ignore the input message and enter a number that is not valid, you can show them an error alert.

On the Error Alert tab:

1. Check 'Show error alert after invalid data is entered'.

2. Enter a title.

3. Enter an error message.
enter-error-message.png
enter-error-message.png (5.39 KiB) Viewed 586 times

4. Click OK.

Data Validation Result

1. Select cell C2.
input-message.png
input-message.png (3.67 KiB) Viewed 586 times

2. Try to enter a number higher than 10.
Result:
error-alert.png
error-alert.png (1.92 KiB) Viewed 586 times
Note: to remove data validation from a cell, select the cell, on the Data tab, in the Data Tools group, click Data Validation, and then click Clear All. You can use Excel's Go To Special feature to quickly select all cells with data validation.


Reject Invalid Dates
This example teaches you how to use data validation to reject invalid dates.

1. Select the range A2:A4.
select-range.png
select-range.png (1.14 KiB) Viewed 585 times

2. On the Data tab, in the Data Tools group, click Data Validation.
click-data-validation (1).png
click-data-validation (1).png (7.95 KiB) Viewed 585 times

Outside a Date Range

3. In the Allow list, click Date.

4. In the Data list, click between.

5. Enter the Start date and End date shown below and click OK.
validation-criteria (1).png
validation-criteria (1).png (5.43 KiB) Viewed 585 times
Explanation: all dates between 5/20/2016 and today's date + 5 days are allowed. All dates outside this date range are rejected.

6. Enter the date 5/19/2016 into cell A2.

Result. Excel shows an error alert.
dates-outside-date-range-rejected.png
dates-outside-date-range-rejected.png (6.76 KiB) Viewed 585 times
Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.

Sundays and Saturdays
3. In the Allow list, click Custom.

4. In the Formula box, enter the formula shown below and click OK.
data-validation-formula.png
data-validation-formula.png (5.53 KiB) Viewed 585 times

Explanation: the WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. If a date's weekday is not equal to 1 (Sunday) AND not equal to 7 (Saturday), the date is allowed (<> means not equal to). In other words, Mondays, Tuesdays, Wednesdays, Thursdays and Fridays are allowed. Sundays and Saturdays are rejected. Because we selected the range A2:A4 before we clicked on Data Validation, Excel automatically copies the formula to the other cells.

5. To check this, select cell A3 and click Data Validation.
formula-check.png
formula-check.png (5.42 KiB) Viewed 585 times
As you can see, this cell also contains the correct formula.

6. Enter the date 8/27/2016 (Saturday) into cell A2.

Result. Excel shows an error alert.
sundays-saturdays-rejected.png
sundays-saturdays-rejected.png (6.61 KiB) Viewed 585 times
Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.
_____________________
Thanks & Regards
Saran BL

www.i-metrik.com

Was This Topic Useful?

Last 10 Members Who Visited This Topic

aedbhrgxb, Regaembaf, skumar21, User avatar Administrator

Return to “Basic Excel Training”

Who is online

Users browsing this forum: No registered users and 1 guest