Column Harbor
Module 03: Build dependable worksheets
LESSON 10 / Intermediate · ABOUT 18 MIN

Guide data entry with dropdowns and number limits

Create a status list and a whole-number rule, then distinguish entry guidance from a complete data audit.

Before you start

Excel 2016, 2019, 2021, 2024, and Microsoft 365 for Windows desktop. These are desktop Data Validation steps; Mac and web dialogs can differ. Time is an estimate.

Useful first: Enter and correct data without changing its meaning · Turn an order range into a growing Excel table

By the end, you can…
  • Build a dropdown from a visible source range.
  • Reject a typed value outside an allowed integer range.
  • Test valid, invalid, existing, and pasted data separately.
YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic starting values only. Paste at A1, then create the source list and validation rules manually.

Download practice data
View the raw practice data
TaskID	Status	Quantity
T01	Open	2
T02	Hold	5
T03	Closed	1

01Define the allowed values before opening the dialog

Paste the three-task sample in A1:C4. In H1 enter AllowedStatus, then enter Open, Hold, and Closed in H2:H4. Keep this source list visible during practice.

The quantity rule permits integers from 1 through 10 inclusive. Zero does not mean missing here. Decide the business meaning first: a permitted number can still describe the wrong task.

CellsRule or values
H2:H4Open; Hold; Closed, one value per cell
B2:B10Status dropdown
C2:C10Whole numbers from 1 to 10

02Connect the status cells to the source list

Select B2:B10 and choose Data > Data Validation. Under Allow select List, set Source to =$H$2:$H$4, and enable In-cell dropdown. Exclude H1: the heading AllowedStatus is not a status. Using a range avoids typing a delimiter-dependent list into the dialog.

On Error Alert, enable the alert and select Stop. Use a message such as Choose Open, Hold, or Closed. On Input Message, explain what the field means. This exercise allows unfinished blank cells while entering tasks; a later completeness review is still necessary.

03Limit quantities and understand the boundary

Select C2:C10, reopen Data Validation, and choose Whole number, between, Minimum 1, Maximum 10. Enable a Stop alert. Directly typing 11 or 2.5 should be rejected, while 1 and 10 are permitted. Test a cell with the actual rule rather than assuming every row received it.

Validation is an entry aid, not proof that the complete dataset is correct. Existing values can remain after a rule is applied, and copying or pasting can bypass or replace validation. Review imported and pasted data independently. Stop blocks an invalid direct entry; Warning or Information alerts can allow a user to continue.

  • Keep headings outside the validated input range.
  • Check the rule again after copying a template to a new area.
  • Use XLSX to retain validation; a CSV export carries cell values, not these rules.
PUT IT INTO PRACTICE

Complete a new task and verify both acceptance and rejection paths.

  1. Enter T04 in A5, choose Hold from B5's dropdown, and type 10 in C5.
  2. Try typing Paused in B5 and 11 in C5. Cancel each rejected entry so Hold and 10 remain.
  3. On a disposable copy, paste 11 into a validated quantity cell and inspect both the value and its validation settings. Treat any bypass or replaced rule as a reason to audit pasted data, not as an acceptable quantity.
Show the worked answer

The valid new record is T04 / Hold / 10.

Paused and 11 fail the intended direct-entry rules. Paste behavior must be checked in the Excel build used; a successful paste is not a validation pass.

Check your work
  • B5's list contains exactly Open, Hold, and Closed.
  • C5 uses Whole number between 1 and 10 with a Stop alert.
  • The source cells H2:H4 and all three original task records remain intact.
CHECK YOUR UNDERSTANDING

One question before you move on.

A worksheet already had Quantity 15 before you applied a 1–10 rule. What should you conclude?

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.
NEXT LESSONClean hidden spaces without losing the original text

Keep the skill close.

FIELD GUIDECSV vs XLSX: what you lose when saving a spreadsheet as CSVWORKSPACE TOOLCSV import checker

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.