Excel practice · Data Tools

Excel data validation & dropdown lists practice exercises

5 graded problems with realistic data, from medium to hard.

Not a function, but a restriction. Limits what a cell will accept, most often shown as a dropdown list of the only values allowed. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.

New to the function? Read the Data validation & dropdown lists guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Approve an expense with a Yes/No dropdown
  2. Restrict an order status to a fixed list
  3. Flag an escalated ticket with a priority dropdown
  4. Source a dropdown's list from a range on the sheet
  5. Don't include a tier that isn't real

The exercises

1. Approve an expense with a Yes/No dropdown Medium

This expense report has already been reviewed and approved. Build a dropdown that only allows Yes or No for the Approved column, then use it to mark this expense approved.

AB
1ExpenseApproved?
2#4471?

Set up the drop-down list.

Show a hint

Type Yes and No, one per line, then pick Yes from the dropdown.

A Yes/No dropdown is the simplest possible data validation. But it still stops someone from typing "yeah", "Y", or "approved" into a column every downstream formula expects to be exactly Yes or No.

2. Restrict an order status to a fixed list Medium

Order #101 has arrived at the customer. Build a dropdown that only allows Pending, Shipped, Delivered, or Canceled for the Status column, then use it to mark this order Delivered.

AB
1OrderStatus
2#101?

Set up the drop-down list.

Show a hint

Type Pending, Shipped, Delivered, and Canceled, one per line, then pick Delivered.

Restricting a status column to a fixed list stops "Shiped" or "delivered " (a typo, a stray space, inconsistent case) from quietly breaking every COUNTIF or SUMIF built on top of it later.

3. Flag an escalated ticket with a priority dropdown Medium

Ticket #552 just got escalated. Build a dropdown that only allows Low, Medium, High, or Urgent for the Priority column, then mark this ticket Urgent.

AB
1TicketPriority
2#552?

Set up the drop-down list.

Show a hint

Type Low, Medium, High, and Urgent, one per line, then pick Urgent.

A priority dropdown keeps a support or bug tracker sortable and filterable. "Kind of urgent I guess" typed freely would break every view built around the four real levels.

4. Source a dropdown's list from a range on the sheet Medium

Priya just moved teams. The allowed department list already lives on this sheet in D2:D4, rather than being typed straight into the dropdown. Read it, build the matching dropdown, and mark Priya's new department: Engineering.

ABCD
1EmployeeDepartmentDepartments
2Priya?Sales
3Engineering
4Support

Set up the drop-down list.

Show a hint

The allowed list is already sitting in D2:D4. Type exactly those three values, one per line, then pick Engineering.

Sourcing a dropdown's list from a range instead of typing it into the dialog is how real workbooks keep the list itself editable in one place. Add a department to D5 and the dropdown picks it up everywhere it's used.

5. Don't include a tier that isn't real Hard

Membership tiers here are Bronze, Silver, Gold, and Platinum. "Diamond" isn't a real tier at this company, even though people sometimes ask for it. Build the dropdown with exactly the 4 real tiers, then mark Owen as Gold.

AB
1MemberTier
2Owen?

Set up the drop-down list.

Show a hint

Only 4 tiers are real here. Don't add "Diamond" just because it sounds plausible.

Reading the actual requirement instead of pattern-matching to "obviously this should be on the list" is the real skill. Copying in an extra plausible-sounding option is exactly the kind of validation mistake that lets bad data back in.

Solve these in a real grid

Type your formula, get graded instantly. Your first 10 exercises are free, no signup.

Start the Data validation & dropdown lists exercises →

More data tools practice

All Excel practice exercises · Excel test prep