Webbo3 Data Analysis Bootcamp · Data Cleaning Module · Lesson 12

Excel Data Validation: Rules, Dropdowns, Custom Formulas, and Checking Data Types with ISBLANK, ISNUMBER, and ISTEXT

A hands on lesson on stopping dirty data before it ever enters your sheet, using built in validation rules, dropdown lists, custom formulas, and the IS-functions that check exactly what kind of value you are dealing with.

Every cleaning tool in the last lesson, Remove Duplicates, Find and Replace, Flash Fill, fixes data that has already gone wrong. Data Validation works the opposite way: it stops bad data from ever getting typed in the first place. Where the last lesson was reactive, this lesson is preventive, and the two work best together. Validation does not replace cleaning, since pasted data and pre-existing entries bypass it entirely, but for anything typed fresh into a sheet, it is the single best defence against ever needing the cleaning tools at all.

1. Whole Number and Decimal Validation Rules

Select your cells, then go to Data tab → Data Tools group → Data Validation. On the Settings tab, the Allow dropdown controls everything. Choosing Whole Number restricts a cell to integers only, completely blocking decimals, text, and anything else. Choosing Decimal works identically but additionally permits fractional values. Either way, a second dropdown labelled Data then lets you pick a comparison type: between, not between, equal to, greater than, less than, and so on, with Minimum and Maximum fields to set your actual boundary values.

A real example. For a quantity field that should never be zero, negative, or fractional, set Allow to Whole Number, Data to "between," with a Minimum of 1 and a Maximum of 9999. Typing 2.5 or -3 or "five" into that cell now gets rejected outright, before it ever reaches a formula downstream that assumes a clean positive integer.

2. List Dropdowns for Controlled Data Entry

Choosing List in the Allow dropdown is the single most commonly used validation type, and for good reason: it is the most direct fix for the exact problem from the last lesson, where one column ends up with five different spellings of the same category. With List selected, the Source box accepts either a comma separated set of values typed directly, for example Sales,IT,HR,Finance, or a cell reference pointing to a range elsewhere on the sheet that holds your approved list, for example =$G$2:$G$6. Make sure the In-cell Dropdown checkbox stays ticked, or the validation rule applies without showing the actual dropdown arrow.

The genuinely smart version of this technique: source your list from an Excel Table instead of a plain range. If your approved list lives inside a real Excel Table rather than a bare range of cells, referencing it in the Source box with structured reference syntax, for example =DepartmentTable[Department], means the dropdown automatically grows or shrinks as rows are added to or removed from that table later. You never need to manually edit the validation rule again just because someone added a new department to the approved list. This single setup choice is the difference between a dropdown that needs maintenance forever and one that maintains itself.

3. Date Range Restrictions

Choosing Date in the Allow dropdown restricts entry to genuine dates falling within whatever bounds you set, using the same Data comparison dropdown, between, greater than, and so on, that Whole Number and Decimal use. You can type fixed dates directly into the Start Date and End Date fields, or reference a cell elsewhere on the sheet.

A genuinely useful trick: typing a formula directly into the Start Date or End Date box. Rather than hardcoding a fixed date that will eventually become outdated, type =TODAY() directly into the Start Date field. Excel evaluates that formula dynamically every single day, so a rule meant to block backdated entries, for example requiring every delivery date to be today or later, keeps working correctly forever with zero manual maintenance, rather than needing someone to remember to update a hardcoded date every month.

4. Custom Validation Formulas

When none of the built in options, Whole Number, Decimal, List, Date, Time, Text Length, fit the actual rule you need, choosing Custom in the Allow dropdown lets you write your own logical formula instead. The rule is simple: your formula must evaluate to TRUE for a valid entry and FALSE for an invalid one, exactly like the logical formulas covered in an earlier lesson. Type it into the Formula box beginning with an equals sign, the same way you would type any normal formula.

A real example: preventing duplicate entries in a list. =COUNTIF($A$2:$A$100,A2)=1 applied to cell A2 and copied down counts how many times the value about to be entered already appears in the full range. If the count comes back as exactly 1, meaning this entry is unique so far, the formula is TRUE and the entry is accepted. If the same value already exists elsewhere in that range, the count rises to 2 or more, the formula becomes FALSE, and the duplicate gets rejected on the spot.

A real example: enforcing a specific text pattern. If every product code must start with "PRD" and be exactly eight characters long, =AND(LEFT(A2,3)="PRD",LEN(A2)=8) combines two checks from earlier lessons, LEFT and LEN, joined with AND, so both conditions must hold true at once for the entry to pass.

The one rule that matters most when applying a custom formula to a whole range at once. When you select a range like A2:A100 and write a custom validation formula, that formula should always reference only the first cell in that selection, A2 in this case, never the full range. Excel automatically adjusts the reference for every other cell in the selection the same way a relative reference shifts when a regular formula is copied down, using exactly the logic covered in the earlier lesson on cell referencing. Writing the formula against the full range instead of just the first cell is one of the most common reasons a custom validation rule silently rejects every single entry, regardless of whether the data is actually valid.

5. Input Messages and Error Alerts

These live on the second and third tabs of the same Data Validation dialog, and they control how the rule actually communicates with whoever is typing.

Input Message is purely a guide, not an enforcement tool. Tick "Show input message when cell is selected," then add a Title and a Message, and that text appears as a small tooltip the moment someone clicks or hovers over the validated cell, before they have even typed anything. It cannot block an invalid entry on its own, its entire job is reducing confusion before a mistake happens, by telling the person exactly what is expected upfront.

Error Alert is where actual enforcement happens, and it comes in three distinct styles that behave very differently. Stop is the strictest: the invalid entry is rejected outright, the person must correct it or press Cancel, and there is no way to override it. Use Stop whenever wrong data would genuinely break a formula or a report built on top of it, for example a numeric field that downstream calculations depend on. Warning shows the message but also offers Yes, No, and Cancel buttons, letting the person push the entry through anyway if they click Yes. Use Warning for a rule that is a strong recommendation rather than an absolute requirement, for example flagging an unusually large number that might be a typo but could also be genuinely correct. Information simply displays the message and always lets the entry through regardless, useful purely for documenting an expected format without blocking anything at all.

A genuinely important limitation worth knowing before you trust validation as a complete safety net. Data Validation only checks new data that is typed directly into a cell after the rule was created. It does not check data that already existed in those cells before the rule was applied, and critically, it does not check data that gets pasted in. Someone can paste an entire column of invalid values straight past a validation rule with no warning at all. To find existing values that fail a current rule, use Data tab → Data Validation dropdown arrow → Circle Invalid Data, which draws a red circle around every cell currently violating the rule, regardless of when that value was entered.

6. ISBLANK, ISNUMBER, ISTEXT: Checking Data Type

Syntax: =ISBLANK(value), =ISNUMBER(value), =ISTEXT(value)

These three belong to a wider family called the IS functions, and every single one of them does the same kind of job: look at a value and answer one specific yes or no question about what it actually is, returning TRUE or FALSE. ISBLANK answers "is this cell genuinely empty." ISNUMBER answers "does this cell hold a real number." ISTEXT answers "does this cell hold text." All three appear constantly inside custom validation formulas, exactly the kind you just learned to write, and inside IF statements used to catch bad data before a calculation runs.

The genuine trap with ISBLANK: "blank" means truly empty, not merely invisible. A cell that visually looks empty but actually contains a single space character, or a formula elsewhere that returns an empty string, is not considered blank by ISBLANK, which returns FALSE for both. ISBLANK only returns TRUE for a cell that contains absolutely nothing at all. If you need to also catch a cell that merely looks empty because it holds a formula-generated empty string, test for a zero length instead, for example =LEN(A2)=0, which catches both a truly empty cell and a cell holding an empty string from a formula.

The genuine trap with ISNUMBER: it cares about the actual stored type, not how a value looks. ISNUMBER returns TRUE only for a real, stored numeric value. If a cell holds the digits 19 but they were typed or imported as text, deliberately wrapped in quotation marks the way many CSV exports do, =ISNUMBER("19") returns FALSE, even though 19 plainly looks like a number to the eye. This is the exact same numbers-stored-as-text problem from the last lesson, and ISNUMBER is the standard diagnostic check for catching it before it silently breaks a SUM formula.

A real combined use case for this bootcamp. Before running any calculation on a column you did not personally build, a quick health check like =IF(ISBLANK(A2),"Missing",IF(ISNUMBER(A2),"OK","Check: not a number")) copied down the column instantly flags exactly which rows are genuinely empty, which rows hold real numbers ready for calculation, and which rows hold something else entirely, most likely a number stored as text, that needs fixing before you trust any total built on top of it.

Using AI to Move Faster in Excel

Data validation is fundamentally about translating a business rule into a formula or a setting, which makes it one of the clearest places where describing the rule in plain language to Copilot can save real time, especially for custom formula rules that would otherwise require trial and error.

1. Describe the actual rule, let Copilot write the custom validation formula.
Instead of working out the exact logical formula yourself, describe the real constraint, for example "Make sure no value in this column is entered twice" or "Only allow product codes that start with PRD and are exactly 8 characters long." Copilot should generate the correct COUNTIF or LEFT-and-LEN combination directly, ready to paste into the Custom Formula box.

2. Ask Copilot to find existing data that would now fail a new rule.
Since validation never checks data that existed before the rule was created, and Circle Invalid Data is a manual, visual check, you can also ask directly: "Which cells in this column contain text instead of numbers" or "Are there any duplicate values already in this list." This is a faster first pass before deciding whether the Circle Invalid Data feature is even necessary.

3. Use AI to write the IS-function health check for an inherited dataset.
If you receive a workbook from someone else and want a quick diagnostic before trusting any of its numbers, ask Copilot to build something like the combined ISBLANK and ISNUMBER check covered in this lesson, tailored to your actual columns, rather than constructing the nested IF yourself from scratch each time.

4. Always test a validation rule against an obvious failure case before relying on it.
Whether you wrote a custom validation formula yourself or had Copilot generate it, deliberately type an entry you know should fail, an empty cell, a duplicate, a wrong length, into the validated cell before trusting the rule across a shared workbook. A validation rule that silently accepts everything because of a reference mistake, for example forgetting to lock the first cell of a range as this lesson covered, is worse than no rule at all, since it creates false confidence that bad data is being blocked when it is not.

A habit worth carrying forward from this lesson: whenever you are about to build a shared template, a data entry form, or any sheet other people will type into, pause before handing it over and ask which fields genuinely need a Stop alert because wrong data there would break something downstream, and which fields only need a Warning or an Input Message because some judgement should be allowed. That one decision, made deliberately for each field rather than defaulting to no validation at all, is what separates a spreadsheet that stays clean for months from one that needs the entire cleaning toolkit from the last lesson run on it every single week.

Next lesson: sorting, filtering, and conditional formatting for working with larger ranges of data.