Professional Workflow›Extra· 30 min read

Data Validation & Drop-Down Lists

Data validation stops bad data at the door — force a cell to accept only valid numbers, dates, or items from a tidy drop-down list.

What you will learn

Explain why validation prevents errors
Create a drop-down list in a cell
Restrict a cell to a number or date range

Stop mistakes before they happen

It is far easier to prevent bad data than to clean it up later. Data validation sets a rule on a cell that controls what may be typed into it. If someone tries to enter something against the rule, Excel rejects it with a polite message. Two everyday uses: a drop-down list of allowed choices, and a number or date range limit.

You set all of this from Data > Data Validation, then choose what to Allow.

A drop-down list of choices

A drop-down means the user does not type at all — they pick from a list, so spelling is always perfect and consistent. This is the single best fix for messy categories like North / north / Norht. Here is how to build one:

Select the cell (or cells) that should have the drop-down.
Go to Data > Data Validation.
Under Allow, choose List.
In the Source box, type the choices separated by commas: North,South,East,West — or point to a range that holds them.
Press OK. A little arrow now appears on the cell.

Create a drop-down so the cell only accepts one of four regions

Data  >  Data Validation  >  Allow: List  >  Source: North,South,East,West

Note: Output: Clicking the cell shows a small arrow; clicking it lists North, South, East and West. The user picks one and it fills the cell. Typing “Souht” by mistake is rejected, so every region in the column is spelled identically — and your pivots and SUMIFS will group them correctly.

Tip: For a list that may change, type the choices into a column (perhaps on a hidden “Lists” sheet), make it an Excel Table or named range, and point Source at that range. Add a new choice to the column and the drop-down updates automatically.

Limit a number or a date

Validation also guards numbers and dates. Suppose a quantity must be a whole number from 1 to 100. Choose Allow: Whole number, then between 1 and 100:

Reject any quantity that is not a whole number from 1 to 100

Data  >  Data Validation  >  Allow: Whole number  >  between 1 and 100

Note: Output: Typing 50 is accepted. Typing 250 (too big), 0 (too small) or 4.5 (not whole) is rejected with a warning, and the cell keeps its previous valid value. Bad quantities never make it into your data in the first place.

Add a helpful message

Two tabs in the Data Validation box make the rule friendlier:

The Input Message tab shows a little tooltip when the cell is selected, like “Pick a region from the list.”
The Error Alert tab lets you write the message shown when an entry is rejected, like “Please choose North, South, East or West.”

Watch out: Validation only checks values typed in after the rule is set, and it can be bypassed by pasting over a cell. So validation reduces errors a lot but is not bullet-proof — still sanity-check imported or pasted data.

Q. What is the main benefit of a data-validation drop-down list for a Region column?

Answer: A drop-down restricts entries to a fixed set of valid, consistently-spelled values, which prevents typos and makes later grouping (pivots, SUMIFS) reliable.

✍️ Practice

Add a drop-down list to a Region cell with the choices North, South, East and West.
Set a Quantity cell to accept only whole numbers between 1 and 100, then try to enter 250 and see it rejected.

🏠 Homework

Make a small order-entry sheet where Region uses a drop-down list, Quantity is limited to 1–100, and the Order Date must be a date this year. Write a short input message for each.