Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Data Validation is an excellent way to control data entry to meet a certain condition.

Suppose you want to insure that numbers entered in the yellow cells are OK for decimals, AND that only numeric increments of a quarter of a number are allowed. For example, the entry of 6.75 or 3.25 are allowed, but 4.35 or 1.62 are not allowed.

The following steps show how this can be done.

Step 1 of 5
Select the range of cells where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =MOD(D5,0.25)=0

Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.