Use spreadsheet data validation to prevent common mistakes

Decide what a correct entry means

A spreadsheet can add a column perfectly while accepting the wrong inputs. A quantity of 500 instead of 50 still produces a plausible total. “Waiting,” “Pending” and “Not started” may describe the same status to a colleague but become three categories in a report. Data validation helps by checking an entry against a rule when someone types it.

This workshop uses Excel for Microsoft 365 on Windows, with English interface labels. Work in a separate practice workbook or a saved copy of your file. The examples below are hypothetical; they describe expected outcomes to check, rather than results from a live test. You need permission to edit the worksheet. No add-ins or account-setting changes are required.

Imagine a small equipment-request sheet. Each row needs an item, a quantity and a status. Before opening a dialog, write down the business rule: one request can contain 1–50 identical units, and its status must be Pending, Approved or Ordered. Those limits are invented for this exercise. A real sheet needs limits agreed with the people who use it.

Put Item, Quantity and Status in row 1. Use only rows 2–21 for now. Keeping the header outside the rule avoids treating a column title as an invalid entry. Restricting the example to a visible block also makes it easier to check whether every intended row received the settings.

Give status entries a shared vocabulary

A dropdown works best when its choices have clear meanings. For this example, Pending means awaiting a decision; Approved means authorized but not yet ordered; Ordered means the order has been placed. Avoid mixing a progress state with a person’s name or a priority level. Those belong in separate columns if you need them.

1. Enter Pending, Approved and Ordered in H2:H4, one per cell. Leave H1 as a descriptive heading, such as Status choices.
2. Select C2:C21. Open Data > Data Validation. On Settings, choose List under Allow.
3. Set Source to =$H$2:$H$4. Enable In-cell dropdown. Leave Ignore blank selected for this practice sheet.
4. On Error Alert, enable Show error alert after invalid data is entered and choose Stop. Write a useful message, such as “Choose Pending, Approved or Ordered.” Select OK.

Select C2 and open its dropdown: the three choices should appear, without the heading. Type an unrelated word to check rejection. Warning and Information let a person accept an invalid entry; use Stop when the choices are mandatory. These controls are documented in Microsoft’s dropdown instructions.

The source cells are part of your sheet’s design. Keep them visibly labelled so a colleague does not mistake them for spare scratch space. This fixed three-cell source is deliberately simple: putting a fourth choice in H5 is not a complete update. The rule’s source must include that new cell before people can select it.

Reject quantities that do not fit the job

1. Select B2:B21 and reopen Data > Data Validation.
2. On Settings, set Allow to Whole number, Data to between, Minimum to 1 and Maximum to 50.
3. On Input Message, enable the message and enter “Enter 1–50 units per request.” On Error Alert, enable the alert, select Stop and explain the same limit. Select OK.

In a practice row, type 1, then 50: both should be accepted. Try 0, 51 and 2.5: each should trigger the alert. With a Stop alert, choose Retry to correct the input or Cancel to abandon that attempted entry. Microsoft documents the rule types in Apply data validation to cells.

These are boundary checks: the exact limits matter as much as a wildly wrong value. If 50 is rejected, inspect the condition and maximum before weakening the rule. If 2.5 is accepted, check that you selected Whole number rather than Decimal and that the cell is inside B2:B21.

For a hypothetical request for 12 units at $8 each, the intended total is $96. Mistyping 120 would produce $960, and the maximum catches it. Mistyping 21 produces $168 and still passes. Validation checks the agreed shape and range; it cannot determine what the requester meant. Compare significant requests with their original instructions.

Check the data that rules did not stop

Adding validation does not automatically flag every unsuitable value already in a sheet. Copying or filling cells can also bypass entry alerts. A successful typed-input check therefore does not prove an imported or pasted block is clean. Microsoft describes these limitations in More on data validation.

After adding rules or receiving pasted data, select the relevant cells. Open the arrow beside Data > Data Validation and choose Circle Invalid Data. Excel marks values that break their cells’ rules with red circles. Correct an entry and its circle disappears. Clear Validation Circles hides the markings without fixing the values. See Microsoft’s validation-circle instructions.

Also inspect a destination cell’s Settings after pasting: an audit can only evaluate rules that still exist. If its expected restriction is missing, reapply the appropriate rule across that column’s intended input block, then repeat the audit. Do not apply the quantity rule to the status column simply because both lost their settings.

Blank entries need a separate completion check. This exercise allows empty cells while requests are being drafted. Before treating a row as ready, check that its item, quantity and status are all present. An empty future row and an unfinished active request have different meanings; decide which rows are active before calling a sheet complete.

Repair the rule before removing it

If Data Validation is unavailable, finish the current edit with Enter or cancel it with Esc. A protected worksheet can also prevent rule changes; ask its owner to make the intended adjustment. Microsoft documents these causes in its troubleshooting guidance.

If a legitimate request exceeds 50 units, establish whether it is an exception or a changed policy. Record the decision before changing the maximum. When extending the sheet beyond row 21, explicitly apply the appropriate rules to the new input cells and repeat the boundary checks there. A neatly formatted new row is not evidence that it received validation.

To deliberately remove a rule, select only the intended cells, open Data Validation and choose Settings > Clear All > OK. This removes validation rather than repairing invalid entries. Keep the saved copy until you have checked the revised sheet; restoring that copy also discards later edits. For routine handoff, leave a short note naming the status source, quantity limits and rows covered so the next editor can maintain the same contract.