I used to put aside time each week to wash my spreadsheets by operating helper formulation, scanning for crimson cells from conditional formatting, and fixing typos that had snuck in over the previous few days. The cleanup by no means received shorter, so I gave Excel’s Information Validation dialog a more in-depth look. As an alternative of discovering errors after they’d already poisoned my pivot tables, I now block them the second somebody tries to enter an invalid worth.
Associated
Excel’s dynamic array features made me understand I might been fixing issues the arduous approach for years
Seems half my previous formulation have been simply dynamic arrays in disguise, written the good distance.
Information validation blocks the unhealthy entry as an alternative of discovering it later
The entire setup takes about 30 seconds per rule
Screenshot by Yasir Mahmood
My previous setup wasn’t damaged, precisely. I had a column of IF and COUNTIF formulation operating quietly subsequent to my knowledge, plus conditional formatting guidelines that turned cells crimson when one thing regarded off. Each Friday, I might scroll by way of and repair what lit up. The difficulty is that this method is reactive — by the point I caught a typo, it had already damaged a SUMIFS or cut up a class in my pivot desk.
Information Validation flips that order. It lives beneath the Information tab, and the fundamental concept is easy: you inform Excel precisely what’s allowed in a cell or vary, and Excel refuses the rest. To arrange a rule:
- Choose the vary you wish to defend.
- Go to Information > Information Validation.
- Decide a rule kind from the Permit dropdown, e.g., complete numbers, decimals, dates, time, textual content size, listing, or customized.
- Set the parameters (a minimal and most, a supply listing, or a formulation).
- Click on OK.
In my gross sales sheet, I used a whole-number rule on the Items Offered column to implement a spread between 1 and 100. Precise values in that column run from 7 to 44, so 100 leaves room with out letting in one thing nonsensical like 1,500 from a mistyped entry. That rule alone catches the type of mistake I might in any other case want a helper formulation and a crimson spotlight to search out.
OS
Home windows, macOS
Supported Desktop Browsers
All by way of net app
Developer(s)
Microsoft
Free trial
One month
Worth mannequin
Subscription
iOS appropriate
Sure
Dropdown lists eradicated my greatest supply of typos
One supply listing, zero misspellings throughout your complete sheet
That is the part of Information Validation I lean on most, and it is the one I want I might constructed into my workflow earlier. My gross sales spreadsheet has a Product Class column, and earlier than I added a listing rule, the identical column held “Electronics,” “ELECTRONICS,” and “electronics” as three separate values. “Residence & Backyard” and “Residence and Backyard” have been additionally splitting the identical class in two.
Once I constructed a pivot desk from that knowledge, I received duplicate rows for what ought to have been one product line. Here is how I fastened it:
- Arrange a small vary elsewhere on the sheet with the 4 allowed values: Electronics, Clothes, Residence & Backyard, and Sports activities.
- Choose the Product Class column.
- Go to Information > Information Validation > Permit > Record.
- Set the Supply to that vary (or convert it into an Excel desk and reference it as a named vary so the dropdown grows after I add a brand new class).
- Click on OK.
Now each cell in that column reveals a small arrow, and entries are restricted to these 4 choices. The pivot desk cleaned itself up the subsequent time I refreshed it.
Pointing the supply at an Excel desk beats typing values straight into the validation dialog, as a result of you’ll be able to add or take away classes in a single place and each dropdown updates routinely.
Customized formulation implement guidelines that the built-in choices cannot
A single formulation can cease duplicates, implement codecs, or catch logic errors
Screenshot by Yasir Mahmood
The Customized possibility in Information Validation is the place the characteristic will get genuinely helpful for messier guidelines. You write a formulation that returns TRUE for legitimate entries and FALSE for every little thing else, and Excel does the remaining.
Three guidelines I discover myself utilizing typically:
- Block duplicate entries. For an order ID column, =COUNTIF($E$2:$E$1000,E2)=1 stops anybody from logging the identical ID twice. That is a lot cleaner than operating a replica verify after the actual fact.
- Implement a format. If product codes in your sheet ought to at all times begin with “PRD-,” =LEFT(A2,4)=”PRD-” rejects something that does not. Helpful for holding code constant throughout collaborators.
- Catch logical errors. If column B holds an finish date and column A holds a begin date, =B2>=A2 blocks entries the place somebody enters a venture finish date that is sooner than the beginning.
The trade-off is price flagging. Customized formulation solely return a go or fail — Excel will not inform the consumer why their entry was rejected, which is the place the subsequent part is available in.
Hold customized formulation easy. Something you’ll be able to learn at a look is simpler to debug a yr from now once you’ve forgotten why you set the rule up within the first place.
Enter messages and error alerts make the principles apparent
A transparent immediate beats a silent failure each time
Screenshot by Yasir Mahmood
The Information Validation dialog has two different equally essential tabs: Enter Message and Error Alert. They’re what flip a silent rule into one thing a collaborator can really work with.
The Enter Message tab provides a small tooltip that pops up when somebody selects the cell. I exploit it to elucidate what’s anticipated earlier than anybody begins typing — one thing like “Enter a price between 1 and 100” or “Decide a class from the dropdown.” It would not block something; it simply solutions the query somebody would in any other case need to ask me.
The Error Alert tab is extra fascinating as a result of it offers you three kinds to select from:
- Cease rejects the entry fully. Finest for arduous guidelines, like distinctive IDs or class labels.
- Warning asks the consumer to substantiate earlier than accepting the worth. Good for tender guidelines, say, a reduction of over 30% that is allowed however uncommon.
- Info simply notifies the consumer and accepts the entry anyway.
The opposite factor price doing right here is writing helpful alert textual content. Excel’s default message is “This worth would not match the information validation restrictions outlined for this cell,” which tells no person something. Nonetheless, “Please enter a date after the venture begin date” is extra clarifying and truly helps.
The place I am taking my validation guidelines subsequent
Layering validation on high of dynamic arrays is the subsequent step
Information validation is not a whole alternative for conditional formatting — there’s nonetheless a spot for highlighting outliers that fall inside the guidelines however look uncommon. What it adjustments is the order of operations. The subsequent factor I wish to attempt is pairing validation guidelines with dynamic array formulation, so my supply lists for dropdowns increase on their very own as I add new classes or salespeople.
