# 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.