How to use data validation to prevent blank cells left in Excel column?
In some cases, you do not allow the blank cells left in a column which means that must enter something in cells of a column. In this article, I will introduce the Data Validation function to handle this job in Excel.
Prevent duplicate values entering in a column in Excel
|In some times, you want to prevent users to enter duplicate values in a column of Excel sheet, but how can you handle it? The Prevent Duplicate utility of Kutools for Excel just need you to click twice time, the job will be done. Click for 60 days free trial!|
|Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.|
To prevent blank cells left in a column, you just need to use a simple formula in Data Validation function.
1. Select the column you want to prevent blank cells left, click Data > Data Validation.
2. Under Settings tab, select Custom from Allow drop-down list, then type this formula >=COUNTIF($F$1:$F1,"")=0 into Formula textbox.
F1 is the first cell in the selected column.
3. Click OK. From now, if you left a blank cell and jump to next cell to enter value, a warning box will pop out to stop you.
If you want to prevent duplicate data entry in a column, you can try Kutools for Excel’s Prevent Duplicate utility.
|Kutools for Excel, with more than 200 handy functions, makes your jobs easier.|
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
Select the column you want to prevent duplicate entry, click Kutools > Prevent Typing > Prevent Duplicate.
Then click Yes > OK to close the reminders.
From now on, it will pop out a warning to stop duplicate entering.