I have a custom SP list with a handful of non-required numerical fields. I'd like to write a column validation rule for each that would force users to enter any value >=0. Our users will be using Data Sheet View only and editing existing records, not creating new ones (so no Infopath or form solutions).

The rule I've tried is =[Target Field]>=0 This doesn't work because SP treats blanks as 0's. Users could simply skip this field and SP would still see it as "0" therefore passing validation.


  • Users should only be able to enter "0", or any other integer. They cannot leave the column blank.
  • Users are only editing existing records in datasheet view. Not creating new ones. Thus, making a field required will not work
  • Is there any reason that making the numerical fields required and setting their minimal value to 0 is not feasible?
    – Anon
    Commented Dec 4, 2019 at 16:34
  • I made an edit to my original question. It's important to note that users are editing existing records in datasheet mode, not creating new ones. We're essentially loading records and then having users modify a select number of numerical fields via quick edit. For this reason, toggling these fields to required after the data load, won't work. We're reliant on field and list validation rules/settings. Commented Dec 4, 2019 at 17:28

1 Answer 1

  1. Try using below formula (This will ensure the entered value is greater than 0):
=[Target Field]<>0
  1. Else, if you want to force your users add any integer including 0 in your numeric field then you need to make your field Required.

  2. Also you can set the default value of your column to 0 if user does not add any value in the column while creating a new item.

  • This doesn't work. Users still need the option to enter "0" Commented Dec 4, 2019 at 16:33
  • Also you can set min and max value to numeric column or make the field required (if there is no particular reason to not set it to required) from column settings. Commented Dec 4, 2019 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.