Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Excel Data validation number multiple 100

Accept only numbers that are multiple of 100

To allow only numbers in multiples of 100, you can use data validation with a custom formula based on the MOD function.



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.

Also See:   MOD function: Description, Usage, Syntax, Examples and Explanation


In the example shown, the data validation applied to C5:C10 is:


How this formula works

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.

Also See:   Basic timesheet formula with breaks in Excel

In this case, the MOD function is used to perform a modulo operation, which returns the remainder after division. The formula used to validate input is:


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, say, 550, the remainder is 50, and validation fails:

Also See:   Convert time to time zone in Excel

Leave a Reply

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