Excel 365: Build a Dropdown List That Removes Items Once They're Picked

Here’s a sentence that should never be true, and yet: “Wait, didn’t we already give Laptop-04 to someone else?” Two people picked the same item from the same dropdown, on the same sheet, and Excel didn’t say a word — because a standard Data Validation list doesn’t know or care what’s already been chosen in the cells around it. It just shows the same static list, forever, to everyone. Fixing that isn’t hard once you know the trick, but almost nobody stumbles onto it by accident.

Who this quietly bites

This shows up anywhere a fixed pool of things gets assigned one-to-one through a dropdown column — a list of resources on one side, a list of requests on the other, and a single Excel column connecting them:

• Loaner equipment sign-out — laptops, projectors, badges, company phones. Two employees both “have” Laptop-04 on paper.
• Meeting room or desk booking sheets — the same room gets double-booked for the same week.
• Shift or on-call rosters — two people accidentally both get assigned Saturday’s on-call slot, or nobody does.
• Event and raffle logistics — table numbers, sponsor booths, prize items assigned more than once.
• Fleet/vehicle checkout logs — the same van “booked” by two different drivers.

The common shape: a finite list of things (rows on a “Resources” list), and a booking column where each pick should come out of the pool for everyone else. Plain Data Validation has no concept of “already used” — it shows the same full list in every cell, so the only thing stopping a double-pick is someone reading carefully. That doesn’t scale past about five rows.

Why this isn’t a setting you can just turn on

It’s worth saying plainly: there’s no checkbox for this. Data → Data Validation → List just points at a range and shows whatever’s in it — it has no built-in “hide what’s already chosen” mode, in any version of Excel, including 365. To get that behavior, the list source itself has to be a formula that recalculates down to “only what’s left,” and the dropdown has to point at that formula’s output instead of the static master list. That’s the whole trick, and how you build it depends on which Excel you’re on.

The fix, two ways

Both versions below work off the same two ingredients: a master list of everything available (say, Lists!A2:A11 — ten laptops), and a booking column where people pick from it (say, 'Sign-Out Log'!B2:B13). The goal is a third list — “available” — that’s always the master list minus whatever’s already sitting in the booking column, and that’s what Data Validation points at.

Option 1 — classic, works in every Excel since 2007 (Excel 2007–2019, 2021, and 365)

This is the well-known “Contextures-style” dropdown-without-duplicates technique. It takes three small helper formulas and one defined name — no array-entry, no CTRL+SHIFT+ENTER, nothing version-specific.

1. Rank the items that are still available. Next to your master list, add a running-count formula that only counts items not yet in the booking column:

Lists!B2  =IF(COUNTIF('Sign-Out Log'!$B$2:$B$13,A2)=0, MAX($B$1:B1)+1, "")

Fill down. Items already booked get "" (blank); items still free get the next number in sequence — 1, 2, 3… — regardless of where they sit in the master list.

2. Pull those ranked items into a clean, gap-free list:

Lists!C2  =IFERROR(INDEX($A$2:$A$11, MATCH(ROWS($C$2:C2), $B$2:$B$11, 0)), "")

Fill down. This compacts the scattered “available” items from column B into a tidy list starting at C2, with no blanks in the middle.

3. Name that list, sized to fit exactly what’s left:

Formulas tab → Define Name → AvailableResources, refers to:

=OFFSET(Lists!$C$2, 0, 0, MAX(COUNT(Lists!$B$2:$B$11), 1), 1)

The MAX(..., 1) is a small defensive habit worth keeping: if every single item gets booked, COUNT would hit zero and OFFSET would try to build a zero-row range, which Data Validation rejects with an error. Flooring it at 1 just shows one blank row instead of breaking the dropdown.

4. Point the dropdown at the name. Select 'Sign-Out Log'!B2:B13 → Data → Data Validation → List → Source: =AvailableResources. Done — pick a laptop in row 2, and it vanishes from the dropdown in every other row, instantly, no macro required.

Option 2 — Excel 365: one FILTER formula replaces all three helper columns

If you’re on 365, you can skip the rank-then-compact dance entirely. FILTER does both jobs in a single spilled formula:

Lists!D2  =FILTER($A$2:$A$11, COUNTIF('Sign-Out Log'!$C$2:$C$13,$A$2:$A$11)=0, "All assigned")

Read it left to right: filter the master list (A2:A11) down to only the items whose COUNTIF count in the booking column is zero — i.e., not yet picked. The third argument is the part classic Excel can’t do at all: if literally nothing is left, the formula spills the text “All assigned” instead of throwing #CALC! or #N/A. No padding trick needed — 365 handles the empty case natively.

Now name the spill, not a fixed range:

Formulas tab → Define Name → AvailableResources365, refers to:

=Lists!$D$2#

That trailing # is the spill reference operator — it means “however many rows this formula happens to produce, right now.” Point Data Validation’s Source at =AvailableResources365 and the dropdown tracks the FILTER’s output exactly, growing and shrinking as people pick and unpick items.

Heads up on a common trap: in some 365 builds, typing =Lists!$D$2# directly into the Data Validation Source box gets rejected outright. Wrapping it in a defined name first, as above, sidesteps that inconsistency completely and is the version that reliably works across builds — worth doing even though it’s one extra step.

Letting someone change their mind

Both versions share one quirk: the “available” list is shared across the whole booking column, including the cell being edited. So if B4 already has “Laptop-07” and you reopen that cell’s dropdown to switch it, Laptop-07 won’t be in the list — as far as the formula’s concerned, it’s still “taken,” by that very cell. The fix is simple and matches how these sheets get used in practice: clear the cell first (select it, press Delete), then reopen the dropdown — the freed-up item reappears everywhere immediately, since every dropdown is reading the same live “available” list.

Where else this pattern pays for itself

• Interview or appointment slot sheets — each time slot gets assigned to exactly one candidate.
• Team task boards — a fixed set of task IDs handed out to team members, one owner each.
• Volunteer sign-ups — a list of roles or shifts that shouldn’t be filled twice.
• License/seat allocation — a fixed number of software seats assigned to named users.
• Wedding or event seating — table numbers assigned to guests without a clash.

Anywhere the sentence “there’s a limited pool, and every pick should come out of it for everyone else” applies, this pattern fits.

Download the workbook

The workbook below is a laptop loaner sign-out sheet with 12 employee rows but only 10 laptops — so you can watch both the classic and 365 versions run out gracefully and show “All assigned.” It has two dropdown columns side by side, built with the two different techniques above, plus the Lists sheet with every helper formula and both defined names already wired up, so you can inspect exactly how each one works and reuse it for your own sheet.

Download the no-duplicate-dropdown workbook (.xlsx)

Takeaways

• Standard Data Validation → List always shows the same static range to everyone — it has no idea what’s already been picked elsewhere, in any Excel version.
• To make picks disappear from the pool, the list source has to be a formula, not a static range — and Data Validation has to point at that formula’s output.
• Classic (Excel 2007+): a running-rank IF(COUNTIF(...)=0, MAX(...)+1, "") helper, compacted with INDEX/MATCH, wrapped in an OFFSET-based defined name.
• Excel 365: one line — =FILTER(list, COUNTIF(booking_range, list)=0, "All assigned") — named via a spill reference (Lists!$D$2#) instead of OFFSET.
• If someone needs to change a pick, clear the cell first, then reselect — the freed-up item reappears in every dropdown immediately.