3 must-know custom data validation formulas for preventing errors in Excel
Data validation in Microsoft Excel is a great way to limit what can be entered into a cell. However, creating rules using a formula allows you to apply even more precise settings. I use three in most of my spreadsheets.
Where to enter custom data validation formulas (and how to set up alerts)
Before I share my formulas with you, I’ll quickly show you where you’ll need to enter them and how to set up error alerts.
Select all relevant cells and in the Data tab, click the main part of the “Data Validation” split button. Next, click “Custom” in the Allow drop-down list on the Settings tab and enter the formula in the Formula field.
Once you’ve entered your formula, open the “Error Alert” tab, set the style to “Stop” and enter a title and message that will appear when the rule you set is violated.
Everything You Need to Know About Data Validation in Microsoft Excel
Set rules to control data entries in Excel.
Formula 1: Guarantee unique entries
Data validation can be used to avoid duplicate values in a column, such as employee IDs or invoice numbers, so that each entry differs from the others.
Let’s say you are entering an employee’s details into this Excel table and you want all values in the Employee_ID column to be unique. If someone tries to enter an ID that already exists, you need Excel to block the entry.
To apply this, first select the entire column in which you want the rule to be applied.
To select a column in a formatted Excel table, select the top-most cell (excluding the column header) and press Ctrl+Shift+Down Arrow. You can also hover your mouse over the column header until you see a small black downward arrow, then click once.
Next, launch the “Data Validation” dialog box and in the Settings tab, enter this in the Formula field (assuming you want to restrict column A):
=COUNTIF($A:$A,A2)=1
Here’s what each part of this formula does:
- $A: $A: Dollar signs lock the column range, so the rule always checks the same complete list of entries in column A. This future-proofs the formula and accounts for the fact that you cannot use structured table references in custom data validation formulas.
- A2: Excel automatically adjusts this relative reference so that each cell in the column is validated individually.
- =1: This is the condition of the test. The formula returns TRUE (with validation success) only if the entered value appears exactly once in the range.
Next, go to the “Error Alert” tab, select “Stop” and enter a suitable title (like Duplicate ID detected) and a message (such as This employee ID already exists in the list. Please enter a unique ID.)
Finally, click “OK” and enter a duplicate identifier in the next cell in column A to see the uniqueness safety net in action.
Data validation only runs when you enter or edit data after a rule has been imposed on a cell.
Formula 2: Block future date entries
When recording past events in an Excel spreadsheet, you can use the simple but powerful TODAY function to ensure that future dates are not captured.
Let’s say you’re tracking shipping dates in column B of this table and want to ensure that only past dates can be entered.
First, the cells in this column must be correctly formatted as dates in order for Excel to recognize the entry. To do this, after selecting the column, press Ctrl+1 to open the Format Cells dialog box, and then on the Number tab, select “Date.” Next, choose a format that suits you and your regional settings, and click “OK”.
After closing the Format Cells dialog box, with the range still selected, open “Data Validation”, select “Custom” and enter the following formula (assuming the dates are in column B):
=B2<=TODAY()
Here’s how the formula works:
- B2: This is the reference relating to the cell being validated.
- <=: When used to evaluate dates, the less than or equal to operator means before or on the date of.
- TODAY(): This returns the current system date.
Even though the TODAY function updates every time the workbook is opened or recalculated, Excel does not rerun the validation check unless the cell containing the date is changed. This means that a registration accepted today will always remain valid.
Then, in the Error Alert tab, select “Stop” and add an appropriate title (such as Future date not allowed) and a message (like The date must be today or a date in the past. Please correct your entry.)
Now, click “OK” and enter a future date to see an error message that blocks the invalid entry.
To block past dates instead of future dates, return the operator < à > in the formula.
Why use Custom instead of the built-in date option
Excel has a built-in Date option in the Allow field that also achieves the same result. However, using Custom is a better option for two main reasons:
- Logical combination: It’s easier to combine the TODAY function with AND or OR to apply two rules at the same time.
- Flexibility: Entering a formula allows for complex dynamic date logic, such as using EOMONTH or checking only the days of the week.
Stop checking Excel formulas one by one: use this function instead
This simple feature acts as an essential safety net for your binders.
Formula 3: impose a specific input format
One of the biggest problems when cleaning data is inconsistent formatting. For example, you might have a column whose entries must contain 10-digit numbers, so any entries that are longer, shorter, or contain letters should be rejected. You can apply strict rules on data length and data type simultaneously using AND, LEN, and ISNUMBER.
Let’s say you have this spreadsheet set up and you are about to enter serial numbers in column B. These must be 10 characters long and contain only numbers.
To implement this rule, select the entire column, open “Data Validation”, select “Custom” and enter this formula (assuming you want to restrict the entries in column B):
=AND(LEN(B2)=10,ISNUMBER(B2+0))
This is what we apply with this formula:
- AND(…): This function ensures that all conditions must be met for validation to succeed.
- LEN(B2)=10: The LEN function counts the number of characters in cell B2, and it must equal 10.
- ISNUMBER(B2+0): Adding +0 is an Excel trick: if cell B2 contains only numeric characters, this forces Excel to treat it as a number, even if the cell is formatted as text. If all characters are numbers, ISNUMBER returns TRUE.
Then, in the Error Alert tab, select “Stop” and enter a title (like Invalid entry) and a message (such as The entry must be exactly 10 digits long and contain no letters or special characters.)
Click “OK” and enter a nine-digit number or 10-character alphanumeric code to view the alert.
In the third example above, I showed you how to limit the number of characters and only allow numbers. However, you can enter custom data validation rules that enforce a more nuanced combination of letters and numbers in a particular order, such as one letter followed by six numbers. The key in this scenario is to create the rules in the worksheet itself and then copy the results into the Data Validation dialog box.
- Operating system
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1TB of OneDrive storage, and more.
