Excel Data validation whole percentage only
Set criteria to allow whole percentage only without decimals.
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.
Formula
=TRUNC(A1*100)=(A1*100)
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.
Explanation
In the example shown, the data validation applied to B5:B9 is:
=TRUNC(C5*100)=(C5*100)
How this formula works
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 to allow we use TRUNC 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.