Prevent Blank Cells with Data Validation

Question

I'm having a seriously hard time with the simplest of Excel data validations: how do you prevent a blank value? The user needs to enter a number into a cell. If the cell has no value, Excel should throw up an error message. I've tried the following:

=COUNTIF($A5:$A27, "")=0
=ISBLANK($A5)
=ISBLANK($A5)=FALSE

and a whole assortment of ridiculous Excel gymnastics, but nothing has worked. The Ignore Blank checkbox is off, too. How hard can this be?

This is in Excel 2016 running on Windows 10. I've tried Google searches ad nauseum with different search keywords. I've tried winging it. I've tried searching on here fora simple formula to solve the problem.

Answer 1

Assuming you also don't want to allow a value of 0 to be entered, you can use the following validation. However, validation only applies when you're entering values into a cell. If you're in edit mode, you won't be able to hit Enter while the cell is blank. But there's nothing to stop you from ignoring the cell entirely or even selecting the cell and clearing it's contents using the Delete key.

There's no way for Excel to force fields to be mandatory and with good reason. If Excel wasn't going to let you do anything while a mandatory cell was empty, you would be stuck if there were two mandatory cells because you could never complete one cell without Excel complaining about the other cell.

You can only force cells to be mandatory when you're also running macros which could display error messages instead of performing the required tasks if the mandatory fields aren't all complete.

As per the comment, the best thing to do is highlight empty mandatory cells using conditional formatting. From the Home tab, create conditional formatting based on value equal to 0 then set the background format to red (or whatever you want).