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.

Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists