Data validation whole percentage only

To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the TRUNC function. In the example shown, the data validation applied to B5:B9 is:

=TRUNC(C5*100)=(C5*100)

Explanation

The Excel TRUNC function does no rounding, it just returns a truncated number. It has an optional second argument (num_digits) to specify precision. When num_digits is not provided, it defaults to zero. In this formula for data validation, we use TRUNC to get the non-decimal part of a percentage, after we multiply the percentage by 100.

For example, if a user inputs 15%:

=TRUNC(.15*100)=(.15*100)
=TRUNC(15)=(15)
=15=15
=TRUE

If a user enters 15.5%, the formula evaluates like this

=TRUNC(.155*100)=(.155*100)
=TRUNC(15.5)=(15.5)
=15=15.5
=FALSE

This formula doesn't validate anything else, for example, that percentages are less than 100%. Additional conditions can be added with the AND function.

Notes: Data validation rules are triggered when a user adds or changes a cell value. 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, B5.