Keith’s Training Hub

LESSON 9 OF 16 · ABOUT 20 MINUTES

Make a safe dropdown with data validation

Allow only Open or Closed as typed status values, then test the rule.

Need the practice files?

Keep using your working copy if you have started the programme.

Practice workbook (.xlsx)

Learn the skill

Consistent labels make counts and filters reliable. Validation reduces mistakes but does not prove all data is valid.

Worked example

The approved exercise labels are Open and Closed.

Apply a List rule to Orders!E2:E9 with source Open,Closed and a Stop error alert.

Typing Pending should be rejected. Existing and pasted values need separate checks.

Follow these steps

  1. Save a separate practice copy. Select E2:E9.
  2. Choose Data > Data Validation. Set Allow to List.
  3. Enter Open,Closed as the source; your regional separator may differ. Enable the in-cell dropdown.
  4. Set a clear input message and a Stop error alert.
  5. Test Closed, then restore the original value. Test Pending and cancel the rejected entry.
  6. Check all original statuses against the source list again.

Try it yourself

In the practice copy, add a separate priority dropdown to G2:G9 using High,Medium,Low. Test an invalid typed priority without keeping it.

Check your practical result
  • Valid labels can be selected.
  • An invalid typed value produces the Stop alert.
  • Original statuses and priorities are restored.

This is your own review, not an automatic inspection of Excel.

If it does not work

Colour or a dropdown alone is not validation. Check the selected range, rule and error style. Pasting can bypass or replace validation, so review imported values separately.

Optional knowledge check

This check is for practice. It is not saved or required to continue.

What does a dropdown prove?

Continue whenever you are ready. No test or recorded score is required.

Course guide and Microsoft helpDownload the whole course guide (.txt)

Steps primarily use Microsoft 365 desktop Excel for Windows. Other versions can differ. Formulas use English names and commas; your region may use semicolons. Work only on the fictional practice copies.

Microsoft: Data validation (opens in a new tab). Guidance checked 3 November 2026.

Browser knowledge checks do not execute Excel. This programme does not change your saved progress in other courses.

All 16 lessons and the day programme

Welcome and setup: 09:30–09:40. Evaluation and close: 16:15–16:30.

The four session windows match the supplied programme. Lesson times below are approximate independent practice times. Facilitators can use the remaining time for demonstrations, questions and breaks, including lunch within Session 2.

SESSION 1 · 09:40–11:40

More functions

Count, total, classify and work with dates.

  1. Count matching orders with COUNTIF · 15 min
  2. Add matching costs with SUMIF · 15 min
  3. Use IF to label a result · 15 min
  4. Use TODAY without losing a reporting date · 15 min

SESSION 2 · 11:40–14:30

Managing datasets

Keep records together, find what matters and control new entries.

  1. Sort whole records safely · 15 min
  2. Sort by team, then amount · 15 min
  3. Put priorities in a custom order · 15 min
  4. Filter a list and restore every row · 15 min
  5. Make a safe dropdown with data validation · 20 min

SESSION 3 · 14:30–15:30

Linking

Reuse values across cells, sheets and files with clear checks.

  1. Link cells instead of retyping · 10 min
  2. Link sheets inside one workbook · 10 min
  3. Link a value from another file · 15 min
  4. Inspect, repair and safely remove a link · 15 min

SESSION 4 · 15:30–16:15

Charts

Choose a chart, label it and improve its design.

  1. Choose a chart for the question · 15 min
  2. Label the chart so someone else can read it · 15 min
  3. Improve the design without changing the message · 15 min

Final practical task and evaluation →