ExamFinch.com My Study

Study guide

5.2 Data Validation Criteria & Custom Formula Rules

Practice this exam

Microsoft Excel Expert Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview & MO-211 Fundamentals

1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture

2 Chapter 2: Managing Workbooks & File Options

2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution

3 Chapter 3: Workbook Protection & Collaboration Settings

3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations

4 Chapter 4: Advanced Data Entry & Series

4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)

5 Chapter 5: Custom Formatting & Data Validation

5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing

6 Chapter 6: Data Grouping, Summarization, & Cleansing

6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing

7 Chapter 7: Advanced Conditional Formatting & Filtering

7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges

8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions

8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS

9 Chapter 9: Advanced Lookup Functions & Variable Optimization

9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET

10 Chapter 10: Date, Time, Text, & Auditing Functions

10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools

11 Chapter 11: Dynamic Array Functions & Spill Operations

11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping

12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions

12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic

13 Chapter 13: Macro Automation & VBA Fundamentals

13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor

14 Chapter 14: Advanced & Specialized Visualizations

14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts

15 Chapter 15: PivotTable Construction & Field Configuration

15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences

16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts

16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts

5.2 Data Validation Criteria & Custom Formula Rules

Key Takeaways Explain This Topic AI

Excel Data Validation enforces cell entry constraints across eight distinct criteria types, including Whole number, Decimal, List, Date, Time, Text length, Any value, and Custom.

List validation can be populated via comma-delimited inline literals or range references; dynamic cascading dropdowns utilize the INDIRECT function to reference named ranges matching prior selections.

Custom validation formulas must evaluate to a logical TRUE or FALSE; formulas must use relative referencing for the active input cell and absolute referencing for fixed lookup arrays or threshold ranges.

Data Validation is client-side and input-focused; pasting data via the clipboard (Ctrl+V) overwrites cell formatting and bypasses validation rules entirely unless audited retroactively.

Last updated: September 2026

5.2 Data Validation Criteria & Custom Formula Rules

Data integrity is fundamental to institutional spreadsheet design. Without input governance, user errors such as typos, out-of-range dates, negative inventory counts, or duplicated transaction IDs propagate through downstream formulas, destabilizing financial models and reporting dashboards. Excel's Data Validation engine provides automated input verification, constraining data entry at the point of ingestion.

To configure Data Validation, navigate to the Data tab on the ribbon, locate the Data Tools group, and click Data Validation (or use the keyboard sequence Alt + A + V + V ). The dialog organizes controls across three tabs: Settings , Input Message , and Error Alert .

Core Validation Criteria Types & Comparison Operators

The Settings tab defines the validation parameters. By default, every cell in a worksheet is set to Any value , meaning no validation occurs. Opening the Allow dropdown reveals seven restrictive criteria types:

  • Validation Criteria Allowed Input Data Common Business Applications
  • Whole number Integer values without decimal fractions Headcount, inventory counts, product quantities

Decimal Continuous numeric values including fractional decimals Hourly wages, interest rates, financial percentages

  • List Restricted items chosen from an inline list or range Department codes, country names, project statuses
  • Date Valid calendar dates between defined thresholds Fiscal year transactions, project milestones
  • Time Valid timestamps between defined intervals Employee shifts, delivery windows

Text length String character counts within specific lengths Fixed-length account IDs, postal codes, state abbreviations

Custom Formula-driven boolean expressions returning TRUE / FALSE Unique IDs, conditional rules, non-standard text masks

When selecting Whole number, Decimal, Date, Time, or Text length, Excel enables the Data operator dropdown, offering relational operators: between , not between , equal to , not equal to , greater than , less than , greater than or equal to , and less than or equal to . For dynamic boundaries, the Minimum and Maximum input boxes can reference cell addresses (e.g., =$G$2 ) or formulas (e.g., =TODAY() ).

Two checkboxes on the Settings tab govern behavior:

Ignore blank : When checked, blank or null cells pass validation without triggering error alerts. Unchecking this option enforces mandatory data entry if a dependent formula evaluates blanks.

In-cell dropdown : Available exclusively under the List criterion; controls whether a clickable down-arrow glyph appears beside the active cell.

Configuring List Validation & Cascading Dropdowns

The List criterion is Excel's most widely implemented validation rule. List sources can be populated using three distinct techniques:

Inline Literals : Enter comma-separated values directly into the Source input box: North,South,East,West Do not enclose items in quotation marks. Note that any space after a comma becomes part of the list item text.

Worksheet Range References : Reference contiguous cells containing valid entries: =$E$2:$E$10 Always use absolute references ( $ ) so the target list does not shift when validation is copied across rows.

  • Defined Named Ranges : Assign a descriptive name to a range and reference it with an equals sign: =RegionList
  • Cascading (Dependent) Dropdown Lists

Enterprise forms often require secondary dropdowns to update dynamically based on a primary selection (e.g., selecting a Region in cell A2 restricts cell B2 to departments within that region).

To build cascading lists:

Create named ranges whose defined names match the exact items in the primary list (e.g., named ranges North , South , East , West ).

Set the primary cell's validation to =RegionList .

In secondary cell B2 , set Data Validation to List and enter the INDIRECT formula: =INDIRECT($A2) The INDIRECT function converts the text string selected in cell A2 into an active range reference, dynamically loading the corresponding subcategory items into B2 .

Custom Formula Validation: Logic & Reference Anchoring

When pre-configured numeric or list rules cannot satisfy complex business requirements, the Custom option empowers analysts to author formula-driven rules.

The Governing Rules of Custom Validation Formulas:

The formula must evaluate to a logical boolean: TRUE permits the entry , while FALSE rejects the entry .

If a custom formula generates an error ( #N/A , #VALUE! , #REF! ), Excel treats the result as FALSE and blocks the input.

The Active Cell Rule : When applying a validation rule to a multi-cell selection (e.g., A2:A100 ), write the formula relative to the active cell (the white cell in the selection, typically the top-left cell A2 ). Excel automatically transposes relative row and column coordinates for every other cell in the range. Common Custom Validation Formulas:

  • ┌──────────────────────────────────────┬────────────────────────────────────────────────────────┐
  • │ Business Requirement │ Validation Formula (Applied to Range starting at A2) │
  • ├──────────────────────────────────────┼────────────────────────────────────────────────────────┤
  • │ Prevent Duplicate Entries │ =COUNTIF($A$2:$A$100, A2)<=1 │
  • │ Restrict Input to Numbers Only │ =ISNUMBER(A2) │
  • │ Restrict Input to Text Characters │ =ISTEXT(A2) │
  • │ Enforce Future Dates Only │ =A2>=TODAY() │
  • │ Enforce Uppercase Text Only │ =EXACT(A2, UPPER(A2)) │
  • │ Prohibit Leading/Trailing Spaces │ =A2=TRIM(A2) │
  • │ Require Valid Email Domain │ =AND(ISNUMBER(FIND("@", A2)), ISNUMBER(FIND(".", A2))) │
  • └──────────────────────────────────────┴────────────────────────────────────────────────────────┘

Exam Pitfall: In =COUNTIF($A$2:$A$100, A2)<=1 , failing to lock the range with dollar signs ( $A$2:$A$100 ) causes the comparison range to drift downward as rows advance. By row 10, Excel would check A10:A108 , allowing earlier duplicates to slip through undetected.

Clipboard Bypass Vectors & Validation Limitations

Data Validation intercepts keyboard entry and direct cell edits, but candidates must recognize its structural vulnerabilities:

Clipboard Paste ( Ctrl+V ) : Pasting data from an external source or unvalidated cell overwrites both the cell content and its underlying Data Validation settings. The validation rule is eradicated, and invalid data enters undetected.

Fill Handle & Drag-and-Drop : Dragging a cell across validated cells can copy source formatting and overwrite validation rules unless the fill option is explicitly set to "Fill Without Formatting".

The "Ignore blank" Nuance : If "Ignore blank" is enabled and a custom validation formula references an empty cell, Excel may bypass formula evaluation entirely, treating the empty cell as inherently valid.