Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet that I have cells that I want to restrict entry to be either a whole number, or a half number.

Allow say 5 or 5.5 or 9.5.

NOT allow say 3.2 or 5.7.

I'd like to do this with a formula, not a macro.

share|improve this question

You can do it via data validation.

  1. Select the cell in which you want to restrict the input and click on Data > Data Validation

  2. Select 'Custom' from the drop down and in the formula field, you use the formula:


This basically means that cell A1 will accept values where the remainder when A1 is divided by 0.5 is 0 (i.e. it is a multiple of 0.5).

If a user tries to put an invalid value, a warning will pop up and warn that that value is not accepted. You can customize the message.

share|improve this answer
This is a good answer and correct. The only issue you may run into is if you want to disallow negative numbers. – Raystafarian Apr 9 '14 at 18:39
@Raystafarian Indeed, but since nothing was mentioned about those in the question, and negative numbers are whole numbers too! In any case, that can be fixed (should negatives be rejected as well) with AND() and A1>0. – Jerry Apr 9 '14 at 18:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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