4. Data validation and input controls in Excel

An NCAP rating calculator is only as trustworthy as the data fed into it. A single mistyped star rating, a head injury criterion (HIC) value entered in the wrong column, or a test date recorded as text instead of a date can silently propagate through the scoring formulas and produce a plausible-looking but wrong star rating. In a workbook that aggregates dozens of crash tests across frontal impact, side impact, pedestrian protection, and active safety categories, manual entry errors are not a matter of if but when. This chapter shows how to build a defensive input layer in Excel using data validation, conditional formatting, and structured error-checking, so that invalid test results are rejected or flagged at the moment of entry rather than discovered during a late-stage audit.

Why validation belongs at the entry boundary

The most effective place to catch a bad value is the cell where it is typed. Excel's Data Validation feature lets you constrain what a cell accepts: a list of allowed values, a numeric range, a date window, or a custom formula. When combined with conditional formatting, the same rules can also give immediate visual feedback on values that are technically allowed but suspicious. The goal is a layered defense: hard constraints block impossible values outright, while soft indicators highlight values that need a second look.

For an NCAP workbook, the input surface typically includes categorical fields (test type, body region, star rating, pass/fail), numeric fields (HIC, chest deflection in mm, femur force in kN, AIS severity), and date fields (test date, report date). Each field type calls for a different validation strategy.

Dropdown lists for categorical test fields

Categorical fields are the easiest to control and the most damaging when wrong. A test type entered as "Frontal Offset" in one row and "Frontal-Offset" in another will break any COUNTIF or lookup that expects a single canonical string. Use a dedicated reference sheet (for example, a sheet named Lists) to hold the allowed values, then point validation at those ranges.

Select the target cells, open Data > Data Validation, choose List as the allow type, and set the source to a named range such as =TestTypes. A named range is preferable to a hard-coded comma-separated list because it keeps the vocabulary in one place and makes it easy to extend. For the test type field, the list might contain:

Frontal Offset Deformable Barrier
Frontal Full Width
Side Mobile Barrier
Side Pole
Pedestrian Head
Pedestrian Leg
Active Safety - AEB
Active Safety - Lane Support

Two settings matter in practice. First, enable In-cell dropdown so users pick rather than type. Second, configure the Error Alert tab with the style set to Stop and a clear message such as "Select a test type from the list. Free text is not accepted." The Stop style prevents entry entirely; Warning and Information styles merely prompt. For canonical fields, Stop is the right choice.

For star ratings, a list of 0 through 5 is sufficient, but consider whether half-stars are permitted in your scoring scheme. If the rating is computed rather than entered, the field should not be an input at all — lock it and let the formula populate it. Mixing computed and manual star ratings in the same column is a common source of inconsistency.

Numeric range validation for physical measurements

Physical measurements need range constraints grounded in the test protocol, not arbitrary guesses. HIC, for instance, is a dimensionless value that in real crash tests rarely exceeds a few thousand; a value of 45,000 almost certainly indicates a unit or decimal error. Chest deflection is measured in millimetres and typically falls within a defined band depending on the dummy and impact severity.

Apply Decimal validation with a minimum and maximum derived from the protocol. For HIC, a defensible example range might be 0 to 5000, with an input message stating the expected unit and typical range. For chest deflection, 0 to 100 mm. These are illustrative bounds for a teaching workbook; in production, derive them from the actual test standard and dummy specification you are working to.

Range validation alone cannot catch every error. A HIC of 850 entered where 1850 was intended passes a 0–5000 range check. This is where conditional formatting adds a second layer: highlight values in the top decile of the observed distribution, or values that exceed a threshold of interest, in amber so a reviewer notices them. The formatting does not block entry — it invites scrutiny.

Date and text-length controls

Test dates should be constrained to a plausible window, for example between the start of the test programme and today. Use Date validation with a formula such as =AND(B2>=DATE(2020,1,1),B2<=TODAY()). This catches the classic error of a date entered in a different locale format (for example, 03/04 interpreted as March 4 versus April 3) when the resulting date falls outside the programme window.

For free-text fields such as test report references or laboratory names, use Text Length validation to enforce a maximum, and consider a custom formula that rejects leading or trailing spaces, which silently break exact-match lookups. A formula like =TRIM(B2)=B2 as a custom validation rule will reject values with stray whitespace.

Conditional formatting for cross-field consistency

Some of the most valuable checks are relational rather than per-cell. A pedestrian head test should not carry a chest deflection value; a side pole test should not have a frontal offset flag. These cross-field rules are best expressed as conditional formatting formulas applied to the whole input block.

For example, to flag rows where the test type is a pedestrian test but a chest deflection value is present, apply a formula rule to the chest deflection column:

=AND($C2="Pedestrian Head",LEN($F2)>0)

where column C holds the test type and column F holds chest deflection. Format the cell with an orange fill and a note in the legend. Similarly, flag any row where the pass/fail field is blank but all measurement fields are populated — a sign that the outcome was never recorded.

Error-checking with a validation summary panel

Cell-level validation catches entry errors, but a summary panel catches systemic problems. Build a small block of formulas that counts violations across the input table and displays a status. For instance, a cell can count rows where the test type is not in the allowed list using =SUMPRODUCT(--(COUNTIF(TestTypes,InputRange)=0)), and another can count blank required fields. Present these counts next to a single status cell that reads "Ready" only when all counts are zero.

The following diagram shows how the input layer, validation rules, and summary panel relate to one another in the workbook.

A worked example: catching a unit error

Suppose a technician enters a chest deflection of 3.2 for a side impact test, intending 32 mm but typing the value in centimetres. The decimal range 0–100 accepts 3.2, so validation does not block it. However, a conditional formatting rule that highlights values below 10 mm in amber — because such low deflections are unusual for a side impact — draws attention to the cell. The reviewer checks the source report, corrects the value to 32, and the amber highlight clears. This is the intended division of labour: validation stops the impossible, formatting surfaces the improbable, and the summary panel confirms that no violations remain before the workbook is released for scoring.

Practical checklist for the input layer

• Every categorical field uses a named-range list with a Stop-style error alert.
• Every numeric measurement has a protocol-derived minimum and maximum, with an input message stating the unit.
• Date fields are bounded by the test programme window and TODAY().
• Cross-field rules are expressed as conditional formatting formulas and documented in a legend.
• A summary panel counts violations and exposes a single READY / NOT READY status.
• Computed fields (star rating, overall score) are locked and never accept manual entry.

With the input layer in place, the workbook can be trusted to hold clean, canonical test results. The next stage — mapping those results to scoring formulas and star ratings — depends on this foundation, because a scoring engine fed inconsistent categories or out-of-range measurements will produce outputs that look authoritative while being quietly wrong.