To require that numbers be a specific multiple of another number, you can use data validation with a custom formula based on the MOD function. In the example shown, the data validation applied to C5:C10 is based on this formula:


This validation rule requires that the number entered in cell C5 be a specific multiple of 100.

Generic formula



In this example, the goal is to create a data validation rule that will only accept numbers that are a specific multiple of another number. In the worksheet shown, the multiple is 100. Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When the formula returns FALSE, validation fails and the input is rejected. This means we need to create a formula that will return TRUE when input is a multiple of 100 and FALSE when input is not a multiple of 100. We can do this with the MOD function.

MOD function

The MOD function returns the remainder of two numbers after division. For example, with a number of 10 and a divisor of 3, MOD will return 1, the remainder after dividing 10 by 3:

=MOD(10,3) // returns 1

In the worksheet shown, we want to validate that entered in column C numbers are a multiple of 100. The formula used to validate input looks like this:


The value in C5 is 500. The MOD function divides 500 by 100 and gets 5, with a remainder of zero. Since 0 = 0, the rule returns TRUE and the data validation passes:


If a user enters, 550, the remainder is 50, and validation fails:


The formula returns FALSE and data validation fails.

Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case, C5.

Fractional values

Although the example above deals with whole numbers, you can use the same approach to validate fractional values as well. For example, to require that the value entered in cell A1 be a multiple of 0.25, you can use a data validation rule with a formula like this:


Effectively, this means the input to A1 must end in 0, 0.25, 0.50, or 0.75.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.