The Sundarban
Published Apr 5, 2026, 3:00 PM EDT
Yasir is a Mechanical Engineer who writes about tech at MUO, covering Windows, Productivity, Security, and the Internet. His pastime in self sustaining programs keeps him always tinkering with both hardware and software program.
His tech writing scoot started at some level of his junior 365 days of engineering, leading him to Android Police earlier than joining MUO. He makes a speciality of making technology accessible, whether he is troubleshooting Windows issues, exploring productivity tools, or explaining security risks in straightforward English. For Yasir, the handiest formulation to finish this is to actually utilize the tools and chase into the same issues readers face.
When he is now not writing or engineering, you can also secure Yasir gazing Impractical Jokers and if reality be told laughing at pranks he is considered a dozen times earlier than.
Most of us scheme up a dropdown list to form spreadsheets smarter with Excel’s data validation. On the opposite hand, the Custom option in the Data Validation dialog accepts any system that returns TRUE or FALSE, that means you can enforce principles that dropdowns weren’t constructed to deal with.
Duplicate prevention, textual content material sample requirements, date restrictions, and calculated limits all chase via one setting that nearly all of us skip fine past. Here’s how I utilize customized formulation to lock down my spreadsheets smartly.
Related
Custom formulation benefit you withhold a watch on far more than a list
The Custom option accepts any TRUE/FALSE system as a validation rule
Excel’s data validation has several constructed-in alternate choices reminiscent of entire numbers, decimals, dates, textual content material length, and, clearly, dropdown lists — it’s a form of Excel strategies value finding out early. These duvet total situations, but they’re rigid. The moment you wish a rule that does now not fit neatly into a form of classes, you are stuck. That’s where the Custom option comes in.
It permits you to write a system that evaluates to TRUE or FALSE. If the system returns TRUE, Excel accepts the entry. If it returns FALSE, the entry will get rejected. Here’s how one can scheme it up:
Decide the cell or range you wish to must validate.
Fling to the Data tab and click on Data Validation.
Under Enable, select Custom.
Enter your system in the System field.
Click on the Error Alert tab and write a sure message explaining why an entry can even be rejected.
Click on OK.
Be mindful that once making utilize of validation to a range, your system must peaceable reference the first cell in that range. Excel mechanically adjusts the reference for the closing cells, just love it does if you inch a system down. For people who secure this tainted, your validation will either now not work or declare the same take a look at to every cell despite position.
Any system that produces a TRUE/FALSE result works — COUNTIF, AND, OR, LEN, LEFT, ISNUMBER, or even nested combos. For people who can write it in a cell, you can utilize it as a validation rule.
Discontinue duplicate entries with a COUNTIF system
It flags repeats earlier than they scheme off issues
Duplicates are one of many commonest data entry mistakes, and they’re anxious to desirable up after the reality. Employee IDs, invoice numbers, and electronic mail addresses ought to not appear twice in the same column. As a substitute of scanning for duplicates manually, looking out to highlight duplicates in Excel after the reality, or working a cleanup later, you can end them at the level of entry.
You can utilize the following system:
=COUNTIF($A:$A,A1)=1
This counts how repeatedly the brand in A1 looks in column A. If it looks precisely once, the entry is legitimate. If COUNTIF returns a brand increased than 1, it capacity a duplicate already exists, and Excel blocks it.
To illustrate, inform you are monitoring advise IDs in column A. You enter “ORD-1042” in A2, and it’s permitted because it’s the first instance. If any individual tries entering “ORD-1042” once more in A10, the validation kicks in and rejects it.
The utilize of $A:$A because the variety works fair for smaller datasets, but it forces Excel to scan the overall column. For people who to find themselves working with hundreds of rows, narrowing the variety to something love $A$2:$A$500 improves efficiency. It’s a minute adjustment, but it issues on increased spreadsheets.
Power entries to follow a particular sample or length
LEN and LEFT withhold the data consistent
Inconsistent entries are a silent issue. Any individual forms a 5-persona product code where it wishes to be 8, or skips the major prefix, and now your filter breaks downstream. Custom validation can enforce both length and sample in a single rule. For length, you can utilize the following system:
=LEN(A2)=8
This rejects the rest that isn’t precisely 8 characters. For a required prefix, utilize LEFT:
=LEFT(A2,3)=”PRD”
You can combine both conditions with AND to enforce them simultaneously:
=AND(LEN(A2)=8,LEFT(A2,3)=”PRD”)
To illustrate, in case your stock system makes utilize of codes love “PRD-1047”, always 8 characters, always starting with “PRD”, then this system ensures every entry follows that constructing. Form “PRD-1047” and it’s permitted. Form “PR-1047” or “INV-1047” and Excel rejects it on the predicament.
This is helpful when more than one of us work on the same spreadsheet. You can’t always withhold a watch on how fastidiously any individual reads the formatting instructions, but you can be obvious Excel enforces them regardless.
Restrict dates so no one enters the rest in the past
TODAY() keeps your date entries forward-making an attempt
A past date in a deadline or shipping column is nearly always a mistake. Moderately than catching these errors at some level of review, you can block them outright with a straightforward system:
=A2>=TODAY()
This ensures every date entered is either at the present time or one day. Since TODAY() recalculates mechanically, the validation stays present without any e-book updates. You can plod further by proscribing entries to a particular window. To illustrate, if you handiest need dates within the subsequent 30 days:
=AND(A2>=TODAY(),A2<=TODAY()+30)
This proves at hand for scheduling forms where any individual booking a gathering three months out can be just as tainted as entering the day prior to this’s date.
