Stop spreadsheet input mistakes with a few well-chosen rules

“Ready,” “complete” and “done” might mean the same thing to the people filling in a spreadsheet. A report counting rows marked “Ready” has a narrower view of the world.

Data validation lets you define what a cell should accept: a status from a list, a whole number in a sensible range or a date within the period you are planning. It catches some mistakes while someone is entering data. It does not establish that the information is true, and it needs a separate check for pasted or imported rows.

Start with the columns that cause actual rework. Here is a small register you can build in Excel, with a Google Sheets equivalent for the status list.

Give the sheet a clear job

This fictional equipment-request register tracks requests awaiting preparation. The names, quantities and dates are demonstration data.

Put these headings in A1:D1. In this example, each row represents one request for one equipment type; details live in the linked request record. Quantity means individual items, not packs. “Ready” means preparation is complete and the required checks have been recorded.

Writing those definitions is part of the fix. A dropdown cannot resolve two people using the same status to mean different things.

Separate three questions when choosing a rule:

The first two questions lend themselves to repeatable checks. The third needs the source. This helps you avoid spending an afternoon adding restrictions that still would not catch the mistake that caused last week's problem.

Work on a copy of your real workbook. The ranges below run from row 2 to row 100; extend both the rules and checks when your register grows.

1. Give status a small vocabulary

For B2:B100, use these values: New, Waiting, Ready, Closed. Keep an explanation of each beside the sheet or in its instructions.

In Excel, select the range, open Data → Data Validation, choose List, and enter New,Waiting,Ready,Closed as the source. Keep the in-cell dropdown enabled. On Error Alert, enable the alert and select Stop. Write a helpful message such as “Choose New, Waiting, Ready or Closed.” Microsoft's dropdown instructions explain these settings and the difference between stopping an invalid entry and warning about it.

Try typing “Complete” into a test row. The list is useful only if the behavior matches what your team expects. Decide whether a new status is actually needed before adding another synonym.

For Google Sheets, select B2:B100 and choose Data → Data validation → Add rule. Use a Dropdown with the same four options. Google's current dropdown guidance explains that unmatched entries are rejected unless you change the invalid-data behavior to show a warning. Keep one selection per cell for this register.

Those are native setup instructions. Do not assume moving an Excel workbook into Sheets preserves every rule exactly; check the converted sheet before using it.

Use a second field when you need a different kind of information. If Waiting can mean “customer has not confirmed” or “warehouse is checking stock,” keep Waiting as the status and record the reason beside the request or in its linked record. Making a separate status for every explanation produces a dropdown people have to study.

Decide what happens to old records when a definition changes. Renaming Ready to Packed may be harmless if those terms have always meant the same thing. It is misleading if older Ready rows also included items still awaiting checks. Review the affected rows before applying a new label across the history. The shared inbox guide uses the same approach to separating status from the reason work is waiting.

2. Catch implausible quantities and dates

For C2:C100 in Excel, choose Whole number, between, minimum 1, maximum 200. Use a Stop alert explaining that the example tracks individual items. The ceiling of 200 is this fictional business's review threshold, not a universal rule. A larger legitimate request should be reviewed and the rule changed deliberately if needed.

For D2:D100, choose Date, between, with =DATE(2026,9,1) and =DATE(2026,12,31) as the demonstration limits. Replace these with the period your actual register covers. Microsoft's data-validation reference covers whole-number, date and custom rules.

Display dates in an unambiguous format such as yyyy-mm-dd. Remember that changing a cell's display format does not prove that a pasted text value has become a valid spreadsheet date.

Avoid limits that punish normal work. If legitimate quantities keep getting rejected, investigate the unit definition or the chosen range. A rule everyone learns to remove is not helping much.

An error message should tell the person how to recover. “Invalid quantity” explains very little. “Enter individual items, 1–200; ask the coordinator to review larger requests” gives the rule and a route for legitimate exceptions. In your actual workbook, replace the example role with the person or queue that will respond.

Keep a distinction between an impossible value and an unusual one. A fractional number cannot represent a count of individual items in this register. A request for 201 items could be real. Both fail the example's entry rule, but they need different responses: correct the unit or value in the first case, review the business limit in the second.

3. Make duplicate request IDs visible

In Excel, select A2:A100 with A2 as the active cell. Under Data Validation → Custom, use:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)=1)

Use a Stop alert: “Enter a request ID that appears only once in this register.” Clear Ignore blank, but still audit missing IDs: validation is not a reliable substitute for checking incomplete rows.

The fixed range describes the whole ID column; A2 changes relative to each row. The formula asks whether the current ID is present and appears once. Microsoft documents COUNTIF's matching behavior, including that text matching ignores case. Keep IDs in one agreed format without extra spaces or wildcard characters such as * and ?.

For an independent check, put this formula in E2 and fill it down:

=IF(A2="","Missing ID",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate ID",""))

Treat E as a review column for rows that represent requests. Empty unused rows will say “Missing ID”; filter or limit your review to the active register. Formula separators can differ by spreadsheet locale, so your installation may use semicolons instead of commas.

4. Try to break the example

Use a practice copy and check these cases:

Test both typing and pasting. Microsoft explains that validation messages can be bypassed by copying, filling, formulas or macros. In supported desktop Excel versions, Data Validation → Circle Invalid Data can help identify values that fail the rules after entry.

Keep the original imported data when cleaning a batch. Correct it against the source, record unresolved items and review the result before using it for orders or reporting. A quantity of eight passes these rules even if the customer asked for six.

Bring a batch into the register without hiding the repairs

Use a separate staging copy when a colleague sends a block of rows or another system exports a file. The original tells you what arrived; the working copy tells you what you have changed. This is an operating method for your own register, not an additional sheet promised in the downloadable example.

First compare the headings and units with your register. A column named Quantity could contain boxes while yours expects individual items. Check whether identifiers retained their full form and whether dates represent the intended calendar dates. Resolve those structural differences before correcting individual cells.

Then review the same cases used above: accepted statuses, allowed quantities, dates in the planning period, duplicate IDs and active rows with missing IDs. Keep an exception list with the original value, proposed correction, source and person deciding. If “Complete” appears in an imported status column, do not automatically convert it to Ready until you know the source's definition of complete.

Reconcile the number of requests at the end. In a separate fictional import exercise, 12 source rows become ten accepted rows and two unresolved rows. Both unresolved rows must remain accounted for. Twelve source rows becoming ten rows with no explanation is data loss, even if all ten remaining rows pass validation.

The downloadable register contains a small practice exercise; it does not automate this import process. Use the workbook to understand the checks, then decide whether your real volume warrants a separate import routine.

Test the next row and the next change

Rules that cover A2:D100 leave the next row outside that stated scope. Before the register reaches its limit, extend the relevant validation and the duplicate-review range together, then test a new row. A larger input range with the old duplicate-check range would leave the new entries only partly checked.

Repeat a small set of tests after moving the workbook between products, changing the planning period or modifying the status list. Type a valid value, type an invalid value, paste an invalid value and add a new row. Inspect what is stored, not just whether a dropdown arrow appears. Our Google Sheets import note explains why preserving a table and preserving every behavior are separate checks.

Give someone responsibility for reviewing the rules when the business changes. Record the definitions and review routine in a short SOP alongside the workbook. The register should explain itself when its original author is away.

The worked Excel example contains a valid register, a practice sheet and an answer key. Use it to learn the behavior, then carry the few rules your own sheet needs into a copy of that workbook. Consistent inputs are worth having. A clear way to find the exceptions is worth having alongside them.