Skip to content

Data Validation & Analysis

Data Validation Rules

Data validation protects your models from invalid entries. Located in the Data Ribbon.

Mechanics

  • Input Message Tab: Displays helpful tooltips/instructions when a user selects the cell.
  • Error Alert Tab: Blocks invalid input and displays a custom warning message.
  • Efficiency Pro-Tip: Write your validation rule in the first cell, copy it, select your target range, and use Paste Special > Validation (Alt + A + V + Enter).
  • Percentages Entry: Always evaluate rules using decimals (e.g., 0.25 for 25%, 0.35 for 35%) to maintain calculations.

Production Logic Examples

Example A: Exact Alpha-Numeric Length Control (Cell E7)

Allows exactly 7 characters: the first 3 must be "STU" (case-sensitive) and the remaining 4 must be numerical (e.g., STU7890).

=AND(LEN(E7)=7, EXACT(LEFT(E7,3),"STU"), ISNUMBER(VALUE(RIGHT(E7,4))))

Example B: Public Domain Email Enforcement (Cell B8)

Restricts entries strictly to major public domain email addresses using a case-insensitive search.

=OR(ISNUMBER(SEARCH("@gmail.com",B8)), ISNUMBER(SEARCH("@yahoo.com",B8)), ISNUMBER(SEARCH("@outlook.com",B8)))

Example C: Dynamic Live Date Validation (Cell F7)

Forces inputs to match the current chronological calendar date dynamically.

  • Allow: Date
  • Data: equal to
  • Formula:
=TODAY()

Analytical Structuring: Sorting, Filtering, & View Controls

View Controls

  • Freeze Panes: View Ribbon > Freeze Panes. Keeps rows or columns visible while scrolling.
  • Merge & Center: Home Ribbon > Merge & Center. Combines empty header rows for clean corporate reporting layout structures.

Standard Filtering & Sorting

  • Apply Filters: Select data table (Ctrl + A) and use shortcut Ctrl + Shift + L.
  • Date Filters: Use the dropdown arrows over headers to select relative periods like Between or specific operational dates.
  • Sorting Shortcut: Alt + S + H (requires an active cell selected within the dataset rather than highlighting headers).
  • Operational Sequence: Always apply Filters first, then Sort. For complex datasets, use Custom Sort and add hierarchical levels to prevent data mismatching.

Advanced Filtering

Used to extract specific records and paste them to entirely different sections or worksheets. * Single-Sheet Criteria: Data Ribbon > Advanced Filter. Define the source data and criteria range. * Multi-Sheet Extraction: Go to the destination sheet first, open the Advanced Filter tool, select your source data from the other sheet, define your criteria, and check Copy to another location.

Print Settings & Page Optimization

Ensure large data sheets remain legible when exported to PDF or paper: * Global Scaling: Open File > Print. Set orientation to Landscape and choose Fit Sheet on One Page using standard A4 paper formatting. * Print Area Isolation: Highlight a specific range, go to Page Layout > Print Area > Set Print Area. Alternatively, choose Print Selection in the main print setup page. * Page Breaks: Use Page Break insertions to cleanly divide distinct tables across separate sheets. * Print Titles: For multi-page tables, select Page Layout > Print Titles to repeat header rows at the top of every printed page.