5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing

5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing

Data Validation reaches its full potential when paired with effective communication and auditing tools. Preventing data corruption requires not only establishing logical boundaries, but also guiding end-users proactively, managing non-compliant entry attempts gracefully, and retroactively identifying violations in existing records. Excel addresses these operational demands through Input Messages, Error Alerts, and the Circle Invalid Data auditing engine.

---

Input Messages: Proactive User Guidance

Rather than forcing users to guess acceptable formats and learn through rejection, Excel allows spreadsheet designers to display proactive instructions via the Input Message tab of the Data Validation dialog (Data > Data Tools > Data Validation).

When the checkbox Show input message when cell is selected is enabled, Excel displays a lightweight, floating yellow callout box immediately adjacent to the active cell as soon as a user selects it.

+------------------------------------+
| Employee ID Input Tip             |
| Enter a 6-digit corporate ID       |
| starting with EMP- (e.g., EMP-104) |
+------------------------------------+
       ▲
       │
  [ A2: Active Cell ]

• Title: Up to 32 characters; rendered in bold text at the top of the callout banner.
• Input message: Up to 255 characters; provides explicit instructions regarding permissible data formats, acceptable numeric ranges, or case requirements.

Key Behavioral Differences from Notes and Threaded Comments:

• Selection-Driven: Input messages appear only while the cell holds active selection focus. As soon as the cursor moves to another cell, the message disappears automatically.
• No Visual Artifacts: Unlike legacy Notes (red corner triangles) or Threaded Comments (purple indicators), cells with input messages display no visible indicators when unselected.
• Non-Printing: Input messages do not appear on printed worksheets or exported PDF reports.

---

The Three Error Alert Styles: Behavioral Architecture

The Error Alert tab controls Excel's response when a user enters data that violates configured validation parameters. If Show error alert after invalid data is entered is enabled, Excel suppresses its generic warning and displays a custom modal dialog containing a specified Title and Error message.

Excel provides three distinct Error Alert styles, each representing a different degree of operational enforcement:

Deep Dive into Alert Button Behaviors:

1. Stop (Strict Rejection): Designed for mission-critical boundaries where invalid data cannot be tolerated under any circumstance.

   • Retry: Keeps the invalid text highlighted inside the cell and places the cursor into active edit mode, forcing the user to correct the input.
   • Cancel: Discards the entered value immediately, reverting the cell to its previous content (or blank). Pressing Esc executes the Cancel action.

2. Warning (Advisory Exception): Employed when business rules have legitimate exceptions (e.g., a purchase order exceeding standard credit limits that a supervisor may authorize).

   • Yes: Overrides the validation rule and commits the non-compliant data directly to the worksheet cell.
   • No: Keeps the entered value in edit mode so the user can modify it.
   • Cancel: Discards the invalid entry and restores the previous cell state.

3. Information (Notification): Used strictly for user awareness where data tracking or logging is preferred without imposing restrictions.

   • OK: Accepts and commits the non-compliant value into the cell immediately.
   • Cancel: Discards the invalid entry and restores the previous cell state.

Exam Scenario: Pay close attention to task instructions on the MO-211 exam. If a prompt states: "Configure validation to notify users when cost exceeds $5,000 but allow them to proceed," selecting the default Stop alert causes task failure. You must explicitly select Warning or Information to satisfy the override requirement.

---

Auditing Existing Data: Circle Invalid Data Mechanics

Data Validation rules evaluate entries only at the moment they are typed and confirmed. They do not retroactively validate existing cell contents. Consequently, non-compliant data can reside inside validated ranges under four common conditions:

1. Data was already present in the cells before the Data Validation rule was created.
2. Data was pasted into the range via clipboard commands (Ctrl+V).
3. Values were generated dynamically by formulas or external macro scripts.
4. Users overrode a Warning alert (clicking "Yes") or Information alert (clicking "OK").

To detect and audit these non-compliant entries, Excel provides the Circle Invalid Data utility:

$\text{Data} \longrightarrow \text{Data Tools} \longrightarrow \text{Data Validation (dropdown arrow)} \longrightarrow \text{Circle Invalid Data}$

Audited Worksheet Grid:
┌──────┬────────────────────────┬──────────┐
│ ID   │ Department             │ Spend    │
├──────┼────────────────────────┼──────────┤
│ 101  │ Operations             │  $4,200  │
│ 102  │╭──────────────────────╮│  $8,900  │  ◄── Red circle flags "Marketing"
│      ││ Marketing [Invalid]  ││          │      when only Sales/Ops permitted
│ 103  │╰──────────────────────╯│  $3,100  │
└──────┴────────────────────────┴──────────┘

Operational Rules of Validation Circles:

• Excel scans the worksheet and draws high-visibility red oval vector shapes around every cell containing data that violates its active validation criteria.
• Worksheet Limit: Excel can display a maximum of 10,000 validation circles per worksheet. If more violations exist, only the first 10,000 are circled.
• Non-Persistent Graphics: Validation circles do not print on paper or PDF exports.
• Removal: Circles can be cleared at any time by selecting Clear Validation Circles from the Data Validation dropdown menu. Modifying a circled cell to a compliant value removes its circle automatically upon recalculation.