Validate spreadsheet inputs with business rules

Spreadsheet validation should express the business contract for a field, not merely restrict typing to something that looks plausible. A quantity may need to be a positive integer; a discount may need to stay within gross line value; a status may need to come from an approved list.

Use input guidance to reduce mistakes and independent checks to detect invalid data after import, paste or refresh. A drop-down alone does not establish the quality of the complete dataset.

Define the exercise's valid population

The spreadsheet quality lab contains ordinary invoice lines, not return lines. Quantity must be a positive integer, UnitPricePaise a nonnegative integer and DiscountPaise an integer between zero and Quantity × UnitPricePaise. Status must be Paid or Pending for this fixture.

CustomerID and LineID are required text identifiers. A clean LineID must be unique, while InvoiceID may repeat across legitimate lines. Dates must be valid and inside the accepted extract period for this particular monthly workflow.

These are teaching rules, not universal invoice rules. A business supporting returns, credit notes or fractional quantities needs a different contract rather than forcing legitimate transactions through unsuitable restrictions.

Add a visible row-level result

On the Sales table, a quantity helper can make the numeric rule inspectable:

=IF(ISNUMBER([@Quantity]),AND([@Quantity]>0,[@Quantity]=INT([@Quantity])),FALSE)

This distinguishes a number from text and tests both positivity and integer value. Create equivalent explicit checks for unit price and discount before combining them into a final row status.

For a numeric, validated quantity and price, the discount relationship is:

=AND([@DiscountPaise]>=0,[@DiscountPaise]<=[@Quantity]*[@UnitPricePaise],[@DiscountPaise]=INT([@DiscountPaise]))

Only apply this after numeric-type checks or guard it appropriately. A business-rule formula that throws an error on malformed text needs an explicit invalid-input path; it should not be mistaken for a completed validation result.

Use input controls as the first layer

Configure whole-number restrictions for quantity where appropriate and a controlled list for status. Explain the allowed values in input messages. Keep the approved status list in a visible reference table so reviewers can see the vocabulary.

Then test pasted and imported values separately. Excel paste operations can transfer content and validation settings according to the paste option; they are not equivalent to individually typing values into a protected input flow. Microsoft's paste-options documentation.

The acceptance decision should depend on the independent row checks after data arrives, not only on whether Excel showed an input warning.

Check relationships across rows and tables

LineID uniqueness requires a dataset-level check. Customer existence requires comparison with a reference table. Customer 0042's conflicting regions require an ambiguity check even though the identifier itself is well formed.

Separate these outcomes in an exception report. “Invalid quantity,” “duplicate line key,” “missing customer” and “ambiguous customer” suggest different investigations. A single red cell or generic “bad data” label provides little help to the person resolving the issue.

Do not reject a valid invoice line merely because its category is unmapped unless the intended report requires complete category attribution. A provisional overall total may be possible while the category report remains blocked.

Make validation reviewable

For each rule, record its description, scope, severity, expected valid examples and failing examples. Decide which failures block publication and which require a visible exception note.

Use a small mutation set: quantity zero, quantity 1.5, negative discount, discount above gross value, unexpected status and repeated LineID with a changed amount. The clean fixture should pass its row arithmetic rules, while each mutation should fail the corresponding check.

The generated workbook provides the data and reference arithmetic. It does not claim that Excel input dialogs or formula results have been interactively verified; perform those checks in your target version.

Exercise: introduce credit-note transactions. Rewrite the quantity and amount rules so legitimate credits pass while contradictory signs or missing original-invoice references remain visible. Explain why simply allowing every negative number would be too broad.

NeuraPath's Data Analytics with Generative AI course develops spreadsheet skills tied to real reporting contracts. Effective validation explains both what the rule permits and how a failed record should be handled.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Excel date serials and imported timestamps explained.
• Continue with Reconcile two Excel lists with unmatched-record reports.