Microsoft Excel · Data preparation · Beginner
Create a Data Validation Drop-Down List and Whole-Number Checks in Excel
Build input dropdowns and whole-number data validation checks in Excel; test a dropdown, quantity limits, blanks, pasted data and recovery.
Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards
Quick answer: Use Data → Data Validation with Allow set to List and an explicit source range. Add a Stop error alert if unlisted direct entries should be rejected.
Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.
In a blank worksheet, enter Status choices in D1, then New, In progress, Blocked and Done in D2:D5. Put Task in A1 and Status in B1; enter Draft, Review and Publish in A2:A4. Leave B2:B4 empty for the dropdowns. This ordinary fixed range is intentionally simpler than a table-based source.
Create the drop-down
- Select B2:B4, the cells that should contain the status.
- Choose Data → Data Validation.
- On Settings, set Allow to List.
- Set Source to
=$D$2:$D$5. Exclude D1 or the heading becomes an unwanted choice. - Keep In-cell dropdown enabled and confirm the rule.
- Open the cell’s drop-down and choose each allowed value.
Using a source in an Excel Table can make maintenance easier because Microsoft documents that drop-downs based on a table can update as items are added or removed.
Input and error messages
The Data Validation dialog can show an input message when the cell is selected and an error alert when an invalid value is entered. Test an unlisted value such as Waiting so you know whether the chosen settings block it or merely warn.
Troubleshooting
- Data Validation may be unavailable on a protected sheet or in some shared-workbook situations.
- A manually typed source must use the appropriate list separator for the environment.
- Do not hide the source range and then forget where it lives; document the source in a maintenance note or named range.
Reproducible maintenance test
The fixed address is easy to inspect later: select a target cell and reopen Data Validation to see which source cells supply its choices.
On Error Alert, enable the alert and choose Stop. Use a clear message such as “Choose a listed status.” Select New in B2 and Done in B3. Type Waiting directly in B4; the Stop alert should prevent acceptance of that unlisted entry. Cancel the invalid entry, then select Blocked instead. A Warning or Information alert is not equivalent to Stop because it permits an override.
Now change D4 from Blocked to Waiting. Reopen B4’s dropdown: Waiting should be available, but the old text already in B4 is not automatically rewritten. Choose Waiting explicitly. This separates maintenance of allowed choices from cleanup of existing records. Save as XLSX and reopen; inspect the source address and test a fresh direct entry. Treat validation as an entry aid, not a security or permission boundary, and review data after bulk pasting.
Add a whole-number quantity check
A list is not the only validation rule. This separate exercise keeps the status dropdowns in B2:B4 and their D2:D5 source intact. It covers whole numbers only, not date or custom-formula validation. The steps remain a Microsoft 365 Excel for Windows desktop documentation walkthrough, not a claim of free-web equivalence or an application test.
Set up the quantity cells
- In the same disposable worksheet, put
Quantityin C1. Leave C2:C3 empty and type9in C4 before adding the new rule. This deliberately seeds an existing invalid value. - Select C2:C4 and open Data → Data Validation. On Settings, set Allow → Whole number, Data → between, Minimum → 1 and Maximum → 8. Leave Ignore blank selected: these practice quantities may be unfinished.
- On Input Message, enable Show input message when cell is selected. Use the title
Quantityand messageType a whole number from 1 through 8; blank means unfinished. - On Error Alert, enable Show error alert after invalid data is entered, choose Stop, and enter
Use 1 through 8, with no fraction.Confirm with OK.
The rule settings come from Microsoft’s Apply data validation to cells, especially “Adding other types of data validation.” The input message explains the rule; it does not enforce it by itself.
Check direct entries and existing data separately
Select C2 and confirm that the input message describes quantities, not statuses. Type each value below directly into C2, reselecting C2 and pressing Enter for every attempt. For an invalid value, cancel the rejected entry before continuing; do not change the rule to get past the alert.
| Direct entry in C2 | Expected result with this Stop rule | Reason |
|---|---|---|
1 |
Accept | Lower endpoint is included. |
8 |
Accept | Upper endpoint is included. |
5 |
Accept | Whole number inside the range. |
0 |
Reject | Below the minimum. |
9 |
Reject | Above the maximum. |
2.5 |
Reject | Fraction, even though it lies between the limits. |
These are independently checked arithmetic expectations, not observed Excel results. Enter 5 once more so the final value in C2 is unambiguous. With a decimal-comma locale, type the local representation of two and a half for the fractional test.
Now inspect C4. Adding a rule does not automatically clean up or alert on its pre-existing 9. Select C2:C4 and use Data → Data Validation → Circle Invalid Data to review the existing entry; C4 should be identified as outside the rule. Replace C4’s value with 5 directly. Keep C3 blank for the next check. Microsoft’s More on data validation, under “Tips for working with data validation,” explains this distinction and the direct-entry limitation.
Save a clean XLSX practice copy with C2=5, C3 blank and C4=5; close and reopen it. Inspect C2:C4’s rule, type 5 in C2, then try 9 and cancel the rejected entry so C2 remains 5. Record your observations rather than treating the saved file as verified by this article.
Blanks and paste are different tests
Use a second, disposable copy of that clean workbook. Keep a small note for each test: action, resulting cell value, alert shown, rule still present.
- Blank: C3 is intentionally unfinished under the selected Ignore blank setting. Clear C2’s value and inspect it and its rule. Record what happens; this is not the numeric
0test. A validation rule is not a guarantee that every required field has been completed. Do not assume that clearing Ignore blank alone makes deletion impossible; check completeness separately before using the data. - Paste: Type
9in unused J1, copy J1 and paste it into C4. Record the value, any alert, and C4’s current Data Validation settings. Copied or filled data does not receive the normal direct-entry validation messages. Do not infer that an absent alert means the data is valid, or that a paste left the destination rule unchanged. - Restore: Discard the disposable test copy. Reopen the saved clean copy, check C2=
5, C3 blank, C4=5and the 1-through-8 rule, then retry direct entry of9and cancel it. This keeps recovery independent of what the paste changed. Leave the original status list and its source untouched.
Do not use validation as a security boundary. Review incoming data and required fields even when a workbook has Stop alerts.
Troubleshoot the quantity exercise
- A fraction is accepted: Inspect Allow in the actual target cell; Decimal is not Whole number. Also confirm that you typed the value rather than pasted it, and that the Stop alert is enabled.
- No quantity message appears: Check the Input Message checkbox and that you selected C2:C4, not the status cells or their source. An input message and an error alert are separate settings.
- C4 still contains 9 after the rule was added: That is the deliberately seeded existing-data case, not evidence that fresh direct entry should accept 9. Inspect the rule, review invalid data and correct the value.
- The command is unavailable: Finish or cancel the current cell edit. If protection or sharing prevents changes, use your own blank practice workbook or ask its owner; do not remove someone else’s protection or sharing controls.
- The interface differs: Stop at the platform boundary. These instructions do not establish Mac, web, Excel 2013, date-rule or custom-formula behavior.
Official references
- Create a drop-down list — Windows steps and list maintenance.
- Apply data validation to cells — whole-number limits, input messages and blank settings.
- More on data validation — alert styles, existing invalid data and copied/filled data limitations.
Original dropdown walkthrough dated 20 October 2026; the relevant Microsoft documentation was freshly reviewed on 28 October 2026 for this extension. These instructions and example checks are documentation-based. No native Excel execution, paid application use, save/reopen verification or application screenshot is claimed; the broader numeric/date/custom-formula topic is not fully covered.